Import sheet from one excel file to another without specifying destination

Question

In need of importing an Excel Workbook to the sheets of existing Excel Macro-Enabled Workbook

Have been successful in importing it using the code which the destination is specified I used ".xlsx" as the destination since "*" is used to specify all xlsx file found in the folder.

Sub CopySheets()

    Dim WB As Workbook
    Dim SourceWB As Workbook
    Dim WS As Worksheet    
    Dim ASheet As Worksheet

    'Turns off screenupdating and events:    
    Application.ScreenUpdating = False   
Application.EnableEvents = False

'Sets the variables:
Set WB = ActiveWorkbook
Set ASheet = ActiveSheet
Set SourceWB = Workbooks.Open(WB.Path & "\*.xlsx")  'Modify to match

'Copies each sheet of the SourceWB to the end of original wb:
For Each WS In SourceWB.Worksheets
    WS.Copy after:=WB.Sheets(WB.Sheets.Count)
Next WS        
    SourceWB.Close savechanges:=False
    Set WS = Nothing
    Set SourceWB = Nothing       
WB.Activate
ASheet.Select
    Set ASheet = Nothing
    Set WB = Nothing        
Application.EnableEvents = True

End Sub

The error is "Run-time error '1004': Sorry, we couldn't find C:\Users\ZMOLD01\Desktop\CaseStudy Results*.xlsx. Is it possible it was moved, renamed or deleted?

Use the Dir function. You can't use a wildcard like that in Workbooks.Open. See this question for how to do it. — BigBen
The one you send just opens the file itself not import it into the current excel sheet. — user11732489

Mikku Mikku · Accepted Answer · 2019-07-30T03:49:56

Dir gives you the file name if it is found in the Destination.

Change:

Set SourceWB = Workbooks.Open(WB.Path & "\*.xlsx")

To:

If Len(Dir(wb.path & "\*.xlsx")) > 0 Then

    Set SourceWB = Workbooks.Open(wb.path & "\" & Dir(wb.path & "\*.xlsx"))

Else: Msgbox "File Not Found"
End if

Import sheet from one excel file to another without specifying destination

1 Answers