How To Initialize An Array Using Multiple Text Files - VBA

Question

Trying to create a loop to open each .txt file in the active workbook folder, to be able to pull data out of each .txt file.

(Data from .txt files will be used for initializing an array)

---

Current code I created gives Compile error Invalid Use of Property for line of code:
folder2 = ActiveWorkbook.path

I know that folder2 is defined as a Folder data type, and thus assigning the string ActiveWorkbook.path variable (to find current working folder location) is most likely causing the problem.

What would be the correct method of going through all the .txt files in the active workbook folder (or correct method of referencing the workbook folder path)?

---

Reference: How to import all text files from a folder

---

Code snippet with problems:

' Loop thru all files in the folder
    folder = ActiveWorkbook.path

    path = folder & "\*.txt"

    Filename = Dir(path)

    ' Get a FileSystem object
    Set fso = New FileSystemObject

    ' get the directory you want
    folder2 = ActiveWorkbook.path

    For Each file In folder.Files

        Set FileText = file.OpenAsTextStream(ForReading)

Full Code:

Option Explicit

Sub Initialize_barcode_lookup_Array()

 Dim fso As FileSystemObject
 Dim folder As String, path As String, count_txt_files As Long, Filename As String
 Dim folder2 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 FNSKU_Input As String


    'Count the number of files in working directory ( - 1, for the Excel spreadsheet)
        folder = ActiveWorkbook.path

        path = folder & "\*.txt"

        Filename = Dir(path)

        Do While Filename <> ""
            count_txt_files = count_txt_files + 1
                Filename = Dir()
        Loop

        'Range("Q8").Value = count
        MsgBox count_txt_files & " : files found in folder"


    'Define longest_lastRow
    longest_lastRow = 0

    'Define i
    i = 0

    ' Loop thru all files in the folder
    folder = ActiveWorkbook.path

    path = folder & "\*.txt"

    Filename = Dir(path)

    ' Get a FileSystem object
    Set fso = New FileSystemObject

    ' get the directory you want
    folder2 = ActiveWorkbook.path 'fso.GetFolder("D:\YourDirectory\")

    For Each file In folder.Files

        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 folder2 = Nothing
    Set fso = Nothing

End Sub

Answer 1

Try the GetFolder command to pick up a folder:

set folder2 = fso.getfolder(ActiveWorkbook.path)

Since it is an object variable, you need the Set command.

(Also note that folder is a reserved name in FSO, so you might want to use a different variable name just to avoid confusion and possible errors.)