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 in Others by Kithuzzz
• 34,760 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 by narikkadan
• 59,740 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer
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
• 59,740 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
• 59,740 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
• 22,970 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,630 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 in Others by narikkadan
• 59,740 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP