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?