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

Garry

I have not studied your code in full but one think that may help you
Instead of using the input box to get the file name change th
directory to the location of file and then use


DataFile $ = Application.GetOpenFilename("Text Files (*.*),*.txt"

--
Message posted from http://www.ExcelForum.com