xlDialogSaveAs question

[Guest]

I have an Excel spreadsheet with a macro in it to save. Clicking on the
save brings up the "Save As" dialog, giving them the option to save it as
various file types, such as Excel, CSV or TXT.

I am using the following code to do this:

fName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
Application.Dialogs(xlDialogSaveAs).Show fName

It works fine, but should the user save it to something other than Excel --
say, a CSV format file -- then the active open workbook becomes what they
saved it to. In other words, they open MyFile.xls, save it as MyFile.csv,
and then the file they are working with is MyFile.csv, and I want them to
still be working with Myfile.xls after they have saved it.

I tried using "ApplicationDialogs(xlDialogSaveCopyAs).show fName, but that
threw an error.

Thanks in advance for any suggestions.

 

[Bob Phillips]

xlDialogSaveCopyAs cannot be shown interactively, it errors.

The following article has more info:

http://support.microsoft.com/support/kb/articles/q213/4/57.asp

Q213457 - XL2000: Dialog Constants That Don't Have Interactive Dialog Box

You could use Application.GetSaveAsFilename to obtain a file name from the
user, and pass that to the SaveCopyAs method directly.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Very helpful link, I didn't know that it wasn't interactive. Do you have any
suggestions, then, as to how to allow the user to save a copy in whatever
format they close through xldialogSaveAs, but without that one becoming the
active workbook? So that they have saved it in their chosen format, but
remain in the original XLS format?

 

[Bob Phillips]

Yes, as I said last time ...


You could use Application.GetSaveAsFilename to obtain a file name from the
user, and pass that to the SaveCopyAs method directly

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)