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

 

[RoryA]

Once again, you might give us a clue as to where the error occurs? Just posting a block of code with no explanation not only doesn't help us to help you, it doesn't really encourage us to try...

 

[wideboydixon]

strTextFile = MyFolder & "\" & MyFile

WBD

 

[wideboydixon]

Once again, you might give us a clue as to where the error occurs? Just posting a block of code with no explanation not only doesn't help us to help you, it doesn't really encourage us to try...

I kinda liked the challenge

 

[RoryA]

I kinda liked the challenge

I used to...

 

[daverunt]

WBD - Does that account for?

strTextFile = MyFileName & ".txt"

 

[haribole]

So sorry !! Please !!

I stuck here with Run time error 53 msg:

strData = objFSO.OpenTextFile(strTextFile, ForReading).ReadAll

 

[wideboydixon]

WBD - Does that account for?

strTextFile = MyFileName & ".txt"

No it doesn't but hopefully the OP will realise the pattern

WBD

 

[wideboydixon]

So sorry !! Please !!

I stuck here with Run time error 53 msg:

strData = objFSO.OpenTextFile(strTextFile, ForReading).ReadAll

Dir() returns only the file name; not the full path. You need to add the path on to the beginning or add a line near the beginning:

ChDir MyFolder

WBD

 

[haribole]

Accordingly I have added path (and minimized the code) but still not working !!

Please help !!

Dim wb As Workbook
Dim ws As Worksheet
Dim objFSO, strTextFile, strData, arrLines, LineCount
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   'Added the path
                   strData = objFSO.OpenTextFile(strTextFile, ForReading).ReadAll      'Here is the error msg [I][COLOR=#ff0000]Run time error '91': Object variable or Block variable not set[/COLOR][/I]
                   arrLines = Split(strData, vbCrLf)
                   LineCount = UBound(arrLines)
                   Set objFSO = Nothing
            MyFileName = Replace(MyFile, ".txt", "")
            Worksheets(1).Cells(i + 1, 1) = i         'Fist row having heading
            Worksheets(1).Cells(i + 1, 2) = MyFileName
            Worksheets(1).Cells(i + 1, 3) = LineCount
    MyFile = Dir
Loop