Reputation: 169
I am having issues getting my embedded document to run on older version of MS Office. I believe the issue lies in how I am declaring my objects, but am unsure how to declare my embedded documents using late binding (I'm still very new to both this concept and vba). Below is how I'm currently doing my variable declaration along with a snippet of code. It works great in Excel 2013, but is not working in 2010 because of how I declared my variables (I believe).
Dim oDoc As Word.Document
Dim oTemplate As Word.Document
Dim wdObj As OLEObject
Dim wdObj2 As OLEObject
'Deselect anything selected
Range("A1").Select
'Copy content of template
Set wdObj = Sheets("Template").OLEObjects("Template")
wdObj.Activate
Set oTemplate = wdObj.Object
oTemplate.Content.Copy
'Initialize letter document
Set wdObj2 = Sheets("Report").OLEObjects("Letter")
wdObj2.Activate
Set oDoc = wdObj2.Object
'Erase contents and replace with clean template
oDoc.Content.Delete
oDoc.Range.PasteAndFormat _
wdFormatOriginalFormatting
I've attempted setting oDoc and oTemplate them "As Object" but am not sure how to link them back to a word object. Doing this simply caused the library reference to fail on the PasteAndFormat call's "wdFormatOriginalFormatting".
I've been tearing my hair out trying to figure this out. Any help would be greatly appreciated. Thanks!
Upvotes: 0
Views: 1163
Reputation: 8941
With Late Binding you cannot use any of the predefined constants of Early Binding as at the time of Dim
it is not know WHICH object you mean (that's the advantage of Early Binding) ... on the other hand you don't need to create references to libraries.
Now for linking an OLE object (Word) back to a Document object ...
With embedded objects you go the other way round ... normally you would first create/open the application, then the doc within the application ... here we make use of the OLE verb to immediately open the embedded doc, then catch the application ...
Sub LateBinding()
Dim WApp As Object
Dim wdobj As Object
Dim wdOLE As OLEObject
' create a reference to the OLE object
Set wdOLE = ActiveSheet.OLEObjects(1)
' open the OLE object using its application (whatever that is)
wdOLE.Verb xlVerbPrimary
' create a reference to the real object inside the OLE object
Set wdobj = wdOLE.Object
' create a reference to the application used by wdobj
Set WApp = wdobj.Application
' start using the application ... let's move the cursor a bit
' hoping it is REALLY a WORD type of object ... we could test that before of course
' note we cannot use predefined WORD constants here, hence Unit and Count
WApp.Selection.MoveDown Unit:=5, Count:=1
End Sub
now the OLE object is linked (back) to a real document object and (back to) its application.
Hope this is what you wanted to know ...
Upvotes: 1
Reputation: 53663
First, ensure you're using Option Explicit
in the code module. THen, when you attempt to compile, it should alert you to a problem with wdFormatOriginalFormatting
.
Why?
wdFormatOriginalFormatting
is an enumerated constant within the Word object model. It doesn't exist in Excel, unless you use early binding.
Solution
When you use late-binding, you need to declare and assign values to these constants, since they are not otherwise known at compile time or run-time:
Const wdFormatOriginalFormatting as Long = 16
Then, this line should not cause the error:
oDoc.Range.PasteAndFormat _
wdFormatOriginalFormatting
Upvotes: 3