Importing multiple excel files into one spreadsheet

Woofy_McWoof_Woof

Board Regular
Joined
Oct 7, 2016
Messages
60
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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:
Upvote 0
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:
Upvote 0
Thinking about it, I'm assuming i would need to change the sheet names to the one's i'm using?
That's right. :)
 
Upvote 0
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.
 
Upvote 0
Your sheet names should be in quotes
Code:
Sheets("Optimised")
 
Upvote 0
Code:
[COLOR=#333333]With .Sheets("Baseload")[/COLOR]
as well
 
Upvote 0
All of them need the quotes.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top