Importing multiple excel files into one spreadsheet

Woofy_McWoof_Woof

New Member
Joined
Oct 7, 2016
Messages
34
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
How about
Code:
Sub Woofy()
    Dim Fso As Object, Fileobj As Object
    Dim Ws1 As Worksheet, ws2 As Worksheet
    
    Set Ws1 = ThisWorkbook.Sheets(1)
    Set ws2 = ThisWorkbook.Sheets(2)
    Set Fso = CreateObject("scripting.filesystemobject")
    For Each Fileobj In Fso.GetFolder("H:\folder1\folder2\folder3").Files
        With Workbooks.Open(Fileobj)
            With .Sheets(1)
                .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(2)
                .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
    Next Fileobj
End Sub
 
Last edited:

Woofy_McWoof_Woof

New Member
Joined
Oct 7, 2016
Messages
34
Thanks for your comments, I have tried the code but I'm now getting a run time error 9: subscript out of range, any thoughts?

Thinking about it, I'm assuming i would need to change the sheet names to the one's i'm using?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Thinking about it, I'm assuming i would need to change the sheet names to the one's i'm using?
That's right. :)
 

Woofy_McWoof_Woof

New Member
Joined
Oct 7, 2016
Messages
34
Ok, I've done that but still getting the same runtime error. I've also named the two sheets in my master file to correspond to the sheet names in the daily files.

Sub Woofy()
Dim Fso As Object, Fileobj As Object
Dim Ws1 As Worksheet, ws2 As Worksheet

Set Ws1 = ThisWorkbook.Sheets(Optimised)
Set ws2 = ThisWorkbook.Sheets(Baseload)
Set Fso = CreateObject("scripting.filesystemobject")
For Each Fileobj In Fso.GetFolder("h:\folder1\folder2\folder3").Files
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
Next Fileobj
End Sub

Any thoughts? Thanks for your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Your sheet names should be in quotes
Code:
Sheets("Optimised")
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
359
Office Version
2013
Platform
Windows
Code:
[COLOR=#333333]With .Sheets("Baseload")[/COLOR]
as well
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
All of them need the quotes.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Forum statistics

Threads
1,078,137
Messages
5,338,458
Members
399,234
Latest member
WaddoAU

Some videos you may like

This Week's Hot Topics

Top