Problem in accessing file name from Input box

[rameshjeni]

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.

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?

[Dave Hawley]

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