Calling Excel File Save As Dialog

[Bookreader]

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.

 

[Tom Ogilvy]

try it this way

xlApp.Dialogs(5).Show

 

[JMB]

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]

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.