Automation XL2Word (Win XP / O2003 SP3 UK)

Topic

New Reply

Hi,

Got a special here. Trying to create an ‘instant insert’ button within Excel. Intention is that it inserts whatever is selected into the activedocument in Word at the point where the cursor is positioned – regardless.

In Excel I’ve come this far – however the final ‘paste’ will not work… (I think ‘Selection’ is the problem, but am not sure as my object is late bound)

Public Sub TEST()
Dim oWd As Object
Dim oDoc As Object

On Error Resume Next

Set oWd = GetObject(Class:=”Word.application”)

If Err.number = 429 Then
Set oWd = CreateObject(Class:=”Word.application”)
Err.Clear
ElseIf Err.number 0 Then
MsgBox Err.number & “; ” & Err.Description
End If

Set oDoc = ActiveDocument
Debug.Print oDoc.Name

‘….some sort of selection – paste’

oWd.Visible = True

Set oDoc = Nothing
Set oWd = Nothing

End Sub

Good suggestions are more than welcome / LoL

Reply | Quote

Replies

Try

oWd.Selection.Paste

Reply | Quote

OK – thanks (no good reason why I hadn’t try that

Here comes the curl…

I already has a ‘master template’ installed and loaded as a global add-in.

In that one there’s two large subs – one handling chart insertion another handling table insertion (let’s call them ‘Table_insert’ and ‘Chart_insert’)
Is it possible from within Excel – instead of using oWD.Selection.Paste

To call the appropiate macro and then again having it inserted at the selected place???
macro would then look sth. like:

Public Sub TEST()
Dim oWd As Object
Dim oDoc As Object

‘code to decide on type – probably sth. like
If Typename(Selection) = chart then
‘set chart flag
elseif Typename(Selection) = Table then
‘set table flag
end if
‘………………………………………….

On Error Resume Next

Set oWd = GetObject(Class:=”Word.application”)

If Err.number = 429 Then
Set oWd = CreateObject(Class:=”Word.application”)
Err.Clear
ElseIf Err.number 0 Then
MsgBox Err.number & “; ” & Err.Description
End If

Set oDoc = ActiveDocument
Debug.Print oDoc.Name

‘ then call one of the two macros in ‘Master_Template’ based on flag….
‘ and insert the ‘item’ in the activedocument at ‘selection – paste’

oWd.Visible = True

Set oDoc = Nothing
Set oWd = Nothing

End Sub

Reply | Quote

I forgot to mention that

Set oDoc = ActiveDocument

is very dangerous – it may create an orphan instance of Word. When using Automation, you must always refer objects back to the application object, directly or indirectly. Here:

Set oDoc = oWd.ActiveDocument

You can call a macro in a global template as follows:

oWd.Run “NameOfMacro”

Reply | Quote

Hi Hans,

Thanks – as usual; it worked like a charm (almost getting trivial, isn’t it )

Reply | Quote