Calling Excel File Save As Dialog

According to articles from Google, I have the following code to call
the "Save As" dialog from Excel.

Everything is working fine with my Excel object. I have copied a
recordset to a spreadsheet and now want to save it.

However, this line generates the Run-time error 1004:
Application-defined or object-defined error.

xlApp.Dialogs(xlDialogSaveAs).Show

Is something wrong with my code?

Here is the code that sets up my Excel objects:
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

I'm not sure of the difference between setting up my objects this way
verus using the Excel Object library. Could someone also explain that
difference to me and why there are two methods?

Thank you.

try it this way

xlApp.Dialogs(5).Show


--
Regards,
Tom Ogilvy


"Bookreader" wrote:

> According to articles from Google, I have the following code to call
> the "Save As" dialog from Excel.
>
> Everything is working fine with my Excel object. I have copied a
> recordset to a spreadsheet and now want to save it.
>
> However, this line generates the Run-time error 1004:
> Application-defined or object-defined error.
>
> xlApp.Dialogs(xlDialogSaveAs).Show
>
> Is something wrong with my code?
>
> Here is the code that sets up my Excel objects:
> Dim xlApp As Object
> Dim xlBook As Object
> Dim xlSheet As Object
>
> Set xlApp = CreateObject("Excel.Application")
> Set xlBook = xlApp.Workbooks.Add
> Set xlSheet = xlBook.Worksheets(1)
>
> I'm not sure of the difference between setting up my objects this way
> verus using the Excel Object library. Could someone also explain that
> difference to me and why there are two methods?
>
> Thank you.
>

I think your issue is due to using late binding for xlApp. The program can't
make sense of the constant xldialogsaveas because there is no reference set
up to Excel. I had a similar issue w/Word - which was resolved and explained
by Peo Sjoblom (for which I am still grateful). As Tom posted, you must use
the numerical value of that constant. Or, set up a reference to Excel under
Tools/References and use early binding.



"Bookreader" wrote:

> According to articles from Google, I have the following code to call
> the "Save As" dialog from Excel.
>
> Everything is working fine with my Excel object. I have copied a
> recordset to a spreadsheet and now want to save it.
>
> However, this line generates the Run-time error 1004:
> Application-defined or object-defined error.
>
> xlApp.Dialogs(xlDialogSaveAs).Show
>
> Is something wrong with my code?
>
> Here is the code that sets up my Excel objects:
> Dim xlApp As Object
> Dim xlBook As Object
> Dim xlSheet As Object
>
> Set xlApp = CreateObject("Excel.Application")
> Set xlBook = xlApp.Workbooks.Add
> Set xlSheet = xlBook.Worksheets(1)
>
> I'm not sure of the difference between setting up my objects this way
> verus using the Excel Object library. Could someone also explain that
> difference to me and why there are two methods?
>
> Thank you.
>

On Thu, 13 Jul 2006 17:41:01 -0700, JMB
<JMB@discussions.microsoft.com> wrote:

>I think your issue is due to using late binding for xlApp. The program can't
>make sense of the constant xldialogsaveas because there is no reference set
>up to Excel. I had a similar issue w/Word - which was resolved and explained
>by Peo Sjoblom (for which I am still grateful). As Tom posted, you must use
>the numerical value of that constant. Or, set up a reference to Excel under
>Tools/References and use early binding.
>
>
>
Thanks.