Excel Export to OLE Object with user prompted SaveAs HELP

First of all, I am a novice here with Excel and have learned much with the resources here. I have stuck on a VBA script I have been working on that takes excel data and inputs them into an word document template that is embedded into the workbook. It utilizes bookmarks in the document template to reference locations where the data should be input.

I started very simple with this code that simply uses word to open the embedded document and SaveAs with user prompted save location:

Formula:

Please Login or Register  to view this content.

This code WAS working and would open the OLE in Word and prompt the user where to SaveAs and file name. Now for whatever reason it errors on [.Name = "c:\SaveExample.doc"] line saying the object doesn't support the property or method.



My full code is:

Formula:

Please Login or Register  to view this content.

What it is supposed to do is 1) Open the embedded .dotx OLE object and 2) using Word.Bookmarks insert data from cells on my spreadsheet into this new word document that it has created and 3) provide the user with a Save As dialog prompt so they can specify save locaiton and file name.

I am sure my coding is way off and please forgive my beginnerness but I am in need of the expert's help on this one. If anyone could help me correct my code I would be greatly indebted! Thank you.

Welcome to the forum!

When you Compile it, as I often recommend, you can see the problem is your Word constant wdDialogFileSaveAs. Set that variable = 84 if you are only going to use early binding to the Word object.

Tip: In MSWord's VBE's Immediate Window, type, ?wdDialogFileSaveAs and press enter key to get the value.

Ok, I have a crude but usable solution which involves copying my embedded Dotx to another sheet and using that to work the data then deleting it when done, thus preserving the original Dotx.

It works, however, if you try to run the macro a second time it errors with "Error 462 The remote server machine does not exist or is unavailble" on line With Word.Application.Dialogs(84)

If I click my macro again it works, so effectively it runs sucessfully every other time you run it. Anyone with any insight on why that is?

Formula:

Please Login or Register  to view this content.

Have you tried making the embeded object a template?

I don't have time to test this right now but try this. SaveAs first after you open the oleobject. Then set the object to nothing. Make your changes to the Word file and then Save. You may have to set the Word object to Nothing and then reset the reference to the Word object based on the filename saved with a GetObject("c:\path\whatever.docx"). I can test these late tonight if needed.

Bump

Anyone have insight as to this error occuring every other time the macro is ran?