Excel to old version of Word

[CaliforniaDreamin]

New to VBA programming here and having a little trouble when trying to run a macro in Excel that pastes cells into Word on XP. It works fine in 07 and 10 versions of Office, but when it's run in XP there's an error because the Office 10 Word Object Library is not available on XP. Here's my code, thanks in advance for any help.

Sub CopyPaste()

Dim WordApp As Word.Application
Dim WordDoc As Word.Document

Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True

Set WordDoc = WordApp.Documents.Add

' Select range you want to export
Range("B12:C40").Copy

WordApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
Placement:=wdInLine, DisplayAsIcon:=False

' Select all, format paragraph
WordApp.Selection.WholeStory
WordApp.Selection.ParagraphFormat.Space1

' Arial10
WordApp.Selection.WholeStory
WordApp.Selection.Font.Name = "Arial"
WordApp.Selection.Font.Size = 10


Set WordDoc = Nothing
Set WordApp = Nothing

End Sub

 

[RoryA]

You will need to late bind - declare wordapp and worddoc as Object and use literal values for Word constants like wdPasteText

 

[CaliforniaDreamin]

Thanks Rory. I found a formula that uses late binding and still works for Office 10, but I'm getting an error in XP related to the wdPasteText part of the code. Could you elaborate on the "use literal values" part of your earlier response?


Sub GetWord()
'Bind to an existing or created instance of Microsoft Word
Dim objApp As Object

'Attempt to bind to an open instance
On Error Resume Next
Set objApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'Could not get instance, so create a new one
Err.Clear
On Error GoTo ErrHandler
Set objApp = CreateObject("Word.Application")
With objApp
.Visible = True
.Documents.Add
End With
Else
'Bound to instance, activate error handling
On Error GoTo ErrHandler
End If

' Copy Range you want in Excel
Range("B12:C53").Copy

' Paste to Word
objApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
Placement:=wdInLine, DisplayAsIcon:=False

' Select all, format paragraph
objApp.Selection.WholeStory
objApp.Selection.ParagraphFormat.Space1
objApp.Selection.ParagraphFormat.SpaceBeforeAuto = False
objApp.Selection.ParagraphFormat.SpaceAfter = 0
objApp.Selection.ParagraphFormat.SpaceAfterAuto = False
objApp.Selection.ParagraphFormat.LineUnitAfter = 0

' Arial10
objApp.Selection.WholeStory
objApp.Selection.Font.Name = "Arial"
objApp.Selection.Font.Size = 10

ErrHandler:
'Release the object and resume normal error handling
Set objApp = Nothing
On Error GoTo 0
End Sub

 

[RoryA]

wdPasteText is a constant (value 2) that is declared in the Word object library. If you don't have a reference set (i.e. you late bind) then Excel has no idea what that is. Add these two lines:

Const wdPasteText as Long = 2
Const wdInLine As Long = 0

and you should be fine.