Use of xlDialogSaveCopyAs

Posted by Doug Phil on July 06, 2001 9:29 PM

I would like to use xlDialogSaveCopyAs to finish off a macro but I haven't found any examples of all the code required to make this work. Can I get an example from someone?

Posted by Tom Urtis on July 06, 2001 11:25 PM

Hi Doug:

I'm not sure by reading your post whether you just want to display the Save As dialogue box for your user to choose what name and file path to
save the original file as, or if you want to take all the guesswork out of the deal and do all the thinking for the user. So, here are examples for both possibilities:

To show the Save As dialogue box for your user to decide, place this line in your code:

Application.Dialogs(xlDialogSaveAs).Show

Alternatively, here is code that will save a daily sales report template as "today's date Report" in your C drive's Clients folder, client name subfolder (in my example your client is called the XYZ Corporation), DailySales sub sub folder.

ActiveWorkbook.SaveAs ("C:\Clients\XYZ Corp\DailySales\" & Format(Now(), "mm""-""dd""-""yyyy") & " Report.xls")

Hope this helps.

Tom Urtis

Posted by Doug Phil on July 07, 2001 10:14 AM

Both examples are really helpful especially the second one so thanks much. What I would really like to do is start the macro with a form that requests a unique filename for the sheet that the marco will eventually save and then use that as the filename in the SaveAs dialog in the current dirctory. Any additional help would be appreciated