![]() ![]() The “use a Collection” strategy also makes it really easy to verify that the loop worked and pulled in the data we expected, which you can check for yourself by un-commenting lines 27-31. Programming is hard enough - avoid deeply-nested loops whenever you can. Each nested loop you add is another layer of complexity for you to mentally keep track of… fuck that. Yes, we COULD have conducted the bulk of the code inside this Dir loop, but I prefer using a Collection here because it reduces the number of nested loops in our subroutine. Umm why not do the whole thing in this Dir loop? Lines 22 through 25 store each file name inside a Collection (named colFileNames), which will make it SUPER easy to iterate through each file a little bit later in the code. (That’s what the asterisk character, “*”, is doing there at the end of the line.) Step 2 – Exploration begins on line 21, where we take advantage of the Dir function to loop through the directory we set up moments ago ( StrDirContainingFiles) and identify every file that ends in “.xlsx”. output) to store the data from each individual file, then assign the first Worksheet in that Workbook as the Dst Worksheet. Then, on lines 16-17, we create a new Workbook (where Dst is short for “destination”, i.e. ![]() (You’ll want to change this to your folder, but in this example we are targeting C:\blog\example_data_in_here.) Our Step 1 – Setup is covered by lines 16-18 - short and sweet.įirst, on line 16, we assign the folder name where the individual Excel files are stored. ![]() Let’s break this challenge down step-by-step using the 4-step VBA process as our guide: Here’s a link to the code above so you can review it side-by-side with the walk through below. This situation blows, since your analysis depends on a pivot table and combining a bunch of workbooks together by hand is terrible.įortunately, VBA will make short work of this pain in the ass. I need to make a pivot table but the data is spread out in lots of different Excel files… ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |