Have Questions? 
Excel Ninja Pro
All Access

February 20, 2018

Loop Through Multiple Files in a Folder and Scrape Data From Each

[et_pb_section fb_built="1" _builder_version="3.0.47"][et_pb_row _builder_version="3.0.47" background_size="initial" background_position="top_left" background_repeat="repeat"][et_pb_column type="4_4" _builder_version="3.0.47" parallax="off" parallax_method="on"][et_pb_text _builder_version="3.0.47" background_size="initial" background_position="top_left" background_repeat="repeat"]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!

[cc lang="vbscript" lines="-1" width="100%"]

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
End If

Next wbFile

End Sub


[button link="http://excelvbaisfun.com/mdocs-posts/loop-through-multiple-files-in-a-folder-and-scrape-data-from-each/" color="default" size="" type="" shape="" target="_self" title="" gradient_colors="|" gradient_hover_colors="|" accent_color="" accent_hover_color="" bevel_color="" border_width="1px" shadow="" icon="" icon_divider="yes" icon_position="left" modal="" animation_type="0" animation_direction="down" animation_speed="0.1" alignment="left" class="" id=""]Download Zip File[/button]


Watch the explainer on YouTube below:
[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section][et_pb_section fb_built="1" _builder_version="3.0.105" global_module="14500"][et_pb_row _builder_version="3.0.105"][et_pb_column type="4_4" _builder_version="3.0.47" parallax="off" parallax_method="on"][et_pb_text _builder_version="3.0.105"]

You Might Also Like

[/et_pb_text][et_pb_blog fullwidth="off" posts_number="6" include_categories="72,73,45,71,46,1,51" show_comments="on" offset_number="0" use_overlay="on" _builder_version="3.0.105" header_font="Playfair Display|700|||||||" header_font_size="20" header_line_height="1.4em" body_font="Poppins||||||||" body_font_size="16px" body_line_height="1.5em" meta_font="Poppins|600||on|||||" meta_font_size="12px" meta_text_color="#edbb5f" meta_letter_spacing="1px" meta_line_height="2em" pagination_font="Poppins|600||on|||||" pagination_font_size="16px" pagination_font_size_tablet="51" pagination_text_color="#edbb5f" pagination_letter_spacing="1px" pagination_line_height="2em" text_orientation="center" animation_style="flip" custom_css_content="display: none;"]



5 comments on “Loop Through Multiple Files in a Folder and Scrape Data From Each”

  1. Thanks for this post!

    I would really love to add in your code 2 things:

    1. Only copy data from files starting with a specific text

    2. After copying the data, move the file to a subfolder (in order to avoid copying more than once)

    Could you please help me?

  2. Hi, do you have nay ideas why it doesn't work on Mac? I know some stuff doesn't work on Macs but perhaps you know some workaround? It is stack at creating fso.

  3. I need your help in re-correcting the macro. My file in the folder have data for sheet 2 & 3. So what happening means macro is picking all the data from all the sheets. I need only first sheet to be consolidated across all the files in the folder.

Excel VBA Is Fun

We believe Excel is one of the most versatile programs out there - and learning to program and automate it makes you very valuable!

You can prevent wasted time and errors, speed up tasks and become indispensable to your organization. 

See how our trainees have gotten raises, promotions and bonuses using their Excel Automation skills while building real problem-solving programs in Excel!
Copyright © 2024 ExcelVbaIsFun

Forgot Password?

Join Us

    Your Cart
    Your cart is emptyReturn to Shop
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram