.woocommerce form .form-row .required{visibility:visible}
Taking too long? Close loading screen.
Excel VBA Is Fun - "Make Excel Work For You"
Sometimes using loops with the fileSystemObject in Visual Basic isn’t the most intuitive thing to use, but in this article I want to show you some basic things you can do with it.

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!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Sub getDataFromWbs()

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
Next x

Next ws

'Close current book
wb.Close
End If

Next wbFile

End Sub

 

Watch the explainer on YouTube below:
https://www.youtube.com/watch?v=XlEzDeD1fsU

You Might Also Like

How To Prompt For A File In Excel VBA

**New To VBA?: In these Excel VBA Posts, you'll need to Open The Visual Basic Editor (VBE), and Make a New Module and then Create A New Macro (Sub procedure) in order to paste the code below and use it. Please click the above Tool Tips in this paragraph in order to...

Password Reset
Please enter your e-mail address. You will receive a new password via e-mail.

Pin It on Pinterest

Share This

Share This

Share this post with your friends!