PDA

View Full Version : Array or Collection - building a file summary



theta
06-14-2012, 04:09 AM
Hi all...hoping to get some pointers/example from the pro's!

I have a folder (say C:\Files\) that contains several workbooks. I also have a Master summary workbook that is to contain the VBA code.

I need to build a list of all files in the folder mentioned above. Should I be creating a collection or building an array?

The macro will go through each file in turn, and summarise the contents of every tab in the sheet (this will just be the worksheets collection) > results stored in the Master workbook.

Just looking for some advice on the best approach.

Bob Phillips
06-14-2012, 12:29 PM
Unless you have a reason to use the properties of a collection, use an array all of the time.

You could use the code snb has been trying to get someone to use to get all of the files into an array

aryFiles= split(CreateObject("WScript.Shell").exec("cmd /c dir ""C:\Files\*.*"" /b /s").stdout.readall,vbCrlf)

and then iterate the loop

For i = LBound(aryFiles) To UBound(aryFiles)

Set wb = Workbooks.Open(aryFiles(i))
'do stuff
wb.Close SaveChanges:=False
Next i

theta
06-15-2012, 03:41 AM
Double post

theta
06-15-2012, 03:41 AM
Hmmm looks great (and i'm sure I will use this at some point) but I would rather use a more structured and likely faster approach.

I have the VBA to get the Registry Key which then gives me the correct base directory.

The array I need to build is not based on files (*.*) I just need a list of all folders in this base directory.

Every folder contains a file called DataFile.xls e.g.

Base\Folder 1\DataFile.xls
Base\Folder x\DataFile.xls

Kenneth Hobs
06-15-2012, 05:29 AM
What does double post mean? If you made another thread, you should mark one solved or post a link to the other and in particular if you cross-post to another forum.

Dim sf() As String
Dim pf As String
pf = ThisWorkbook.Path 'No trailing backslash.
sf() = Split(CreateObject("WScript.Shell").exec("cmd /c dir " & pf & "\*.*" & " /ad /b /s").stdout.readall, vbCrLf)
MsgBox Join(sf(), vbCrLf)

theta
06-15-2012, 05:46 AM
Sorry it means I hit enter twice.

I have tried this code and it works...I have the whole thing planned out in pseudocode so just need it VBA. Overall I would like to use this code to acheive :

1. List of all folders in directory (done)
2. If folders conains "Project.xls" then keep it, otherwise delete item
3. RedDim array

I am thinking it may even be better to do it this way

For Each Folder in Directory
If Dir(Folder\Project.xls) Then add to array
Next

Then I would end up with an array of folder names (with no need to redim) that contain Project.xls. This array will then be used to load the project workbooks one by one and report on their contents...

Any help appreciated, I know what I need to do but my VBA is letting me down.

Kenneth Hobs
06-15-2012, 10:45 AM
You would still need redim as you would not know the size of the array to subset. I suspect that you just need the full path. Why not iterate the array and not create another array? Another option is to iterate the array and create a collection or dictionary object.

This shows how to iterate the array and then to send the full path to a sub routine. Obviously, you need to change the value for pf and change ken.xls to your file name.

Sub t()
Dim sf() As String
Dim pf As String
Dim a As Variant, fn As String

pf = ThisWorkbook.Path 'No trailing backslash.
sf() = Split(CreateObject("WScript.Shell").exec("cmd /c dir " & pf & "\*.*" & " /ad /b /s").stdout.readall, vbCrLf)
'MsgBox Join(sf(), vbCrLf)

For Each a In sf()
fn = a & "\ken.xls"
If fn = "\ken.xls" Then fn = pf & fn
If Dir(fn) <> "" Then m (fn)
Next a
End Sub

Sub m(fn As String)
MsgBox fn
End Sub