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

H

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.
 
G

Guest

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
 
G

Guest

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
 
R

Ron de Bruin

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



--

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


JLatham said:
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.
Click to expand...
Click to expand...
 
H

Hendy88

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

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
Click to expand...
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
 
D

Dave Peterson

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

Marco said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error 58, file already exists - why? 7
Automated multiple text files into multiple sheets in one workbook 14
Change Code in Macro to specify file/folder to look in. 1
Error - file already exists. 5
Workbooks.OpenText method help 3
move files and prompt user if file already exists in target filepath 8
Error importing key - object already exists. 2
Test if folder exists prior to creating it - VBA 5

Top