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.
a(rows.count)=a(5)and then add, row 1a(1)ora(0)Sheet1.Range("A1").Resize(actualNumberOfLines, 7).value = file_data(for example) would only write the firstactualnumberOfLinesrows from your array to the sheet