Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: Problem in accessing file name from Input box

  1. #1
    Join Date
    12th April 2012
    Location
    Chennai India
    Posts
    8

    Problem in accessing file name from Input box

    I am attaching a code where I get the details of folder and file names and based on that do a series of actions on two files. The code gets stuck in the step "Workbooks(MyFilename).Activate". Basically the file name "MyFilename" from Open_File function is not getting passed to the " OpenWorkbookFromFolder" sub.

    Code:
    Sub OpenWorkbookFromFolder()
    'To open files and / or folders using the FollowHyperlink method
         'Open Folder
        Dim strFolder As String
        Dim strXLSFile As String
        Dim strXLSFilepath As String
        'Open excel workbook=source
        Open_File
        Msgbox "My File is" & "  " & MyFilename
        'Open excel workbook activate workbook and worksheet to copy from
        Workbooks(MyFilename).Activate
        Sheets("Sheet1").Activate
        Range("A1:M3").Select
        Selection.Copy
        
        'Open excel workbook -destination
        Open_File
        Workbooks(MyFilename).Activate
        Sheets("Sheet1").Activate
        Range("A1").Select
        ActiveSheet.Paste
        Rows("4:11").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlUp
    End Sub
    
    Function Open_File() As String()
        Dim sFile As String
        Dim MyFilename As String
        Dim MyFolder As String
        MyFolder = InputBox("folder name", "enter folder name", "C:\")
       
       'this is just to check the input box selects correctly
        Msgbox "My Folder is" & "  " & MyFolder 
        
       MyFilename = InputBox("Enter file number", "enter file name", "Test") & ".xls"
      
       'this is just to check the input box selects correctly
       Msgbox "My File  is" & "  " & MyFilename
        sFile = MyFolder & MyFilename
        
        ActiveWorkbook.FollowHyperlink Address:=sFile, NewWindow:=True
           
        'Workbook.Open Filename:=sFile
    End Function
    any suggestions on where the problem could be?
    Last edited by Dave Hawley; April 14th, 2012 at 20:03.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,789

    Re: Problem in accessing file name from Input box

    Use the GetOpenFileName Method because of typos. How to Use the GetOpenFilename Method

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: October 28th, 2010, 23:32
  2. Accessing large file across WAN: Windows XP; Word 2007.
    By Dave Hawley in forum Office 2007 Migration and Application Compatibility
    Replies: 0
    Last Post: December 12th, 2009, 15:45
  3. Accessing the file system with VBA
    By MrC in forum EXCEL HELP
    Replies: 1
    Last Post: September 21st, 2005, 01:41
  4. Problem Accessing Clipboard
    By studentVB in forum EXCEL HELP
    Replies: 1
    Last Post: April 4th, 2005, 18:00

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno