Announcement

Collapse
No announcement yet.

Problem in accessing file name from Input box

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 19:03.

  • #2
    Re: Problem in accessing file name from Input box

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

    Comment

    Working...
    X