Extract Specific cell data from Worksbooks in all subfolders

[Orfevre]

Hello, I need some help getting specific cell information into my report worksheet from all excel files in the folder and all subfolders. I have managed to get the code to open all the files in the directory but whenever I try to extract the cell information I get an error.

I am wanting it to run through each file, bring in information from multiple cells in all the files to create a report.

Sub copydata()
    Call GetFiles("D:\data\Rev\Analysis\records\files\")
End Sub

Sub GetFiles(ByVal path As String)
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Dim folder As Object
Set folder = fso.GetFolder(path)

Dim subfolder As Object
Dim file As Object

For Each subfolder In folder.SubFolders
    GetFiles (subfolder.path)
Next subfolder

Application.ScreenUpdating = False

For Each file In folder.Files
    Workbooks.Open (file)
    Range("A2").Offset(r).Value = .Range("C5").Value
    Range("B2").Offset(r).Value = .Range("D51").Value
    Workbooks.Close (file)
Next file

Set fso = Nothing
Set folder = Nothing
Set subfolder = Nothing
Set file = Nothing

End Sub

 

[Joe4]

Welcome to the Board!

I see one thing that would cause an error. Specifically, these two lines:

    Range("A2").Offset(r).Value = .Range("C5").Value
    Range("B2").Offset(r).Value = .Range("D51").Value

Note how you have the two parts bolded in red start with a period.
You can only do that if those lines are contained within a WITH block (because the periods tells it that it belongs to an object (sheet) specified in the WITH portion, i.e.)

With Sheets("Sheet1")
    Range("A2").Offset(r).Value = .Range("C5").Value
    Range("B2").Offset(r).Value = .Range("D51").Value
End With

To try to use bits of code that start with a period without using WITH will cause errors, because Excel cannot tell which object they are supposed to belong to.

See here for an explanation: VBA With Statement (With - End With)

When dealing with multiple files, you should probably qualify where all the ranges are coming from (what file and sheet they belong to).

 

[Orfevre]

Welcome to the Board!

I see one thing that would cause an error. Specifically, these two lines:

    Range("A2").Offset(r).Value = .Range("C5").Value
    Range("B2").Offset(r).Value = .Range("D51").Value

Note how you have the two parts bolded in red start with a period.
You can only do that if those lines are contained within a WITH block (because the periods tells it that it belongs to an object (sheet) specified in the WITH portion, i.e.)

With Sheets("Sheet1")
    Range("A2").Offset(r).Value = .Range("C5").Value
    Range("B2").Offset(r).Value = .Range("D51").Value
End With

To try to use bits of code that start with a period without using WITH will cause errors, because Excel cannot tell which object they are supposed to belong to.

See here for an explanation: VBA With Statement (With - End With)

When dealing with multiple files, you should probably qualify where all the ranges are coming from (what file and sheet they belong to).

Thank you I have amended per your suggestion which now runs, however no data is copied from the files I cannot understand why if it runs without error.

Sub Copdata()
Call GetFiles("D:\data\Rev\Analysis\records\files\")
End Sub

Sub GetFiles(ByVal path As String)
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Dim folder As Object
Set folder = fso.GetFolder(path)

Dim subfolder As Object
Dim file As Object

For Each subfolder In folder.SubFolders
    GetFiles (subfolder.path)
Next subfolder

Application.ScreenUpdating = False

For Each file In folder.Files
    Set fromWorkbook = Workbooks.Open(file)
    With fromWorkbook.Worksheets("Dashboard")
        Range("A2").Offset(r).Value = .Range("C5").Value
        Range("B2").Offset(r).Value = .Range("D51").Value
    End With
    fromWorkbook.Close savechanges:=False
Next file

Set fso = Nothing
Set folder = Nothing
Set subfolder = Nothing
Set file = Nothing

End Sub

 

[Joe4]

Your code seems to be missing a few things?
Where exactly are you trying to copy the data to?
The file with the VBA code/macros in it?

If so, you need to qualify that workbook too.
Otherwise, theses parts of your code (in red):

        Range("A2").Offset(r).Value = .Range("C5").Value
        Range("B2").Offset(r).Value = .Range("D51").Value

will just default to whatever the active workbook is at the time (and if you just open a file, that last opened file will be the active workbook).

Also, you have not set the "r" in your Offset command to anything.

 

[Orfevre]

Your code seems to be missing a few things?
Where exactly are you trying to copy the data to?
The file with the VBA code/macros in it?

If so, you need to qualify that workbook too.
Otherwise, theses parts of your code (in red):

        Range("A2").Offset(r).Value = .Range("C5").Value
        Range("B2").Offset(r).Value = .Range("D51").Value

will just default to whatever the active workbook is at the time (and if you just open a file, that last opened file will be the active workbook).

Also, you have not set the "r" in your Offset command to anything.

Thank for you help and easy to understand explanations

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Farrakh]

Hello, I need some help getting specific cell information into my report worksheet from all excel files in the folder and all subfolders. I have managed to get the code to open all the files in the directory but whenever I try to extract the cell information I get an error.

I am wanting it to run through each file, bring in information from multiple cells in all the files to create a report.

Sub copydata()
    Call GetFiles("D:\data\Rev\Analysis\records\files\")
End Sub

Sub GetFiles(ByVal path As String)
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Dim folder As Object
Set folder = fso.GetFolder(path)

Dim subfolder As Object
Dim file As Object

For Each subfolder In folder.SubFolders
    GetFiles (subfolder.path)
Next subfolder

Application.ScreenUpdating = False

For Each file In folder.Files
    Workbooks.Open (file)
    Range("A2").Offset(r).Value = .Range("C5").Value
    Range("B2").Offset(r).Value = .Range("D51").Value
    Workbooks.Close (file)
Next file

Set fso = Nothing
Set folder = Nothing
Set subfolder = Nothing
Set file = Nothing

End Sub

You have to do nothing.
No Vba required

Just follow the below some easy steps.

1. Make sure that your files have same table name (from where you want to copy the data)
2. Open you Master excel file where you want to get/copy the data
3. Click "Data"
4. Click "Get Data"
5. Select "From Folder"
6. Select your main Folder and click Open (this main folder may have contained many subfolders and files)
7. Excel will display a list of all files.
8. Click "Combine" and select "Combine & Load To"
9. Select Sample File "First File"
10. Click "Petameters"
11. Select the Table name which you want to import. On the right side, you can view the table data.
12. Click "OK"
13. Select "Table" option, and change nothing and click "Ok"
14. Excel will load the data from all files.
15. This data is linked to your selected folder, whenever you create a new file or edit any file, data will be updated in your Master File.

Thank you