Runtime error '53': File not found

See attached file, I hope it runs fine.
I think your problem is due to use of dir () together open file, because when using dir () with the opening of a file dir () gives problems.

Regards,
Antonio

Hi antoka05,

Thanks for taking the time to go over the code and providing your feedback. It is higly appreciated. This approach is new to me, but I'm new to vba

So you have replaced the original code for looping through files by using the Dir function:

Option Explicit

Sub GetTxtFiles_old()
Dim sFName          As String
Dim fPath           As String

    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\"
    End With

sFName = Dir$(fPath & "*.txt")

    Do While Len(sFName)

' Do something
    
sFName = Dir$()
    
Loop

End Sub

to this new code (which by the way works so far when the first code did not):

Option Explicit

Sub GetTxtFiles()
Dim fso             As Object
Dim fPath           As String
Dim myFolder, myFile
   
    Set fso = CreateObject("Scripting.FileSystemObject")
 
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\"
    End With
   
    Set myFolder = fso.GetFolder(fPath).Files
  
For Each myFile In myFolder

    If LCase(myFile) Like "*.txt" Then
    
' Do something
    
    End If
   
Next myFile

End Sub

So far, this has circumvented the issues caused by Dir and I will definately test it for speed and accurracy on multiple operating systems.

In general do you always use this method or only when Dir fails?

Thanks again.

abousetta

I prefer to use the file system object method because it is reliable and powerful.
With file system object it's possible move or rename files and folders, read in one isctruction all a text file and assign it to a variable.

Regards,
Antonio

Thanks Antonio for sharing your experience. Good advice that I will remember to use in the future when looping through files (which I need to do frequently).

Best wishes,

abousetta

If you don't open files during dir() loop you can use dir() but I remember when open a file in a dir () loop it's like losing pointing to files and gives in error.

Regards,
Antonio