Embedded Excel object in Word Document 1

[tjlee35]

I am having trouble finding a solution to edit an excel object embedded in a word document. I attempted to use a solution from thread707-1049774, but I am getting an error.

Basically, I have an estimate template in word that I use to create a new document. I then need to open the existing excel object and edit these cells. I have been able to find a work around by creating an excel spreadsheet, adding the standard calculation cells, copying those cells, and pasting them as a new excel object in the word document, but I would like to simplify this by just editing an existing excel object within the existing word template.

Using thread707-1049774, the following code run from my Access module produces the error "Run time error '91': Object Variable or With Block Variable Not Set" on the line "XLWb.OLEFormat.Open".

I'm not sure if I'm missing a reference or what, so any suggestions could help.

'MY CODE:
Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Dim ExpPath As String

cmdHourglassOn

' Open Microsoft Word using automation
Set WDApp = New Word.Application
WDApp.Documents.Add "C:\OutdoorBuildersApps\Contract Template2.dot"
WDApp.Visible = False
Set WDDoc = WDApp.ActiveDocument


If WDDoc.Bookmarks.Exists("Name") = True Then
strName = Form_EstimatePricingWizard!NameC & ", " & Form_EstimatePricingWizard!FirstName
If Nz(Form_EstimatePricingWizard!Spouse, "") <> "" Then strName = _
strName & " and " & Form_EstimatePricingWizard!Spouse
WDDoc.Bookmarks("Name").Range.Text = strName
End If

If WDDoc.Bookmarks.Exists("Address") = True Then
WDDoc.Bookmarks("Address").Range.Text = Form_EstimatePricingWizard!Address
End If

If WDDoc.Bookmarks.Exists("City") = True Then
WDDoc.Bookmarks("City").Range.Text = Form_EstimatePricingWizard!City
End If

If WDDoc.Bookmarks.Exists("Phone") = True Then
WDDoc.Bookmarks("Phone").Range.Text = Form_EstimatePricingWizard!Phone
End If

If WDDoc.Bookmarks.Exists("Zip") = True Then
WDDoc.Bookmarks("Zip").Range.Text = Form_EstimatePricingWizard!Zip
End If



Dim XLWb As Word.Shape
Dim XLRange As Range

Set XLWb = owdSource.Shapes(1)

' This line is where I get the error:
XLWb.OLEFormat.Open

Set XLRange = XLWb.OLEFormat.Object.Worksheets(1).Range("A1")
XLRange = "TEST"


Thanks for the help!

Trevor

 

[rmikesmith]

Trevor,

I don't see where you declare owdSource, but I don't think that is problem.

When I run the macro recorder while inserting an Excel spreadsheet into Word, it shows this as an InlineShape not a Shape object. So, if I use code like this:

Dim XLWB As Word.InlineShape

   Set XLWB = ActiveDocument.InlineShapes(1)

   XLWB.OLEFormat.Open

the embededded XL object is activated. So, you might want to try that.

Regards,
Mike

 

[tjlee35]

Thank you so much! I was pulling my hair out and knew it was something simple. I had tried that from the other thread and had gotten an error and assumed it was wrong, but it was just my syntax that was wrong. Thanks!