Importing multiple excel files into one spreadsheet

[Woofy_McWoof_Woof]

Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the previous day's data. To make it more complicated each day's file has two tabs that i need to import but I'm assuming the code below will do this automatically??? Or do i need to separate the tabs out?

Error messages displayed when I run are:

The 'Next' before the 'End Sub' creates a Compile Error:Next Without For, I'm not sure why this occurs.
If i remove the 'Next' and then run it I get run time error 438: Object doesn't support this property or method

I've copied in the code below, hopefully you can help.

Sub Mergefiles()
Dim booklist As Workbook
Dim mergeObj As Object, dirobj As Object, filesobj As Object, everyobj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.Filesystemobject")


'change folder path of excel files here
Set dirobj = mergeObj.getfolder("H:\folder1\folder2\folder3")
Set filesobj = dirobj.filesobj
Set booklist = Workbooks.Open(everyobj)


'data is contained in columns A3:AC3 downwards, the first two rows contain headers
Range("A3:AC" & Range("A65536").End(xlUp).Row).Copy


ThisWorkbook.Worksheets(1).Activate


Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial


Application.CutCopyMode = False
booklist.Close
Next
End Sub

 

[Woofy_McWoof_Woof]

Yes, i think so. I am now getting the run time error 1004 after i moved the code into a module. When i debug it, it highlights With Workbooks.Open(Fileobj). Sorry for all of this.

 

[Fluff]

What is the error message?

 

[Woofy_McWoof_Woof]

Run time error 1004 - Excel cannot access the file (path name and file name). There are several possible reasons:
The file name or path does not exist
The file is being use another programme
The workbook you are trying to save has the same name as a currently open workbook

 

[Fluff]

When you get the error, click debug & hover the mouse over the Fileobj variable.
A tool tip should appear showing the value of the variable, What does it say?

 

[Woofy_McWoof_Woof]

It says Fileobj="H:\test\~$Import files test.xlsm"

 

[Fluff]

Is that the name of the file with the code?

 

[Woofy_McWoof_Woof]

The name of the file with the code is Import files test.xlsm which i have referenced in the code If Fileobj.Name <> "Import files test.xlsm" Then so I'm not sure why it adds in the extra~$

 

[Fluff]

Ok, how about

Sub Woofy()
    Dim Pth As String, Fname As String
    Dim Ws1 As Worksheet, ws2 As Worksheet
    
    Application.ScreenUpdating = False
    Set Ws1 = ThisWorkbook.Sheets("Optimised")
    Set ws2 = ThisWorkbook.Sheets("Baseload")
    Pth = "H:\test\"
    Fname = Dir(Pth & "*.xls*")
    Do While Fname <> ""
        If Fname <> "Import files test.xlsm" Then
            With Workbooks.Open(Pth & Fname)
                With .Sheets("Optimised")
                    .Range("A3:AC" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
                    Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                End With
                With .Sheets("Baseload")
                    .Range("A3:AC" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
                    ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                End With
                Application.CutCopyMode = False
                .Close False
            End With
        End If
        Fname = Dir
    Loop
End Sub

 

[Woofy_McWoof_Woof]

Hooray, it worked!!! It has bought down all of the days so far this month. Fantastic. Thanks a lot for your help

 

[Fluff]

You're welcome & thanks for the feedback