Run time error '53': File not found

[haribole]

Dim wb As Workbook
Dim ws As Worksheet
Dim objFSO, strTextFile, strData, arrLines, LineCount
Dim ReceivedTrans, Mis As Integer
Dim MyFolder, MyFile, MyFileName, MySheetName, ReportDate As String
Dim FileType As String
Dim i, j As Integer
Const ForReading = 1
i = 1
j = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
MyFolder = "D:\TotalCompare"
MyFile = Dir(MyFolder & "\*.txt")
Do While MyFile <> ""
    If Left(MyFile, 21) = "ReverseFile_FOOD4514_" Or Right(MyFile, 12) = ".xls_RES.txt" Then
           If Left(MyFile, 21) = "ReverseFile_FOOD4514_" Then
                   strTextFile = MyFile
                   strData = objFSO.OpenTextFile(strTextFile, ForReading).ReadAll
                   arrLines = Split(strData, vbCrLf)
                   LineCount = UBound(arrLines)
                   Set objFSO = Nothing
              MySheetName = Replace(MyFile, "txt", "")
              ReportDate = Right(Replace(MySheetName, ".", ""), 8)
              MyFileName = Replace((Replace(MySheetName, "_" & ReportDate & ".", "")), "ReverseFile_FOOD4514_", "")
              ReceivedTrans = LineCount
              On Error Resume Next
                   strTextFile = MyFileName & ".txt"
                   Set objFSO = CreateObject("Scripting.FileSystemObject")
                   strData = objFSO.OpenTextFile(strTextFile, ForReading).ReadAll
                   arrLines = Split(strData, vbCrLf)
                   LineCount = UBound(arrLines)
                   Set objFSO = Nothing
              Mis = LineCount
          End If
    End If
MyFile = Dir
 Loop

 

[daverunt]

I'm not seeing that error on my PC, it opens the file and reads the content into strData.

Try using Set strData= objFSO.OpenTextFile(.......etc

also sort out DIMs
Dim objFSO, strTextFile, strData, arrLines, LineCount ??? Objects, strings and integers

 

[haribole]

Yes, you right it works for first file and show error msg for next file !!!

 

[wideboydixon]

Please post your current code.

WBD

 

[haribole]

Thank You Please see:

Dim objFSO As Object
Dim strTextFile As String
Dim strData As Variant
Dim arrLines() As String
Dim LineCount As Integer
Dim MyFolder, MyFile, MyFileName As String
Dim i As Integer
Const ForReading = 1
i = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
MyFolder = "D:\TotalCompare"
MyFile = Dir(MyFolder & "\*.txt")
Do While MyFile <> ""
                   strTextFile = MyFolder & "\" & MyFile
                   strData = objFSO.OpenTextFile(strTextFile, ForReading).ReadAll
                   arrLines = Split(strData, vbCrLf)
                   LineCount = UBound(arrLines)
                   Set objFSO = Nothing
            MyFileName = Replace(MyFile, ".txt", "")
            Worksheets(1).Cells(i + 1, 1) = i
            Worksheets(1).Cells(i + 1, 2) = MyFileName
            Worksheets(1).Cells(i + 1, 3) = LineCount
    MyFile = Dir
Loop

 

[daverunt]

Hi

There's no file system object when you loop to the second file as you have set it to nothing inside of the loop.
Set it to nothing outside of the loop.

   MyFile = Dir
Loop
Set objFSO = Nothing
End Sub