VBA works in 2007 but not 2010

[brenton]

this code always worked in 07 but now is crashing in 2010. It should take a range in excel and paste as an image in the corresponding bookmark in Word? It seems like it is running out of memory because it copies everything to the clipboard. Ive tried changing Data Type=:0 and DoEvents any ideas? Thanks!


Sub GenerateBusinessCase()

'
Application.ScreenUpdating = False

Dim pappWord As Object
Dim docWord As Object
Dim wb As Excel.Workbook
Dim xlName As Excel.Name
Dim Path As String
'Dim rangeData As Range

Const wdGoToAbsolute As Integer = 1
Const wdGoToLine As Integer = 3



Set wb = ActiveWorkbook
Path = wb.Path & Application.PathSeparator & "GE Lighting Business Case Template.docx"
On Error GoTo ErrorHandler

'Create a new Word Session
Set pappWord = CreateObject("Word.Application")
On Error GoTo ErrorHandler

'Open document in word
Set docWord = pappWord.Documents.Add(Path)

'Loop through names in the activeworkbook
For Each xlName In wb.Names
'if xlName's name is existing in document then put the value in place of the bookmark
If docWord.Bookmarks.Exists(xlName.Name) Then
docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
End If
Next xlName

'Run the table copy code. I am copying/pasting my Excel tables with this code.

docWord.Bookmarks("FirstPillar").Range.Select
Range("FirstPillar1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3

docWord.Bookmarks("ThirdPillar").Range.Select
Range("ThirdPillar1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3

docWord.Bookmarks("FourthPillar").Range.Select
Range("FourthPillar1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3

docWord.Bookmarks("SecondPillar").Range.Select
Range("SecondPillar1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3

docWord.Bookmarks("WaterfallControls").Range.Select
Range("WaterfallControls1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3

docWord.Bookmarks("ModelAdjustment").Range.Select
Range("ModelAdjustment1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3

docWord.Bookmarks("FinancialMetrics").Range.Select
Range("FinancialMetrics1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3

docWord.Bookmarks("FinancialResults").Range.Select
Range("FinancialResults1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3

docWord.Bookmarks("SolutionInvestment").Range.Select
Range("SolutionInvestment1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3


'Turn off copy mode
Application.CutCopyMode = False

'Activate word document
With pappWord
.Visible = True
.ActiveWindow.WindowState = 1
.Activate
' Optional - Display document on page #1
.Selection.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=1
End With

'Release the Word object to save memory and exit macro
ErrorExit:
Set pappWord = Nothing
Exit Sub

'Error Handling routine
ErrorHandler:
If Err Then
MsgBox "Error No: " & Err.Number & ";" & vbNewLine & " o Verify calculations on the financial results page do not return errors".
If Not pappWord Is Nothing Then
pappWord.Quit False
End If
Resume ErrorExit
End If
Application.ScreenUpdating = True

End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

What do you mean by crashes exactly?

 

[brenton]

I get "error 4605" when running it in Excel 2010, but no problems in 2007. On some computers with 2010 the error won't show up and the macro will copy and paste some of the ranges, but they won't be accurately placed into the bookmarks. They end up at the top of the page. It has to be a difference with the object code i think.
I saw on some forum that in office 2010 you have to change the DataType: to "0" It always says there is a lot of information on the clipboard, so i wonder if there is a more efficient way to copy and paste?

docWord.Bookmarks("FourthPillar").Range.Select
Range("FourthPillar1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3

 

[Andrew Poulsom]

What's the text of the error?

 

[brenton]

I don't know if this is customary to do, but it seems very similar if not the same problem as here: http://www.tek-tips.com/viewthread.cfm?qid=1637602&page=5
same errors. same problem. i tried to put "Doevents" and " Application.CutCopyMode = False" in like they discussed, but that didnt really help, maybe it just took longer to error.

 

[Andrew Poulsom]

Have you got any applications or add-ons running that might be using the clipboard?

 

[brenton]

yeah you might be onto something! there may be some addons running, how would i disable that in the code? or do i have to disable addons every time?

 

[brenton]

I modified the code to

docWord.Bookmarks("FirstPillar").Range.Select
Range("FirstPillar1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=0

now i get runtime error'-2147220995
automation error:"Object is not connected to the server

I ran it again and i recieved no error and it just hung there.

 

[brenton]

is there any other code i can use to paste an image from excel to Word instead of this?

docWord.Bookmarks("FirstPillar").Range.Select
Range("FirstPillar1").Copy
pappWord.Selection.PasteSpecial Link:=False, DataType:=3

 

[Andrew Poulsom]

I'm no Word expert but can't you just use Paste rather than pasting a link? Use the macro recorder in Word to get the syntax.