What I'm trying to do is to merge user selected CSV files with same header. I read the first csv file and put the header into an array. Then read the body data and put it into two dimension array like this.
Following is incomplete code. I removed some unconcerned codes for clarity.
Problem is I can't enlarge the csvFinalData row size while preserving data according to this site.
Is there any workaround to implement what I want to do? Any input would be greatly appreciated. Thanks for your precious time.
Following is incomplete code. I removed some unconcerned codes for clarity.
Code:
Dim csvHeader() As String
Dim csvFinalData() As String
Sub MergeCSV()
Dim Str As Variant
Dim fileName As String
Dim filePath As String
Dim csvHeader() As String
Dim csvFinalData() As String
For Each Str In fileList
fileName = fso.GetFileName(Str)
filePath = fso.GetFile(Str).ParentFolder.Path
ReadCSV filePath, fileName
Next Str
End Sub
Sub ReadCSV(filePath As String, fileName As String)
Dim csvData() As String
'Initialize data size based on OLEDB result set
ReDim csvData(0 To numberofRow, 0 To numberofColumn)
'Read csv and put into csvData() codes go here.
'~~~~~~~~> PROBLEM HERE <~~~~~~~~
'Enlarge the size of csvFinalData based on csvData size.
ReDim Preserve csvFinalData(UBound(csvFinalData, 1) + UBound(csvData, 1), UBound(csvFinalData, 2))
'Append data from csvData() to csvFinalData() codes go here.
'Pass csvFinalData() data as parameter to CSV Write function.
End Sub
Problem is I can't enlarge the csvFinalData row size while preserving data according to this site.
<code>Only the upper bound of the last dimension in a multidimensional array can be changed when you use the Preserve keyword; if you change any of the other dimensions, or the lower bound of the last dimension, a run-time error occurs. Thus, you can use code like this:
ReDim Preserve Matrix(10, UBound(Matrix, 2) + 1)
But you cannot use this code:
ReDim Preserve Matrix(UBound(Matrix, 1) + 1, 10)
</code>
Is there any workaround to implement what I want to do? Any input would be greatly appreciated. Thanks for your precious time.
Last edited: