Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-Time error '1004': No cells were found | Excel Programming | |||
Run time error 1004: "No list was found.." | Excel Programming | |||
Microsoft Visual Basic Run-time error '1004' No cells were found | Excel Programming | |||
Run-time error '1004': No cells were found | Excel Programming | |||
Run-time error '1004': No cells were found | Excel Programming |