View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
mike mike is offline
external usenet poster
 
Posts: 216
Default DIR Function Question

I'm trying to rename files and then move them to a new
folder. The code to rename works. The code to move
works. They don't work together. Attached is the complete
code. Files in the original folder will rename using the
filecopy. Kill works and the original file is gone but the
move never takes place. If I comment out the copy and
kill pharses then the move works fine. What am I doing
wrong?

Thanks
Mike

Sub TEST()
Dim oFSO As Object
Dim inFILE, outFILE, fNAME, fromFOLDER, toFOLDER

Set oFSO = CreateObject("Scripting.FileSystemObject")

inFILE = Dir("C:\DVW\REPORTS\")
fNAME = oFSO.getbasename(inFILE)
oFSO.copyfile inFILE, fNAME & " NGPS" & ".xls"
Kill inFILE

Do While inFILE < ""
inFILE = Dir
fNAME = oFSO.getbasename(inFILE)
On Error GoTo OOPS
oFSO.copyfile inFILE, fNAME & " NGPS" & ".xls"
Kill inFILE
Loop

fromFOLDER = "C:\DVW\REPORTS\*.XLS"
toFOLDER = "C:\DVW\REPORTS\D9\"

oFSO.movefile fromFOLDER, toFOLDER

Set oFSO = Nothing
OOPS:
If Err < 0 Then
Err.Clear
End If
End Sub