How To Initialize a VBA Array Using Multiple Text Files?

[eryksd]

Hoping to get some help on this, first time working with opening text files and 3 dimensional arrays. Getting an error on line:

For Each file In folder.Files

Problem:

I am trying to write a script that will open all the .txt files [within the working folder of the excel workbook], and initialize their contents into a 3-dimensional array. (Each text file's contents will be initialized into array variable "i")



Details:

The amount of text files (and file names) will vary whenever script is run, and the number of rows within the text files will vary (though the general structure will remain the same).

I am trying to initialize the array barcode_lookup(i - 1, j - 1, k) where:

i = number of text files in working directory
j = largest number of rows found across all text files (longest_lastRow)
k = 9 (fixed number of columns in each text file)



Sample text files:
Shipment Plan 1
Shipment Plan 2
Shipment Plan 3
Shipment Plan 4



References:
How to import all text files (column wise) from a folder?
How to get current working directory using vba?



Can anyone please help me with the code?

Option Explicit

Sub Initialize_barcode_lookup_Array()


 Dim fso As FileSystemObject
 Dim folder 'As folder
 Dim file As file
 Dim FileText As TextStream
 Dim TextLine As String
 Dim Items() As String
 Dim i As Long, j As Long, k As Long
 Dim cl As Range
 
 Dim shipping_plan As Long      'Number of shipping plans text files imported
 Dim barcode_lookup() As String
 Dim lastRow As Long
 Dim longest_lastRow As Long
 Dim counter As Long
 Dim count_txt_files As Long
 Dim FNSKU_Input As String
 
 


    ' Get a FileSystem object
    Set fso = New FileSystemObject


    ' get the directory you want
    folder = ActiveWorkbook.Path 'fso.GetFolder("D:\YourDirectory\")
       
    'Count the number of files in working directory ( - 1, for the Excel spreadsheet)
    For Each file In folder.Files
        count_txt_files = count_txt_files + 1
    Next file


    'Define longest_lastRow
    longest_lastRow = 0
    
    'Define i
    i = 0
    
    ' Loop thru all files in the folder
    For Each file In folder.Files
    'MsgBox file
        ' Open the file
        Set FileText = file.OpenAsTextStream(ForReading)
        
        'Define lastRow
        lastRow = Range("A1").End(xlDown).Row   'Last row of the data set
        
           'Make sure longest_lastRow is the largest value found of lastRow within all _
           'shipping plan .txt files
           If lastRow > longest_lastRow Then longest_lastRow = lastRow
        
        'Redimension Array barcode_lookup()
        ReDim barcode_lookup(count_txt_files - 1, longest_lastRow, 9)


        ' Read the file one line at a time
        Do While Not FileText.AtEndOfStream
            TextLine = FileText.ReadLine
            cl = TextLine
            
            'Initialize Array
                For j = 0 To (lastRow - 1) 'UBound(barcode_lookup(lastRow - 1))
                    For k = 0 To 9
                        barcode_lookup(i, j, k) = cl
                        cl = cl.Offset(0, k + 1).Value
                    Next k
                    'Set cl one row down, and set column back to 0
                    cl = cl.Offset(j + 1, k - 9)
                Next j
        Loop


        ' Clean up
        FileText.Close
        
        i = i + 1
    Next file


    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing


End Sub

Any help would be greatly appreciated.

 

[John_w]

I can see 2 problems with this part of your code:

        ' Open the file
        Set FileText = file.OpenAsTextStream(ForReading)
        
        'Define lastRow
        lastRow = Range("A1").End(xlDown).Row   'Last row of the data set
        
           'Make sure longest_lastRow is the largest value found of lastRow within all _
           'shipping plan .txt files
           If lastRow > longest_lastRow Then longest_lastRow = lastRow
        
        'Redimension Array barcode_lookup()
        ReDim barcode_lookup(count_txt_files - 1, longest_lastRow, 9)

1. Simply opening the file doesn't put the file contents into the sheet cells. To determine the number of lines in a file you could do this:

    Dim fileLines As Variant
    fileLines = Split(FileText.ReadAll, vbNewLine)
    If UBound(fileLines) > longest_lastRow Then longest_lastRow = UBound(fileLines)

You could then loop through the fileLines array instead of looping through the file one line at a time.

2. Every ReDim loses the current contents of the barcode_lookup array, which you populate further down the code. You can use ReDim Preserve, to preserve the data in the array, but this can only be used when you change the size of the last dimension, so you would have to change the order of the last 2 dimensions:

    ReDim Preserve barcode_lookup(count_txt_files - 1, 9, longest_lastRow)

with a corresponding change where you populate barcode_lookup