Filling a temporary array to avoid using Preserve in a dynamic multidimensional array

Question

As a newbie to VBA, I'm trying to learn most of this ad-hoc content, so forgive my dust.

I have a routine that I am writing to read through lines in a text file. Each line represents a space designated as a "text identifier". I want each row to be divided into fields as a multidimensional array.

Sub ReadLines()
Dim LineValues() As String
Dim row As Long, col As Long
Dim DataArray() As String
Dim TempArray() As String
Dim FileContent As String
Dim FilePath As String

FilePath = "c:\mytextfile.txt"
row = 0
TextFile = FreeFile
Open FilePath For Input As TextFile
FileContent = Input(LOF(TextFile), TextFile)
Close TextFile
LineValues = Split(FileContent, vbCrLf)

For X = LBound(LineValues) To UBound(LineValues) 
 If Len(Trim(LineValues(X))) <> 0 Then
 DataArray = Split(LineValues(X), "'") 
 col = UBound(DataArray)
 TempArray = DataArray
 ReDim DataArray(col, row) 
 For i = LBound(TempArray) To UBound(TempArray)
 DataArray(i, row) = TempArray(i)
 Next i
 End If
 row = row + 1 
Next X

I came to this piece of code after struggling with multidimensional issues with ReDim Preserve. (only the ability to change the last dimension) A multidimensional array in my text file will have unknown columns and rows depending on user input.

... ! , (TempArray), ReDim ( ) (DataArray), DataArray .

, , ,   DataArray = Split (LineValues ​​(X), "'" )

, , ( ) .

, , - ? !

,

, , script , . - ( )

 '<irrelevant text I want to ignore until seeing pattern 'NumberOfVariables?'>
    ...
    ...
    NumberOfVariables?
    NUMBEROFVARIABLES
    'for the end user, I need to be able to pull information from each of these fields assigned to a variable to create strings as headers as per a specific format
'note that variable and variable type 
    Variable#1 VARIABLETYPE Location? LOCATION UNITS DESCRIPTION 'text for each field is enclosed as follows '' (code formatting on site prevents me doing this)
    Variable#2 VARIABLETYPE Location? LOCATION UNITS DESCRIPTION
    ...
    Variable#NUMBEROFVARIABLES
    ' from here there is a column of data that is assigned to each variable such that
    Variable#1Element1         Variable#2Element1       'etc until #NUMBEROFVARIABLES
    Variable#1Element2         Variable#2Element2 
    Variable#1Element3         Variable#2Element3 
    Variable#1FinalElement     Variable#2FinalElement 

- script , , , .

, Excel.

- - MsgBox , , , !

Answer 1

. :

Sub ReadLines()

    Const FilePath$ = "c:\mytextfile.txt"

    Dim iFile%, c&, i&, j&, k&, Content$, Lines, Temp, Data

    c = 499
    Open FilePath For Input As #iFile
    Content = StrConv(InputB(LOF(iFile), iFile), vbUnicode)
    Close #iFile

    Lines = Split(Content, vbCrLf)
    ReDim Data(0 To UBound(Lines), 0 To c)

    For i = 0 To UBound(Lines)
        If Len(Trim$(Lines(i))) Then
            Temp = Split(Lines(i), "'")
            If k < UBound(Temp) Then k = UBound(Temp)
            If k > c Then
                c = k * 2
                ReDim Preserve Data(0 To UBound(Lines), 0 To c)
            End If
            For j = 0 To UBound(Temp)
                Data(i, j) = Temp(j)
            Next
        End If
    Next
    ReDim Preserve Data(0 To UBound(Lines), 0 To k)

End Sub

Answer 2

, .

, , , , , .txt .

Workbooks.OpenText : TextQualifier:=xlTextQualifierSingleQuote ( , ).

"" ( ).

UsedRange.Value .