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]

I spoke too soon unfortunately. It just popped up an error when about halfway through importing the files. It says 'someone else is working in the master file, try again later', I'm the only one in the file. I've shut down excel and tried again but same message. It then gives a pop up window with an big red x in it and says 400. Any thoughts?

 

[Fluff]

Is it possible that somebody else has one of the files in that folder open?

 

[Woofy_McWoof_Woof]

No, its my own personal drive. I have just shut down excel and my computer to see if it cleared the memory. Still the same issue though, hmm

 

[Fluff]

Is the file that contains the code, in the same folder as the files you want to open?

 

[Woofy_McWoof_Woof]

Yes

 

[Fluff]

In that case that's the problem as it's trying to open the file in the loop, but you already have it open.
Try

Sub Woofy()
    Dim Fso As Object, Fileobj As Object
    Dim Ws1 As Worksheet, ws2 As Worksheet
    
    Application.ScreenUpdating = False
    Set Ws1 = ThisWorkbook.Sheets("Optimised")
    Set ws2 = ThisWorkbook.Sheets("Baseload")
    Set Fso = CreateObject("scripting.filesystemobject")
    For Each Fileobj In Fso.GetFolder("C:\Mrexcel\test").Files
        If Fileobj.Name <> "[COLOR=#ff0000]Master.xlsm[/COLOR]" Then
            With Workbooks.Open(Fileobj)
                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
    Next Fileobj
End Sub

Change file name in red to suit

 

[Woofy_McWoof_Woof]

Same issue i'm afraid, its always when it hits row 290. Is there a way to code it to ignore the loop error?

 

[Fluff]

What do you mean by "when it hits row 290"?

 

[Woofy_McWoof_Woof]

The error message pops up once the imported files in my master file hits row 290, i don't know if its a coincidence though or just part of the looping issue.

 

[Fluff]

Are you saying that as soon as data goes into row 290, you get an error telling you that the file is open?