G
Garry
I am attempting to write a macro using Excel 2000 running in Windows 98.
The macro takes a file (the file names differ only by the
numerical part) in one directory and copies it into
another directory with a different name. As written, the
macro gives an error message "File not found".
If I change the code as noted, it works. The difference is
in the way the value of the variable DataFileToCopy is
assigned.
Sub RUN()
'
'
Dim Iterations, UnderscorePosition, wLen, NumChars, z, _
UnderChar, NeededChar As Integer
Dim OutputFileName, TableFile, fBase, DataFileToCopy, _
DataFile, WorkingFile As String
Dim fs As Object
DataFile = InputBox("ENTER THE NAME OF THE BASE DATA _
FILE, INCLUDING EXTENTION ")
Set fs = CreateObject("Scripting.FileSystemObject")
NumChars = Len(DataFile)
UnderscorePosition = InStr(DataFile, "_")
z = UnderscorePosition
UnderChar = 0
Do While UnderChar <> 92
UnderscorePosition = UnderscorePosition - 1
UnderChar = Asc(Mid(DataFile, UnderscorePosition, 1))
Loop
wLen = z - (UnderscorePosition + 1)
fBase = Mid(DataFile, UnderscorePosition + 1, wLen)
NeededChar = NumChars - (NumChars - z)
WorkingFile = "c:\nmv\run\" & fBase & "_100" & ".CSV"
DataFileToCopy = Left(DataFile, NeededChar) & "_200" & ".CSV"
TableFile = "c:\nmv\run\" & fBase & "_1" & ".PRN"
OutputFileName = "c:\nmv\run\" & fBase & "_1" & ".OUT"
' If I define DataFileToCopy as next line, all is well
'DataFileToCopy = "c:\nmv\run\input data\outsample_200.csv"
Msgbox (DataFileToCopy) ' temp line to check contents of DataFileToCopy
fs.CopyFile DataFileToCopy, WorkingFile
' If I substitute the line below for the line above, all is well
' fs.CopyFile "c:\nmv\run\input data\outsample_200.csv",WorkingFile
End Sub
Can anyone suggest a solution to the Error 53, File Not Found problem?
TIA
The macro takes a file (the file names differ only by the
numerical part) in one directory and copies it into
another directory with a different name. As written, the
macro gives an error message "File not found".
If I change the code as noted, it works. The difference is
in the way the value of the variable DataFileToCopy is
assigned.
Sub RUN()
'
'
Dim Iterations, UnderscorePosition, wLen, NumChars, z, _
UnderChar, NeededChar As Integer
Dim OutputFileName, TableFile, fBase, DataFileToCopy, _
DataFile, WorkingFile As String
Dim fs As Object
DataFile = InputBox("ENTER THE NAME OF THE BASE DATA _
FILE, INCLUDING EXTENTION ")
Set fs = CreateObject("Scripting.FileSystemObject")
NumChars = Len(DataFile)
UnderscorePosition = InStr(DataFile, "_")
z = UnderscorePosition
UnderChar = 0
Do While UnderChar <> 92
UnderscorePosition = UnderscorePosition - 1
UnderChar = Asc(Mid(DataFile, UnderscorePosition, 1))
Loop
wLen = z - (UnderscorePosition + 1)
fBase = Mid(DataFile, UnderscorePosition + 1, wLen)
NeededChar = NumChars - (NumChars - z)
WorkingFile = "c:\nmv\run\" & fBase & "_100" & ".CSV"
DataFileToCopy = Left(DataFile, NeededChar) & "_200" & ".CSV"
TableFile = "c:\nmv\run\" & fBase & "_1" & ".PRN"
OutputFileName = "c:\nmv\run\" & fBase & "_1" & ".OUT"
' If I define DataFileToCopy as next line, all is well
'DataFileToCopy = "c:\nmv\run\input data\outsample_200.csv"
Msgbox (DataFileToCopy) ' temp line to check contents of DataFileToCopy
fs.CopyFile DataFileToCopy, WorkingFile
' If I substitute the line below for the line above, all is well
' fs.CopyFile "c:\nmv\run\input data\outsample_200.csv",WorkingFile
End Sub
Can anyone suggest a solution to the Error 53, File Not Found problem?
TIA