1

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

1 Answer 1

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.)

Sign up to request clarification or add additional context in comments.

6 Comments

Thanks vacip, I am beginning to understand how fso.getfolder() works a bit better. That part of code works, but getting an error now, 19 lines below, for the code snippet 'cl = TextLine'. Trying to read up on how to fix it- any suggestions by any chance?
Pretty sure I need to break up the text file into an array, looks like it's text-tab delimited. Currently trying to modify my code to use the technique in this guide: thespreadsheetguru.com/blog/vba-guide-text-files
How can I make the loop only open .txt files? (Currently it's also opening the excel file I'm working from)
cl is a range, and you never set it. Use something like Set cl=range("A1"). Then you can go and give it a value: cl.Value=TextLine.
To break up a string into multiple substrings, use the Split command.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.