Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro save as delete original file
I would like to write a macro to save a file to a different folder location,
same filename, and then delete the file from the original location. I tried record macro and went to file-open to do the delete file part (right click on file and delete), but the file didn't delete. In visual basic, the command recorded was ChDir and the filename. Any help would be appreciated. Pam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro save as delete original file
Why not use Name
http://www.rondebruin.nl/folder.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pam M" wrote in message ... I would like to write a macro to save a file to a different folder location, same filename, and then delete the file from the original location. I tried record macro and went to file-open to do the delete file part (right click on file and delete), but the file didn't delete. In visual basic, the command recorded was ChDir and the filename. Any help would be appreciated. Pam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro save as delete original file
I tested the code below and it worked for me.
You should definitely test it out before you use it on live data. The relevant code for deleting the file is from VBScript (I googled that to find the code snippet). VBScript is compatible with VBA, and it allows one to operate on windows objects, like files and folders. Some VBScript Help is available in VBA Help, but not all. Sub DeleteFile() Dim MyFullName Dim fileSaveName MyFullName = ActiveWorkbook.FullName fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls), *.xls") If fileSaveName < False Then ActiveWorkbook.SaveAs fileSaveName Set objFSO = CreateObject("Scripting.FileSystemObject") objFSO.DeleteFile (MyFullName) End If End Sub Note that if you click Cancel on the Save As dialog box, nothing happens. It's only if you have given a Save As name that the file will be saved and the original deleted. You could add a warning that file MyFullName is about to be deleted, etc. That would take some extra coding and might wind up being a pain to respond on each time. Hope this helps. Keith "Pam M" wrote: I would like to write a macro to save a file to a different folder location, same filename, and then delete the file from the original location. I tried record macro and went to file-open to do the delete file part (right click on file and delete), but the file didn't delete. In visual basic, the command recorded was ChDir and the filename. Any help would be appreciated. Pam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Macro to Open File, Delete Contents, Save New File | Excel Discussion (Misc queries) | |||
rename current file save and delete original | Excel Programming | |||
auto save excel file every 10 minutes to its original file name | Excel Discussion (Misc queries) | |||
Auto save replaced my original file and now I need the original? | Excel Discussion (Misc queries) | |||
VBA to Save a file to a different format but keep original | Excel Programming |