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
32,254
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
32,254
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
32,254
Office Version
365
Platform
Windows
Your sheet names should be in quotes
Code:
Sheets("Optimised")
 

mohadin

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

Fluff

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

Fluff

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

Forum statistics

Threads
1,082,075
Messages
5,363,040
Members
400,709
Latest member
PhilLewis

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top