xlDialogSaveAs question

[KarenH]

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.../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)

"KarenH" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[KarenH]

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" wrote:

> xlDialogSaveCopyAs cannot be shown interactively, it errors.
>
> The following article has more info:
>
> http://support.microsoft.com/support.../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)
>
> "KarenH" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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)

"KarenH" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > xlDialogSaveCopyAs cannot be shown interactively, it errors.
> >
> > The following article has more info:
> >
> > http://support.microsoft.com/support.../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)
> >
> > "KarenH" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> >
> >
> >