Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to move files gets an error if it already exists...
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to move files gets an error if it already exists...
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 On Error Goto 0 Name OldFilePath As NewFilePath " wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to move files gets an error if it already exists...
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 " wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to move files gets an error if it already exists...
Or use FSO
http://www.rondebruin.nl/folder.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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 On Error Goto 0 Name OldFilePath As NewFilePath " wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to move files gets an error if it already exists...
Thanks guys!! It worked. "KILL"ing the destination file previous to
moving it did the trick. I appreciate all your help. Cheers! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to move files gets an error if it already exists...
On 5 Mrz., 20:00, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to move files gets an error if it already exists...
It causes an error (53 path not found) for me.
Marco Pagliero wrote: On 5 Mrz., 20:00, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error with linked files....Macro Problem??? | Excel Worksheet Functions | |||
Move data Macro | Excel Discussion (Misc queries) | |||
remove error value when formula exists for empty cells | Excel Worksheet Functions | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
How do I move files that are linked with template wizard? | Excel Discussion (Misc queries) |