Macro to move files gets an error if it already exists...

[Hendy88]

Hello all,

I have the following macro snippet that moves a txt file AFTER its
data has been imported into a spreadsheet. It works great as long as
the file doesn't already exist.

Function TXTFileMove()

OldFilePath = "C:\Import Files\New\" & TXTFileName
NewFilePath = C"\Import Files\Complete\" & TXTFileName
Name OldFilePath As NewFilePath

End Function

If the file already exists in that location, the macro halts with the
error:

Run-time error '58': File already exists [End] [Debug]

I've tried enclosing the "path" commands with
Application.DisplayAlerts = False, but it didn't work.

 

[JLatham]

KILL the NewFilePath file before attempting to copy it with the Name command.

On Error Resume Next
KILL C"\Import Files\Complete\" & TXTFileName
If ERR<> 0 Then
ERR.CLEAR
End IF

 

[JLatham]

Or you could simply use
KILL NewFilePath
since you've already set that up in your code. Got in too big of a rush to
copy. So

Function TXTFileMove()

OldFilePath = "C:\Import Files\New\" & TXTFileName
NewFilePath = C"\Import Files\Complete\" & TXTFileName
On Error Resume Next
KILL NewFilePath
If ERR<>0 Then
ERR.CLEAR
End If
On Error Goto 0
Name OldFilePath As NewFilePath

End Function

 

[Ron de Bruin]

Or use FSO
http://www.rondebruin.nl/folder.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

KILL the NewFilePath file before attempting to copy it with the Name command.

On Error Resume Next
KILL C"\Import Files\Complete\" & TXTFileName
If ERR<> 0 Then
ERR.CLEAR
End IF

Hello all,

I have the following macro snippet that moves a txt file AFTER its
data has been imported into a spreadsheet. It works great as long as
the file doesn't already exist.

Function TXTFileMove()

OldFilePath = "C:\Import Files\New\" & TXTFileName
NewFilePath = C"\Import Files\Complete\" & TXTFileName
Name OldFilePath As NewFilePath

End Function

If the file already exists in that location, the macro halts with the
error:

Run-time error '58': File already exists [End] [Debug]

I've tried enclosing the "path" commands with
Application.DisplayAlerts = False, but it didn't work.

 

[Hendy88]

Thanks guys!! It worked. "KILL"ing the destination file previous to
moving it did the trick. I appreciate all your help. Cheers!

 

[Marco Pagliero]

Or you could simply use
KILL NewFilePath
since you've already set that up in your code. Got in too big of a rush to
copy. So

Function TXTFileMove()

OldFilePath = "C:\Import Files\New\" & TXTFileName
NewFilePath = C"\Import Files\Complete\" & TXTFileName
On Error Resume Next
KILL NewFilePath
If ERR<>0 Then
ERR.CLEAR
End If
On Error Goto 0
Name OldFilePath As NewFilePath

End Function

KILL doesn't generate an error if the file doesen't exist, KILL
generates an error only if the file is open. So maybe "on error resume
next" and the rest are not necessary after all.

Greetings
Marco P

 

[Dave Peterson]

It causes an error (53 path not found) for me.

KILL doesn't generate an error if the file doesen't exist, KILL
generates an error only if the file is open. So maybe "on error resume
next" and the rest are not necessary after all.

Greetings
Marco P