 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2007Macro 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. 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 > Hello all, > [quoted text clipped - 17 lines] > I've tried enclosing the "path" commands with > Application.DisplayAlerts = False, but it didn't work. Or use FSO http://www.rondebruin.nl/folder.htm  SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > KILL the NewFilePath file before attempting to copy it with the Name command. > [quoted text clipped - 27 lines] >> I've tried enclosing the "path" commands with >> Application.DisplayAlerts = False, but it didn't work. Thanks guys!! It worked. "KILL"ing the destination file previous to moving it did the trick. I appreciate all your help. Cheers! 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 > Hello all, > [quoted text clipped - 17 lines] > I've tried enclosing the "path" commands with > Application.DisplayAlerts = False, but it didn't work. > Or you could simply use > KILL NewFilePath [quoted text clipped - 14 lines] > > 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 It causes an error (53 path not found) for me. > > Or you could simply use > > KILL NewFilePath [quoted text clipped - 21 lines] > Greetings > Marco P SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.