Further to solution 1
Part of your problem is that you have used
Set mergeObj = CreateObject("Scripting.FileSystemObject")and
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
It is far easier to use the
actual types rather than generic objects. E.g.
Dim mergeObj As New FileSystemObject
Dim dirObj As Folder
...etc
Then you can use Intellisense and you might have noticed that each WorkBook has a
Sheets collection that you can iterate through using a
for each loop.
Here is a reworked example of your function
'Add a reference to Microsoft Scripting Runtime (Tools, References)
Sub simpleXlsMerger()
'This is where you are going to copy the other workbooks TO
Dim target As Worksheet
Set target = ActiveWorkbook.Sheets(1)
'This is how I am going to keep track of where the next set of data goes
Dim startRow As Long
startRow = 1
'Get the name of the folder - I would prefer a dialog selection box
Dim strWSName As String
strWSName = InputBox("Enter the file path of Excel Files to merge")
'This is the list of excel spreadsheets I want to load
Dim mergeObj As New FileSystemObject
Dim dirObj As Folder
Set dirObj = mergeObj.GetFolder(strWSName)
Set filesObj = dirObj.Files()
Application.ScreenUpdating = False
Dim everyObj As File
For Each everyObj In filesObj
Debug.Print everyObj.Name + " " + everyObj.Type
'Check for Excel files
If Left$(everyObj.Type, Len("Microsoft Excel")) = "Microsoft Excel" Then 'Caters for different versions
Dim bookList As Workbook
Set bookList = Workbooks.Open(everyObj) 'Should really have some error handling around this
'This bit goes through all of the sheets in the loaded workbook
Dim ws As Worksheet
For Each ws In bookList.Sheets
Debug.Print (" " + ws.Name)
ws.Activate
ws.Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
'Work out where to put the new data
target.Activate
target.Range("A" + CStr(startRow)).Select
target.Paste
'set up ready for the next sheet or workbook
startRow = startRow + Selection.CurrentRegion.Rows.Count + 1
Application.CutCopyMode = False
Next
bookList.Close
End If
Next
End Sub
Couple of things to note ... that first comment - You will need to add a reference as described.
Secondly I've used a very crude method of selecting the data to copy (
CurrentRegion) and of finding out where to paste it in my target sheet (
startRow) - you might want to change how you do that
You can ignore the
Debug.Print lines - I just used them for debugging and testing.
Especially notice the lines with
.Activate - you may get an error if you don't let the code know where you want to reference
Submit an article or tip