Is there a way to save a copy of a workbook in VBA?

[TomCon]

If you do a "Save As" the workbook you are working in is not left open, instead what is left open is the new workbook with the new name. What I'd like is for the original workbook to remain open, but that a copy of it is tucked off somewhere. In fact i don't really want that copy open at all, just saved under that name.

Is there a way to do that?

Also wondering if you are in the middle of executing VBA and do a "Save As" does the code continue executing at the same place in the new-named workbook? Or is there any discontinuity/interruption?

Thanks!

 

[mse330]

TomCon, try SaveCopyAs

ActiveWorkbook.SaveCopyAs "complete path & file name"

 

[TomCon]

Thanks much! I didn't know about SaveCopyAs, so thats what i need!

I do have a follow-up question. Do you know how to specify the path to save a file to OneDrive?

When i recorded a macro, this was recorded:
ActiveWorkbook.SaveAs Filename:= _
"https://d.docs.live.net/da6e7d8e7901482d/D4_p_TRK.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Of course i did not specify the whole "d.docs.live..." thing. Using the UI, I just selected OneDrive and then my file name, "D4_p_TRK.xlsm" and that was what was recorded by the macro recorder.

But, when i tried this in a macro:
ActiveWorkbook.SaveCopyAs "https://d.docs.live.net/da6e7d8e7901482d/D4_p_TRK.xlsm"

I got the following error:
Error 1004
Sorry, we couldn't find "https://d.docs.live...". Is it possible it was moved, renamed, or deleted.

The error message seems mysterious as this is a save operation, not an open operation.

So...can somebody tell me what to put in the "" with SaveCopyAs to save a copy of the file to OneDrive?

Thanks!

 

[mse330]

You are welcome ... I have never tried to save some a document directly to google drive through VBA. But if you do record the same macro couple more times, would it still give the same link ? Have you tried to google this ?