Importing .Txt files based on date last modified, then sort file by Comma Separation (VBA)---Error: "Run-time error '76: Path not Found"

[thelukeee]

Hello everyone,

I'd appreciate some coding help in Excel using VBA. I'm very new to this.


Background: There is a new .txt file made for each machine run, which contains details about the part and the weight gain. The .txt file is created automatically in the system, and goes unedited by anyone, therefore using the date last modified is a viable way to sort through the files.

Goal of project:
1. The purpose of this code is the pull all of the files located the file name textFilesFolder="_____".
2. List out all the lines in the .txt files in separate columns (a single text file would have multiple rows in excel)
3. To be able to only pull files that have a 'lastModifiedDate' that are within a desired range (between 'Start Date' and 'End Date')
This is the purpose of the For Each statement. The contents of the .txt file should separated into separate columns based on the commas in the text file.

Error: "Run-time error '76: Path not Found"

Public Sub Import_WeightGain_Text_Files()

    Dim TestBaseCell As Range
    Dim textFilesFolder As String
    Dim startDate As Date, endDate As Date
    Dim FSO As Object, FSfile As Object, ts As Object
    Dim LineItems As Variant
    Dim ws As Worksheet
    Dim row_number As Long

    

    Set ws = Sheets("Raw WG Data")
 
    ws.Cells.Clear
 
    textFilesFolder = "\\janedoe\HTM_MACH_WG2\New Weight Gain Storage\ARCH" '*folder that contains my text files*
 
    startDate = Worksheets("Intro").Range("B5").Value
    endDate = Worksheets("Intro").Range("B6").Value
 
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    row_number = 2 'to be placed under the column titles
    
    
    With Worksheets("Raw WG Data")
    
        For Each FSfile In FSO.GetFolder(textFilesFolder).Files
            
            
            If LCase(FSfile.Name) Like LCase("*.txt") And FSfile.DateLastModified >= startDate And FSfile.DateLastModified <= endDate Then
                
                Open FSfile For Input As #1
                
                
                Do Until EOF(1)
                    
                
                    Line Input #1, LineFromFile
                    LineItems = Split(LineFromFile, ",")
                        
                    ActiveCell.Offset(row_number, 1).Value = LineItems(3) 'Date
                    ActiveCell.Offset(row_number, 2).Value = LineItems(0) 'Lot NBR
                    ActiveCell.Offset(row_number, 3).Value = LineItems(1) 'J/N
                    ActiveCell.Offset(row_number, 4).Value = LineItems(2)     'Serial NBR
                    ActiveCell.Offset(row_number, 5).Value = LineItems(4)    'Pre-Weight (g)
                    ActiveCell.Offset(row_number, 6).Value = LineItems(5)     'Post-Weight (g)
                    ActiveCell.Offset(row_number, 7).Value = LineItems(6)    'Weight Diff (g)

   
                            
                    row_number = row_number + 1 'Each line from the .txt file has its own row
                Loop
                
                Close #1
                
            End If
        row_number = row_number + 1 'After the text file is finished, then another row is added to begin copying the data for the next .txt file
            
        Next
    End With
End Sub

Notes: This is a very similar project to my previous problem: Importing Multiple .Txt Files Into Excel Based on the Date Last Modified (VBA)

*This question is only posted on MrExcel here


Picture #1: Desired format to in excel
Picture #2: Folder that .Txt files are contained in
Picture #3: Example .Txt file

Thank you very much for any help.

 

[thelukeee]

Do I need to add more info to this? Not sure if it's clear enough..

 

[John_w]

You haven't said where the error occurs.

Error: "Run-time error '76: Path not Found"

I'm guessing this line:

For Each FSfile In FSO.GetFolder(textFilesFolder).Files

which probably means your textFilesFolder string is wrong. Try replacing the textFilesFolder = "xxxx" line with this to browse to the required folder:

    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing text files"
        If .Show Then
            textFilesFolder = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With

 

