I have a workbook that opens txt files in worksheets, one file per sheet.
i use Logfiles = ThisWorkbook.Path so that where it searches within the directory the workbook is located. Works fine when I open excel and navigate to the directory where the workbook exist. When I click directly on the file and I run the macro I get a "Run-time error '53': File Not Found"
[VBA]
Sub Log_Cleaner()
Dim numfiles As String
DeleteAll
FileLoc = ThisWorkbook.Path
FileCountA (FileLoc)
End Sub
Function FileCountA(Path As String) As Long
Dim strTemp As String
Dim lngCount As Long
Dim C3Po As String
Sheets("Variables").Select
C3Po = Range("C3").Value
If C3Po = "" Then
C3Po = "*.*"
End If
strTemp = Dir(Path & "\" & C3Po)
If strTemp = "" Then
MsgBox "No files found that fit Search criteria", , "CM MESSAGE"
Exit Function
End If
ImportTextFile strTemp, "!@#", 0
Do While strTemp <> ""
lngCount = lngCount + 1
strTemp = Dir
If strTemp = "" Then
Exit Function
End If
ImportTextFile strTemp, "!@#", lngCount
Loop
End Function
[/VBA]