Need to enlarge row size of array dynamically while preserving data in vba

min9ox2

New Member
Joined
Aug 18, 2014
Messages
4
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.


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:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Forum statistics

Threads
1,215,064
Messages
6,122,936
Members
449,094
Latest member
teemeren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top