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: copy to clipboard 
Private Sub SaveAs_Click()

    Dim oleObject As Object

    Dim wordDocument As Object

     

    Set oleObject = ActiveWorkbook.Sheets("Summary").OLEObjects(1)

    oleObject.Verb Verb:=xlPrimary

    ActiveSheet.Range("A1").Select

     

    Set wordDocument = oleObject.Object

     

    With Application.Dialogs(wdDialogFileSaveAs)

    .Name = "c:\Report.doc"

    .Format = wdFormatDocument

    .Show

End With

     

End Sub


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: copy to clipboard 
Private Sub ExportSaveAs()

    Dim oleObject As Object

    Dim wordDocument As Object

    

    Dim MyDataCellA As Excel.Range

         

    Set oleObject = ActiveWorkbook.Sheets("Summary").OLEObjects(1)

    oleObject.Verb Verb:=xlPrimary

    ActiveSheet.Range("A1").Select

     

    Set wordDocument = oleObject.Object

     

    Set MyDataCellA = Sheets("Summary").Range("F7")

    

    With wordDocument.Bookmarks

        .Item("bmFiscalYear").Range.InsertAfter MyDataCellA

End With



    With Application.Dialogs(wdDialogFileSaveAs)

    .Name = "C:\Report.doc"

    .Format = wdFormatDocument

    .Show

End With

     

End Sub


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.