[thelukeee]

You haven't said where the error occurs.

I'm guessing this line:
which probably means your textFilesFolder string is wrong. Try replacing the textFilesFolder = "xxxx" line with this to browse to the required folder:

    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing text files"
        If .Show Then
            textFilesFolder = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With

Hey John_w,
thanks for the reply.


Hmmmm I didn't change anything in my code, just restarted my computer, then I got a different error. The new error is: "Run-time error '52: Bad file name or number"
I tried your code and I got the same error.

When I press F8 to scroll through the code, it doesn't show specificially when I get this error. It goes through all of the code, then repetedely iterates through the for loop. I believe this is what it should do. But I don't know where it is having issues.

Thanks,
thelukeee

 

[John_w]

Run the macro, when the error occurs click Debug on the error message and post the yellow highlighted line.

Also, it shouldn't make any difference, but change the Open line to:

Open FSfile.Path For Input As #1

 

[thelukeee]

Alright, I made that change as well as a couple different things to add. Even though it shouldn't have changed anything, I'm getting no error at all anymore. Nothing is printed on my "Raw WG Data" sheet, and I get no errors when I run my VBA... D: I have no idea what's going on.

Here's my current code. Everything is the same except textFilesFolder is changed for security

Public Sub Import_WeightGain_Text_Files()

    Dim textFilesFolder As String
    Dim startDate As Date, endDate As Date
    Dim FSO As Object, FSfile As Object
    Dim LineItems As Variant
    Dim ws As Worksheet
    Dim row_number As Long

    

    Set ws = Sheets("Raw WG Data")
 
    ws.Cells.Clear
 
    textFilesFolder = "\\johnwick\HTM_MACH_WG2\New Weight Gain Storage\ARCH" '*folder that contains my text files*
    

 
    startDate = Worksheets("Intro").Range("B5").Value
    endDate = Worksheets("Intro").Range("B6").Value
 
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    row_number = 2 'to be placed under the column titles
    
    
    Worksheets("Raw WG Data").Range("A1").Value = "Date"
    Worksheets("Raw WG Data").Range("B1").Value = "Lot NBR"
    Worksheets("Raw WG Data").Range("C1").Value = "J/N"
    Worksheets("Raw WG Data").Range("D1").Value = "Serial NBR"
    Worksheets("Raw WG Data").Range("E1").Value = "Pre-Weight (g)"
    Worksheets("Raw WG Data").Range("F1").Value = "Post-Weight (g)"
    Worksheets("Raw WG Data").Range("G1").Value = "Weight Diff (g)"
    
    
  
    
    For Each FSfile In FSO.GetFolder(textFilesFolder).Files
            
            
        If LCase(FSfile.Name) Like LCase("*.txt") And FSfile.DateLastModified >= startDate And FSfile.DateLastModified <= endDate Then
            
            Open FSfile.Path For Input As #1
        
            Do Until EOF(1)
                    
                
                Line Input #1, LineFromFile
                LineItems = Split(LineFromFile, ",")
                        
                Cells(row_number, 1).Value = LineItems(3) 'Date
                    'ActiveCell.Offset(row_number, 2).Value = LineItems(0) 'Lot NBR
                    'ActiveCell.Offset(row_number, 3).Value = LineItems(1) 'J/N
                    'ActiveCell.Offset(row_number, 4).Value = LineItems(2)     'Serial NBR
                    'ActiveCell.Offset(row_number, 5).Value = LineItems(4)    'Pre-Weight (g)
                    'ActiveCell.Offset(row_number, 6).Value = LineItems(5)     'Post-Weight (g)
                    'ActiveCell.Offset(row_number, 7).Value = LineItems(6)    'Weight Diff (g)

   
                            
                row_number = row_number + 1 'Each line from the .txt file has its own row
            Loop
            Close #1
        
            
    row_number = row_number + 1 'After the text file is finished, then another row is added to begin copying the data for the next .txt file
        
        End If
    Next
    


