Combine named sheets from a collection of spreadsheets in a specific folder

[JayCS]

Here is my problem. I have a collection of spreadsheets (actually they are all in xml format) in a folder and I want to combine all the data from the sheets named "Balance Summary" but I really don't know how. The macro I have below isn't working. I tried modifying one I use that successfully pulled in all the first sheets of every file in the folder. I just need to know how to get it to pull in all the data from a named folder that is never the first sheet in the spreadsheet. Any help would be greatly appreciated. Also, if anyone knows of a good training class for Excel VBA, I would love your feedback as well.

Here is my Script:

Sub BankScanSummary()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer
Const sName$ = "Balance Summary" ' << sht name

RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

ThisWB = ActiveWorkbook.Name

path = GetDirectory("Select a folder containing Excel files you want to merge")

Application.EnableEvents = False
Application.ScreenUpdating = False

Set shtDest = ActiveWorkbook.Sheets(1)
Filename = Dir(path & "\*.xml", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count + 4))
Set Dest = shtDest.Range("B" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
CopyRng.Copy Dest
Wkb.Close False
End If

Filename = Dir()
Loop

Range("A1").Select

Application.EnableEvents = True
Application.ScreenUpdating = True

MsgBox "Balance Summary Done!"
End Sub

 

[Rijnsent]

Hi Jay,
To start you off with some learning resources, I like Excel and VBA trainings and Excel VBA Programming - a free course for complete beginners for the basics. Secondly, I don't have your XML files, but at a first glance the code you use looks okay. It basically should loop though all the XML files in that folder and try to add them to your Balance Summary sheet.
Some general debugging tips:

• don't run your code pressing F5 or play, that's too quick/too fast
• use F8 -> it goes step by step through your code
• and/or use F9 / breakpoints plus F5 (click left of the line of code, it should color dark red and get a circle in front of it)
• use the direct window (VBE menu: View or use CTRL+G) to output variables for debugging with debug.print variable_name
• with breakpoints/step-by-step progressing, use the local variables window (again: menu View) to check the values of your local variables on the fly
• With some screens: Debugging in Excel VBA

What do you see happening if you try those debugging tips?
Cheers,
Koen
P.S. Next time, please post your code in VBA tags, that makes it much more readable.

 

[JayCS]

OK. You are giving me too much credit. The way I run my macro is to go to the developer menu and click on the Macros button that I created to run the VBA.

Maybe I need to clarify. Here is my example: I have 15 xml files and each one of them has a worksheet (that is NOT the first worksheet in its respective file) that is named "Balance Summary". I launch a new workbook and want to have my code go and pull all information from each of the "Balance Summary" tabs in each of the 15 files and paste it into my new workbook. The script as it is written always combs through the first sheet of each xml.

In other words, how do you write a command to copy all data from each file that has a tab called "Balance Summary"? I assume it is Set Copyrng=Wkb.Sheet, but how do you tell it to only look in the sheet named "Balance Summary".

I am such a rookie at this I don't even know what you mean when you tell me to post my code in VBA tags. Sorry about that.

 

[Rijnsent]

Ah, got ya! First: the VBA tags are in the bar when you make a post/message here: from left to right the bar starts with remove formatting, bold, italic, underline... And half way there is <vba/> which gives you tags that help make the VBA code more readable. Secondly, I guess you mean you have xls/xlsx/xlsm files? XML is a very different format, that's what confused me. Thirdly: do give my bullets a try, it might be worth your time to learn some VBA (ALT+F11 for the editor, that's where the magic really happens), has helped me tremendously in my career. And finally, try this for a solution: replace your 2 lines starting with Set Wkb and Set CopyRng by:

Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
Set Sht1 = Wkb.Worksheets("Balance Summary")
Set CopyRng = Sht1.Range(Sht1.Cells(RowofCopySheet, 1), Sht1.Cells(Sht1.UsedRange.Rows.Count, Sht1.UsedRange.Columns.Count + 4))

I used the <vba/> tags here, that's what this formatting comes from.
Hope that works,
Koen

 

[JayCS]

OK. I now get an error when running. The error pops up on this line:

Set Sht1 = Wkb.Worksheets("Balance Summary")

and the error is: Compile error: Variable not defined.

I think we are close. Thanks for the VBA tags advice.hopefully this message will look better.

 

[Rijnsent]

Self-help course lesson 1: look for that error message in e.g. Google or DuckDuckGo. It will tell you that you have "Option Explicit" at the top of your macro. That means that all variables that you use (like Wkb, CopyRng, Sht1) need to be defined by using a Dim statement before you use it. So add somewhere with the other Dim lines something like:

Dim Sht1 as Worksheet

Cheers,
Koen

 

[JayCS]

Thanks. I did google the error and I did read about option explicit but I didn't know how to turn Option explicit off. I probably should have tried to just delete it to see what would happen. I added the line you suggested and everything worked perfectly. Thank you so much for your help. Now that I am past my problem, I need to go back and start doing that training.

 

[Rijnsent]

Thanks. I did google the error and I did read about option explicit but I didn't know how to turn Option explicit off. I probably should have tried to just delete it to see what would happen. I added the line you suggested and everything worked perfectly. Thank you so much for your help. Now that I am past my problem, I need to go back and start doing that training.

Good luck! You turn that option off by deleting that line from your code. I am a lazy coder myself, so normally leave it out, although it is good practice to leave that option explicit on. That means that you have to Dim a variable before you can use it. That should prevent errors and weird behavior because you e.g. use a variable twice without realizing it.
Cheers