how define a number of arrays in vba dynamically - one array for each file in a folder

Question

I have a folder which contains a few excel files.

In each of those files I want to create an array which will contain the data in the "data" sheet. Each workbook or file has this worksheet.

Only problem is there could be a different number of files in the folder each time.

I want to capture the contents of the "data" worksheet of each file in an array each and then write all this data to one single file.

Question Is it possible to dynamically create arrays based on for example the number of files in a folder?

If so how do you create those dynamic arrays?

Example Instead of having one array (which I change the size of) I want to ... (depending on the number of files in a folder for example create that many separate arrays?

e.g. 3 files in an folder

dim array01 dim array02 dim array03

Is it better to create one array per workbook in the folder - to store the contents of the "data" worksheet, or create one larger array?

Ok I get that you can change the size of an array with the ReDim Preserve, but HOW do you change the Number of arrays? — yoshiserry, Aug 22 '14 at 03:03
You could make a Multi-Dimensional array? See [this](http://stackoverflow.com/questions/16369217/redim-preserve-to-a-multi-dimensional-array-in-visual-basic-6) — Matt, Aug 22 '14 at 03:06
I think you want a multi-dimensional array. So array(0,1) would be the first file in the 0th folder etc. — serakfalcon, Aug 22 '14 at 03:09
if there are two files in the multidimensional array, how do you access the elements (columns, rows) of the data in the first file? Normally I would do a loop with ubound(array,1) ubound(array,2)? — yoshiserry, Aug 22 '14 at 04:25
actually to better describe it - how would I access the data value in the second file, the first column and the third row of data in that second file using array syntax? — yoshiserry, Aug 22 '14 at 04:29
somehow related: [Dynamically create dynamic arrays in VBA](http://stackoverflow.com/questions/18978839/dynamically-create-dynamic-arrays-in-vba/) — , Aug 22 '14 at 07:19

score 1 · Answer 1 · answered Aug 22 '14 at 05:32

Instead of using a multi-dimensional array and redim it all the time, consider storing each data array in a Collectionlike this:

Dim allData As Collection, data As Variant, file As Variant
Dim wb As Workbook, ws As Worksheet


Set allData = New Collection
file = Dir("c:\testfolder\*.xlsx")
While (file <> "")
    Set wb = Workbooks.Open(file)
    data = wb.Sheets(1).UsedRange.Cells  'Adjust this to get your data range
    allData.Add data, file
    file = Dir
Wend

Later you can use a For Each loop to retrieve the data:

Dim count As Integer
For Each data In allData
    count = count + 1
    Debug.Print "Dataset #" & count & " has " & _
        UBound(data, 1) & " x " & UBound(data, 2) & " entries"
Next

score 0 · Answer 2 · answered Oct 18 '15 at 05:43

    Sub MAIN()
   Dim FolderOfInterest As String, i As Long
    FolderOfInterest = "C:\TestFolder"
   Dim ary()
    ary = files_in_folder(FolderOfInterest)
   MsgBox "There are " & UBound(ary) & " files in folder " & FolderOfInterest
   '
   '  Now store the array in a worksheet column
'
i = 1
For Each a In ary
   Cells(i, "A").Value = a
  i = i + 1
Next a
End Sub

Public Function files_in_folder(folderS As String) As Variant
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set folder = fso.GetFolder(folderS)
   ReDim temp(1 To folder.Files.Count)

   i = 1
   For Each file In folder.Files
   temp(i) = file.Name
  i = i + 1
  Next file

   files_in_folder = temp
End Function

how define a number of arrays in vba dynamically - one array for each file in a folder

2 Answers2

Linked