Prev: Error 1004 setting xValues repeatedly
Next: create exe file
From: Alan on 18 Jul 2008 22:39
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

From: Joel on 19 Jul 2008 02:27
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
>
>
 | 
Pages: 1
Prev: Error 1004 setting xValues repeatedly
Next: create exe file