Looping through a folder with Excel files and scraping date from selected cells usin VBA

0 votes

I want to execute a VBA macro that iterates through all of the Excel files in a given folder. The macro copies the data from four distinct cells (let's say A2, B3, C5, and D6) that I have labelled Region, DateSales, Sales, and Salesman after opening a single file, pastes it into a master file, and then closes the file.

Since I am not an expert in VBA, I need some assistance. I have found a VBA code, but it does not work.

This is the code I have right now

Sub getDataFromWbs()

    Dim wb As Workbook, ws As Worksheet
    Dim Region As String
    Dim DateSales As Date
    Dim Sales As Integer
    Dim Salesman As String

Application.ScreenUpdating = False

    Set fso = CreateObject("Scripting.FileSystemObject")

'Path to the folder
    Set fldr = fso.GetFolder("C:\Users\xxxxx\yyyyyy\Desktop\Sales\")

'Next available row in 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)

    Region = Sheets(1).Cells(1, 2).Value
    DateSales = Sheets(1).Cells(2, 3).Value
    Sales = Sheets(1).Cells(3, 5).Value
    Salesman = Sheets(1).Cells(4, 6).Value

'Loop through each sheet (ws)
Feb 16, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

You record the outcome of your comparison to the value of the cell. You observe if it will be TRUE or FALSE as a result. Try to alter these four lines of code to duplicate the actual data:

   ThisWorkbook.Sheets(1).Cells(y, 1) = ws.Cells(x, 1).Value ' Region
    ThisWorkbook.Sheets(1).Cells(y, 2) = ws.Cells(x, 2).Value ' DateSales
    ThisWorkbook.Sheets(1).Cells(y, 3) = ws.Cells(x, 3).Value ' Sales
    ThisWorkbook.Sheets(1).Cells(y, 4) = ws.Cells(x, 4).Value ' Salesman
answered Feb 16, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

VBA - Build a Two-Column Array By Looping Through one Array with a Specific Criteria and Selecting From Another Array

As already mentioned as comment: Loop over ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,720 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP