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
– yoshiserry, Commented Aug 22, 2014 at 3:03
I think you want a multi-dimensional array. So array(0,1) would be the first file in the 0th folder etc. — serakfalcon
– serakfalcon, Commented Aug 22, 2014 at 3: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
– yoshiserry, Commented Aug 22, 2014 at 4:25

Peter Albert · Accepted Answer · 2014-08-22 05:32:02Z

1

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

answered Aug 22, 2014 at 5:32

Peter Albert

17.6k6 gold badges68 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ashwith Ullal · Accepted Answer · 2015-10-18 05:43:51Z

0

    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

answered Oct 18, 2015 at 5:43

Ashwith Ullal

2633 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related