Importing multiple excel files into one spreadsheet

Woofy_McWoof_Woof

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

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

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
What is the error message?
 

Woofy_McWoof_Woof

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Is that the name of the file with the code?
 

Woofy_McWoof_Woof

New Member
Joined
Oct 7, 2016
Messages
46

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,344
Members
416,096
Latest member
forevans

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
Top