1

My VBA code loops through all subfolders and all files to collect 7 pieces of information from each file, and writes these pieces of information into an Excel spreadsheet.

The number of file attributes is fixed at 7, however I have an unknown number of files inside an unknown number of folders. After running it a few times, I know that I have nearly 10,000 rows in my spreadsheet, and each time I run my macro it takes a very long time. Performance is an issue as I am doing nearly 10,000 write operations and it writes the array to the file each time.

Instead, I would like to create a 2-D array that can add a new row to this 2-D array for each new file it comes across. Then, once it has iterated through all the files, it uses just one write operation to write the entire 2-D array to the spreadsheet.

I am well aware of VBA ReDim Preserve. However, this would require me to still call ReDim Preserve nearly 10,000 times which is also an expensive operation. I also need to iterate through each file individually to do some processing on the data belonging to it.

And so I was wondering if there it is possible for VBA 2-D arrays to work something like

dim file_data(, 7) as String

I have read this question here, but clearly this can't work as the highest voted answer still prescribed printing for each file.

Johnny
  • 320
  • 3
  • 12
  • Can you define the array off the row count, and then use the row number as the point to insert, so 5 rows a is defined as `a(rows.count)`=`a(5)` and then add, row 1 `a(1)` or `a(0)` – Nathan_Sav May 16 '22 at 16:06
  • 2
    One option would be to programmatically determine the number of files first, before reading them. – BigBen May 16 '22 at 16:08
  • 1
    Collect all of the file paths before you start processing them, so you know exactly what size array you need. Alternatively, you can resize your output array in "chunks", or just start with an array which is larger than you're going to need (eg. 20k/30k rows)... – Tim Williams May 16 '22 at 16:11
  • @Nathan_sav the thing is that this folder will grow and shrink over time, therefore it's not constant and I can't hardcode it. – Johnny May 16 '22 at 16:17
  • @TimWilliams but if I have to declare an array with size 20,000. Won't the remaining other 10,000 rows need to be populated before I can add it into my sheet? – Johnny May 16 '22 at 16:18
  • Maybe use the count of files in the folder if possible? – Nathan_Sav May 16 '22 at 16:21
  • 1
    `Sheet1.Range("A1").Resize(actualNumberOfLines, 7).value = file_data` (for example) would only write the first `actualnumberOfLines` rows from your array to the sheet – Tim Williams May 16 '22 at 16:25
  • Or just write the entire array, since it's a `String` array you're effectively writing blank strings to the sheet for any unpopulated element. – BigBen May 16 '22 at 16:26

0 Answers0