Return Cell value for all files in a folder (including sub folders)

[scotsrule08]

I am needing to retrieve the account number from every excel document in a folder including sub folders (roughly 3000 excel files) . The account number is in the same cell (G23) on all excel workbooks.

Ideally the macro would return cell value in column A and in column B have the file location.


Example

A: B:
123123123 :/Users/MyDocuments/Completed
124121241 :/Users/MyDocuments/NeedsToBeWorked


TIA for any help

 

[yaroslav89]

Try this code. This should get you started.

Sub Extract_Data()

'First Dimention "x" = to Workbook and "y" = to Workbook.
'Second Dimention "ws1" = Worksheet and "ws2" = to Worksheet.
Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

'"x" is a path where data is copied.
'"y" is a path where to paste copied data.
Set x = Workbooks.Open("C:\124121241 :/Users/MyDocuments/NeedsToBeWorked")
Set y = Workbooks.Open("C:\123123123 :/Users/MyDocuments/Completed ")

'First set is determining what Sheet to copy data from. Notice that ws1= to "x" which is a path where data is copied from.
'Second set is determining what Sheet to paste copied data. Notice that ws2= to "y" which is path where data is pasting.
Set ws1 = x.Sheets("Sheet1")
Set ws2 = y.Sheets("Sheet1")

'This is stating what range to copy and from which Worksheet follow by what Worksheet to paste.
'Note that paste range is specified and needs to be changed for every new Worksheet that is being copied.
ws1.Cells.Range("A1:R6").Copy ws2.Range("A1")

'This is to close every Workbook that is open
y.Close True
x.Close False

Hope this helps.

 

[scotsrule08]

I really was hoping to accomplish this task without having to open and close every workbook, but I understand if it’s not feasible.

Try this code. This should get you started.

Sub Extract_Data()

'First Dimention "x" = to Workbook and "y" = to Workbook.
'Second Dimention "ws1" = Worksheet and "ws2" = to Worksheet.
Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

'"x" is a path where data is copied.
'"y" is a path where to paste copied data.
Set x = Workbooks.Open("C:\124121241 :/Users/MyDocuments/NeedsToBeWorked")
Set y = Workbooks.Open("C:\123123123 :/Users/MyDocuments/Completed ")

'First set is determining what Sheet to copy data from. Notice that ws1= to "x" which is a path where data is copied from.
'Second set is determining what Sheet to paste copied data. Notice that ws2= to "y" which is path where data is pasting.
Set ws1 = x.Sheets("Sheet1")
Set ws2 = y.Sheets("Sheet1")

'This is stating what range to copy and from which Worksheet follow by what Worksheet to paste.
'Note that paste range is specified and needs to be changed for every new Worksheet that is being copied.
ws1.Cells.Range("A1:R6").Copy ws2.Range("A1")

'This is to close every Workbook that is open
y.Close True
x.Close False

Hope this helps.