CODEWITHSUNDEEP
arraysexcelvba
yoshiserry
yoshiserry

Reputation: 21355

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

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?

Upvotes: 1

Views: 241

Answers (2)

Ashwith Ullal
Ashwith Ullal

Reputation: 263

    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

Upvotes: 0

Peter Albert
Peter Albert

Reputation: 17485

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

Upvotes: 1

Related Questions