Without getting too technical, the purpose of this article is to show you how you can loop through multiple files and a particular folder on your computer and scrape data from each worksheet.
We’re going to take this data and put it on to the master workbook, meaning the workbook where the code originates from.
Obviously, you can copy and paste this code or download the zip file that contains the three demo workbooks and the master workbook with all the code, but if you would prefer an over the shoulder video guide, just watch the YouTube video at the end of this post.
If you’re interested in learning the ins and outs of VBA, check out my ultimate Excel programmer course, which is now over 10 hours of jam packed Excel content!
Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")
'This is where you put YOUR folder name
Set fldr = fso.GetFolder("C:\temp")
'Next available Row on Master Workbook
y = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
'Loop through each file in that folder
For Each wbFile In fldr.Files
'Make sure looping only through files ending in .xlsx (Excel files)
If fso.GetExtensionName(wbFile.Name) = "xlsx" Then
'Open current book
Set wb = Workbooks.Open(wbFile.Path)
'Loop through each sheet (ws)
For Each ws In wb.Sheets
'Last row in that sheet (ws)
wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row
'Loop through each record (row 2 through last row)
For x = 2 To wsLR
'Put column 1,2,3 and 4 of current sheet (ws) into row y of master sheet, then increase row y to next row
ThisWorkbook.Sheets("sheet1").Cells(y, 1) = ws.Cells(x, 1) 'col 1
ThisWorkbook.Sheets("sheet1").Cells(y, 2) = ws.Cells(x, 2)
ThisWorkbook.Sheets("sheet1").Cells(y, 3) = CDate(ws.Cells(x, 3))
ThisWorkbook.Sheets("sheet1").Cells(y, 4) = ws.Cells(x, 4)
y = y + 1
'Close current book
Watch the explainer on YouTube below:
You Might Also Like
Sometimes using a text box on a user form or on a worksheet doesn't work exactly the way we think it should. This is especially true when were working with dates and times straight from a worksheet. The problem we faced when helping Emil was that the format function...
Recently, I was asked how to format Textboxes as Hours and Minutes, like a Start Time and End Time, here's a quick video tip I replied with. Hope you enjoy! https://www.youtube.com/watch?v=HaPAPweKsOM Get the workbook here...
This week, I was asked how to load the sheet names onto a listbox and then when a sheet in that listbox is clicked - how to list the column headers in the second listbox! Sound crazy hard? No, it's actually very easy. Check out the video below and Download the...
Excel VBA Scrollbars are awesome for visually and easily changing a value, but there is one limitation - when you click the square in the middle and drag it, you can't automatically make it snap to the neares NN value! Until now! Check out how we resolve this with a...
Excel VBA UserForms #2 Create a UserForm with 2 option buttons: 1 Sort by Employee ID or 2 Sort by Name. Part one, create the UserForm and give it buttons. First, bring up the VBA UserForm editor (F11), and add a new UserForm using the...
UserForm Basics #1 Here, you'll learn to make a UserForm, add a button, and make clicking on that button hide the UserForm! Make a UserForm: Press Alt-F11 to bring up Visual Basics menu Click the drop down menu in the upper left hand and select...