macro save as delete original file

[Guest]

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

 

[Ron de Bruin]

Why not use Name

http://www.rondebruin.nl/folder.htm

 

[Guest]

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