Save copy of workbook using Shell

[YasserKhalil]

Hello everyone

I know that there is a method SaveCopyAs method in excel that creates a copy of the workbook to a specific path.
Is there an approach using Shell to do such a task?

[HansV]

Yes:

Code: Select all

Shell "cmd.exe /c copy ""C:\Folder1\ThisWorkbook.xlsm"" ""C:\Folder2\ThatWorkbook.xlsm""", vbHide

[YasserKhalil]

Great .. Replacing the quotes is a nightmare in such lines. How can I replace the paths with variables so as to make this line more flexible?

[HansV]

Those double double quotes are actually only needed if the path+filename contains spaces, so in the example in my previous reply, you might omit them. But in general, it's safer to keep them.

Code: Select all

    Dim strSource As String
    Dim strTarget As String
    strSource = "C:\Folder1\ThisWorkbook.xlsm"
    strTarget = "C:\Folder2\ThatWorkbook.xlsm"
    Shell "cmd.exe /c copy " & Chr(34) & strSource & Chr(34) & " " & Chr(34) & strTarget & Chr(34), vbHide

[YasserKhalil]

Thanks a lot for these treasures my tutor.