Using Excel from Word Macro

[Mavila]

I'm trying to use the built-in "Save As" dialog in Excel using a Word macro. I'm able to create the Excel object (I'm calling it exApp) and work with the worksheet fine, but I am having trouble with the syntax of this built-in dialog box. I'm using the following code:

With exApp.Dialogs(xlDialogFileSaveAs)
exApp.Name = strPath
exApp.Show
End With

I get a run-time error 1004 (application-defined or object-defined error) when the code tries to read the "With" line of code.

Anyone have any experience with this?

[gmaxey]

I'm not that familiar with Excel, but could it be that there is no such dialog in the Excel object?
Perhaps:
exApp.Dialogs(xlDialogSaveWorkbook)

[Frosty]

the excel enumerator "xlDialogFileSaveAs" probably isn't readily recognized within Word VBA. Since you've only shown a very small snippet of code, I don't know if it will be enough to say Excel.xlDialogFileSaveAs or perhaps exApp.xlDialogFileSaveAs)

But my bigger question would be: why do you want to do this? Seems wonky to call a built-in excel save as dialog from within Word... since you can't be sure what the ActiveWorkbook object is (since you could have multiple workbooks open), and thus you can't be sure what workbook you'd actually be Saving As...

[Mavila]

Thanks for the replies.

First of all, gmaxey, you are correct. The correct object is xlDialogSaveAs. But that doesn't work either.

Frosty, I'm just trying to get the path location where the user would like to save a completed form (there is a Word and Excel version of the form). With the Word form, I store the path in a text box and then confirm that it exists before saving the file. It's using a "Browse" button to ID and verify the path.

[Mavila]

The answer is that you must use the numerical value for the dialog box. So, the correct code is:

exApp.Dialogs(5).show

Thanks for your time!

[Frosty]

That's one right answer;)

You could also add a reference to the excel object library and then have the enumerator "xlDialogSaveAs" properly return the value of "5" web you fully reference it from word VBA via Excel.xlDialogsSaveAs or perhaps Excel.Application.xlDialogSAveAs.

Although there are probably multiple ways to do it.

Glad it's solved for you!