Macro for creating a list of data on a sheet from a directory full of identical files

[gls2815]

I am trying to figure out how to create a macro that will look into a
specific directory on the hard drive filled with identical excel files,
pull information from the same specific cell on the same workbook in
each file, and then write it to the active workbook as a continuous
list. Essentially I'm trying to aggragate a list of the data (in this
case, a count of plywood boards used in each file, which represents an
individual construction job) from structurally-identical job files and
list them out. Does anyone have some pointers?
Thank you,
Gerard

 

[JE McGimpsey]

I am trying to figure out how to create a macro that will look into a
specific directory on the hard drive filled with identical excel files,
pull information from the same specific cell on the same workbook in
each file, and then write it to the active workbook as a continuous
list. Essentially I'm trying to aggragate a list of the data (in this
case, a count of plywood boards used in each file, which represents an
individual construction job) from structurally-identical job files and
list them out. Does anyone have some pointers?

Public Sub GetValuesFromDirectory()
Const sPATH As String = "<your path here>"
Const sCELL As String = "A1"
Dim vArray() As Variant
Dim wb As Workbook
Dim nCount
Dim sFileName As String

Application.ScreenUpdating = False
sFileName = Dir(sPATH, MacID("XLS8"))
Do While sFileName <> ""
nCount = nCount + 1
ReDim Preserve vArray(1 To nCount)
Set wb = Workbooks.Open(sPATH & sFileName)
vArray(nCount) = wb.Worksheets(1).Range(sCELL).Value
wb.Close
sFileName = Dir()
Loop
ActiveWorkbook.Sheets(1).Range("A1").Resize(nCount, 1).Value = _
Application.Transpose(vArray)
Application.ScreenUpdating = True
End Sub

For <your path here> substitute the entire path, e.g.:

HD:Users:YourNameocuments:SpecificDirectory:

(including the trailing colon). Replace the "A1"s as desired.

 

[gls2815]

JE,

Thank you for the reply. I tried your code but it seems to end with a
run-time 1004 error. I'm not sure what is causing it. Can you help?
Here is more information that may point to what may be causing the
1004:

The path is "Volumes:timocuments:Bid-folder:Bids-Accepted:"
The worksheet is "Labor Detail", which is the 4th sheet
The target cell is "R8C2" on that sheet

And of course all the excel files are identical in structure, just with
different file names. I forgot to make the distinction in what sheet
the target cell was previously. Any ideas?

Here is how I had it modified:

Public Sub GetValuesFromDirectory()
Const sPATH As String = "Volumes:timocuments:Bid
folder:Bids-Accepted:"
Const sCELL As String = "R8C2"
Dim vArray() As Variant
Dim wb As Workbook
Dim nCount
Dim sFileName As String
Application.ScreenUpdating = False
sFileName = Dir(sPATH, MacID("XLS8"))
Do While sFileName <> ""
nCount = nCount + 1
ReDim Preserve vArray(1 To nCount)
Set wb = Workbooks.Open(sPATH & sFileName)
vArray(nCount) = wb.Worksheets(1).Range(sCELL).Value
wb.Close
sFileName = Dir()
Loop
ActiveWorkbook.Sheets(1).Range("R1C1").Resize(nCount, 1).Value
= _
Application.Transpose(vArray)
Application.ScreenUpdating = True
End Sub

 

[JE McGimpsey]

JE,

Thank you for the reply. I tried your code but it seems to end with a
run-time 1004 error. I'm not sure what is causing it. Can you help?
Here is more information that may point to what may be causing the
1004:

The path is "Volumes:timocuments:Bid-folder:Bids-Accepted:"
The worksheet is "Labor Detail", which is the 4th sheet
The target cell is "R8C2" on that sheet

First, if your sheet is the fourth sheet, then either use

ActiveWorkbook.Sheets(4).Range(...

or

ActiveWorkbook.Sheets("Labor Detail).Range(...


Second, as explained in XL/VBA Help ("Range Property"), the argument to

Range()

must be an A1-style reference. You're using RC-style references. You can
either change sCELL to "B8" and

ActiveWorkbook.Sheets("Labor Detail").Range("R1C1")...

to

ActiveWorkbook.Sheets("Labor Detail").Range("A1")...


or use the Cells() method:

vArray(nCount) = wb.Worksheets(1).Cells(8, 2).Value


ActiveWorkbook.Sheets("Labor Detail").Cells(1, 1)...

 

[gls2815]

JE,

Thanks for the reply. I'm sorry I wasn't very clear by what I meant by
target. Within the target directory, the information to be pulled off
the workbook files resides on the 4th sheet ("Labor Detail") on cell B8
of each workbook file. I believe I have to define that at the beginning
as a Const statement? The goal is to look into the directory, pull off
the value in each B8 cell of the 'Labor Detail' sheet and list them in
the active workbook that contains only the macro.

 

[JE McGimpsey]

JE,

Thanks for the reply. I'm sorry I wasn't very clear by what I meant by
target. Within the target directory, the information to be pulled off
the workbook files resides on the 4th sheet ("Labor Detail") on cell B8
of each workbook file. I believe I have to define that at the beginning
as a Const statement? The goal is to look into the directory, pull off
the value in each B8 cell of the 'Labor Detail' sheet and list them in
the active workbook that contains only the macro.

Which is what the modifications I suggested do...

 

[gls2815]

JE,

I got it working. Thank you very much! Is there a way to do the loop
statement without the application opening each file during execution?

 

[JE McGimpsey]

I got it working. Thank you very much! Is there a way to do the loop
statement without the application opening each file during execution?

There is, if you use VBA to insert a reference to the workbook, sheet,
and cell into a cell in your current workbook. However, in the past I've
found that method to be slower and more error-prone.

Is there a particular reason that opening the files is a problem?

 

[gls2815]

Well, there are usually a fairly large number of files in the
directory, so the problem is then having to close them all after
executing the macro. After around 40 the application starts to run
short of memory and behave erratically.

 

[JE McGimpsey]

Well, there are usually a fairly large number of files in the
directory, so the problem is then having to close them all after
executing the macro. After around 40 the application starts to run
short of memory and behave erratically.

Did you include the

wb.Close

line I put in the macro I supplied? You shouldn't have to have all your
wb's open...

 

[gls2815]

Yes I did. But I also tried it without it. The problem with including
it is the files in question are rather complicated, and update internal
data just by opening them, so when I include wb.Close, each file
prompts me if I want to save changes to it. I suppose it could be
prevented, but there are now hundreds of files set up that way.

 

[JE McGimpsey]

Yes I did. But I also tried it without it. The problem with including
it is the files in question are rather complicated, and update internal
data just by opening them, so when I include wb.Close, each file
prompts me if I want to save changes to it. I suppose it could be
prevented, but there are now hundreds of files set up that way.

Use

wb.Close SaveChanges:=False

 

[gls2815]

Thanks JE. Works flawlessly now.

 

[gls2815]

JE, one question:

Is there a statement that I can put into this loop to have it leave a
gap of 5 rows between each iteration of the loop in the active
worksheet it is writing into?

 

[gls2815]

The reason why I am asking is that I modified the loop to pull 22
variables from each workbook in the directory and write them into 3
rows of 7 columns, in the range D6:K8. If I change the delcaration

nCount1 = nCount1 +1 to nCount1 - nCount1 + 6, the loop seems to start
deleting entries from the earlier variables and only leaves the last
row of variables from each loop intact.