File not found (error 1004)
 

I modified some VBA code to find an Excel Workbook file in a
directory hierarchy and open it. When I attempt to open it, it says
it cannot find the file. However, a message box just prior indicates
that it can indeed find the file.

I capture the filename in a string s:

s = Dir$(currdir, vbDirectory)

Later I have the two lines I mentioned above:

MsgBox ("File Found: " & s) 'for debugging
Workbooks.Open (s)

Why does the Open not find the file?

The complete code may be found below. I am new to VBA. Thanks
in advance.

Alan

Complete Code:

Sub FixCreateAndPrintAllCharts()
'
'
'
Dim StartDir As String
StartDir = ThisWorkbook.Path
Dim s As String
Dim currdir As String
Dim dirlist As New Collection

If Right$(StartDir, 1) < "\" Then StartDir = StartDir & "\"
dirlist.Add StartDir

While dirlist.Count
currdir = dirlist.Item(1)
dirlist.Remove 1
s = Dir$(currdir, vbDirectory)
While Len(s)
If (s < ".") And (s < "..") Then 'get rid of parent and
current directory
If GetAttr(currdir & s) = vbDirectory Then 'add the
subdirectory
dirlist.Add currdir & s & "\"
Else 'process the file if it is right name
If (s Like "FIXED_PerfWiz?*.csv") Then
MsgBox ("File Found: " & s)
Workbooks.Open (s)

LResult = Replace(ActiveWorkbook.FullName,
"FIXED_", "Data_Charts_FIXED_")
LResult = Replace(LResult, ".csv", ".xls")
ActiveWorkbook.SaveAs Filename:=LResult _
, FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False,
CreateBackup:=False
ActiveWorkbook.Close
End If
End If
End If
s = Dir$
Wend
Wend
Application.DisplayAlerts = False
Application.Quit
End Sub

File not found (error 1004)
 

Dir does not return the path and the open requires the path

from
Workbooks.Open (s)
to
Workbooks.Open (currdir & "\" & s)

"Alan" wrote:

I modified some VBA code to find an Excel Workbook file in a
directory hierarchy and open it. When I attempt to open it, it says
it cannot find the file. However, a message box just prior indicates
that it can indeed find the file.

I capture the filename in a string s:

s = Dir$(currdir, vbDirectory)

Later I have the two lines I mentioned above:

MsgBox ("File Found: " & s) 'for debugging
Workbooks.Open (s)

Why does the Open not find the file?

The complete code may be found below. I am new to VBA. Thanks
in advance.

Alan

Complete Code:

Sub FixCreateAndPrintAllCharts()
'
'
'
Dim StartDir As String
StartDir = ThisWorkbook.Path
Dim s As String
Dim currdir As String
Dim dirlist As New Collection

If Right$(StartDir, 1) < "\" Then StartDir = StartDir & "\"
dirlist.Add StartDir

While dirlist.Count
currdir = dirlist.Item(1)
dirlist.Remove 1
s = Dir$(currdir, vbDirectory)
While Len(s)
If (s < ".") And (s < "..") Then 'get rid of parent and
current directory
If GetAttr(currdir & s) = vbDirectory Then 'add the
subdirectory
dirlist.Add currdir & s & "\"
Else 'process the file if it is right name
If (s Like "FIXED_PerfWiz?*.csv") Then
MsgBox ("File Found: " & s)
Workbooks.Open (s)

LResult = Replace(ActiveWorkbook.FullName,
"FIXED_", "Data_Charts_FIXED_")
LResult = Replace(LResult, ".csv", ".xls")
ActiveWorkbook.SaveAs Filename:=LResult _
, FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False,
CreateBackup:=False
ActiveWorkbook.Close
End If
End If
End If
s = Dir$
Wend
Wend
Application.DisplayAlerts = False
Application.Quit
End Sub