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](https://stackoverflow.com/questions/11152870/macro-open-all-files-in-a-folder) for how to do it. — BigBen, Jul 30 '19 at 02:59
The one you send just opens the file itself not import it into the current excel sheet. — , Jul 30 '19 at 03:14

Mikku · Answer 1 · 2019-07-30T05:46:13.620

0

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

edited Jul 30 '19 at 05:46

answered Jul 30 '19 at 03:49

Mikku

6,538
3
15
38

it has an error of "Object variable or With block variable not set" in the line "For Each WS In SourceWB.Worksheets" – Jul 30 '19 at 05:32
Did msgbox came up ? – Mikku Jul 30 '19 at 05:37
yes it came up and nothing have been copied to my current file – Jul 30 '19 at 05:38
Then it couldn't find the File. That's Why msgbox came up. Are you sure file is in Format `Results*.xlsx` ? and in the same folder as current workbook ? – Mikku Jul 30 '19 at 05:39
in the location of the excel file im currently using there are only 1 .xlsx file and I need that to be automatically detected so it would be copied in my excel file im using – Jul 30 '19 at 05:40
Isnt there any pattern of that file name ? what is the name of that file – Mikku Jul 30 '19 at 05:41
sometimes there are 2 files in here. that is why i am figuring out how vba can automatically open without specifying the location or file name – Jul 30 '19 at 05:42
But there has to be a pattern of the File Name. Otherwise how will VBA identify the file ? – Mikku Jul 30 '19 at 05:42
I have read something that in order to open all the file with .xlsx there is a need of "*" – Jul 30 '19 at 05:43
Maybe you can benefit from reading [this](https://stackoverflow.com/questions/31414106/get-list-of-excel-files-in-a-folder-using-vba). If you cannot find a pattern, you need to loop through all the files. that is rather a big code. All the Best – Mikku Jul 30 '19 at 05:45

Import sheet from one excel file to another without specifying destination

1 Answers1