End Sub

 

[thelukeee]

I've been repeatedly running the same code to see if I could get anything, and finally instead of no errors, I get a "run-time error'55: File already open" I don't have a debug button when there is an error.

I'm guessing the problem is that it is opened twice in the code:
1.

For Each FSfile In FSO.GetFolder(textFilesFolder).Files

2

Open FSfile.Path For Input As #1

Also, it's weird that my code won't even print out what is specified here on my sheet:

Worksheets("Raw WG Data").Range("A1").Value = "Date"
    Worksheets("Raw WG Data").Range("B1").Value = "Lot NBR"
    Worksheets("Raw WG Data").Range("C1").Value = "J/N"
    Worksheets("Raw WG Data").Range("D1").Value = "Serial NBR"
    Worksheets("Raw WG Data").Range("E1").Value = "Pre-Weight (g)"
    Worksheets("Raw WG Data").Range("F1").Value = "Post-Weight (g)"
    Worksheets("Raw WG Data").Range("G1").Value = "Weight Diff (g)"

 

[John_w]

The For Each FSfile doesn't open the file. It returns the next file from the specified folder.

Only the Open FSfile.Path opens the file.

Try this macro in a fresh workbook:

Option Explicit

Public Sub Import_WeightGain_Text_Files()

    Dim textFilesFolder As String
    Dim startDate As Date, endDate As Date
    Dim FSO As Object, FSfile As Object, ts As Object
    Dim LineItems As Variant
    Dim ws As Worksheet
    Dim row_number As Long
    Dim lineFromFile As Variant
    Dim fileNum As Integer

    Set ws = Worksheets("Raw WG Data")
 
    textFilesFolder = "\\johnwick\HTM_MACH_WG2\New Weight Gain Storage\ARCH" '*folder that contains my text files*
    startDate = Worksheets("Intro").Range("B5").Value
    endDate = Worksheets("Intro").Range("B6").Value
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
 
    row_number = 2 'to be placed under the column titles
    
    With ws
    
        .Cells.Clear
        .Range("A1:G1").Value = Array("Date", "Lot NBR", "J/N", "Serial NBR", "Pre-Weight (g)", "Post-Weight (g)", "Weight Diff (g)")
    
        For Each FSfile In FSO.GetFolder(textFilesFolder).Files
            
            If LCase(FSfile.Name) Like LCase("*.txt") And FSfile.DateLastModified >= startDate And FSfile.DateLastModified <= endDate Then
                
                fileNum = FreeFile
                Open FSfile.Path For Input As #fileNum
                
                Do Until EOF(fileNum)
                
                    Line Input #fileNum, lineFromFile
                    LineItems = Split(lineFromFile, ",")
                        
                    .Cells(row_number, 1).Value = LineItems(3) 'Date
                    .Cells(row_number, 2).Value = LineItems(0) 'Lot NBR
                    .Cells(row_number, 3).Value = LineItems(1) 'J/N
                    .Cells(row_number, 4).Value = LineItems(2) 'Serial NBR
                    .Cells(row_number, 5).Value = LineItems(4) 'Pre-Weight (g)
                    .Cells(row_number, 6).Value = LineItems(5) 'Post-Weight (g)
                    .Cells(row_number, 7).Value = LineItems(6) 'Weight Diff (g)
                            
                    row_number = row_number + 1 'Each line from the .txt file has its own row
                    
                Loop
                
                Close #fileNum
                
            End If
            
        Next
        
    End With
    
End Sub

I wouldn't bother with disabling events and screen updates.

 

[thelukeee]

Hey John,

Thanks for the reply. I tried to copy that code into my existing file, then there was an error. However, I created a new workbook like you said and it ran perfectly. It does exactly what I want it to do.

Thanks for the help again,
thelukeee