Help with Open File from Directory

Dear Superusers,

Could someone please tell me how I can add the 'select file from directory' for below mentioned code?

Currently the code is picking all files that are in the folder location, however, I would like to select them.
Tried couple of things but failed...

Thanks for your help!

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "\\Nl-file01\elc-depts\Shared Services\Cash management\Cash forecast\2012 reports\04 April" ' change to suit
    Set wbDst = ActiveWorkbook
    strFilename = Dir(MyPath & "\*.xls", vbNormal)
    
    If Len(strFilename) = 0 Then Exit Sub
    
    Do Until strFilename = ""
        
            Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
            
            Set wsSrc = wbSrc.Worksheets(1)
            
            wsSrc.Copy after:=wbDst.Worksheets(wbDst.Worksheets.Count)
            
            wbSrc.Close False
        
        strFilename = Dir()
        
    Loop
    
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

Thanks Alrette for the Quick Reply - can you help me with adding a directory to this as well?

Originally Posted by MarianneBal

Thanks Alrette for the Quick Reply - can you help me with adding a directory to this as well?

And I would need to select multiple files.

Hello again,

It's not working - I also need to select multiple files...

Any suggestions?

Hello Arlette,

With the code I want to get a POP UP where I can selecte multiple files.

From my selection I want the code to copy the first worksheet in the Active Workbook.
(this part does actually work, but with my code mentioned above it picks automatically all files in the folder)

For another sheet I have a code that allows me to select multiple files, but I'm somehow not able to 'merge' those 2 codes.

The Code with the multiple file selection that works is:

Private Declare Function SetCurrentDirectoryA Lib _
    "kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
    SetCurrentDirectoryA szPath
End Sub


Sub MergeSpecificWorkbooks()
    Dim MyPath As String
    Dim SourceRcount As Long, FNum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long
    Dim SaveDriveDir As String
    Dim FName As String


    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    SaveDriveDir = CurDir
    ' Change this to the path\folder location of the files.
    ChDirNet "\\Nl-file01\elc-depts\Shared Services\Cash management\Cash forecast\2012 reports\04 April"

    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
                                        MultiSelect:=True)
                                        
...