A
Alan
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
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