Inserting Excel range into Word bookmark of existing document

gyz

New Member
Joined
Nov 11, 2010
Messages
6
Hello, I have a MS word template populated with various bookmarks. At one bookmark, I want to use the insertfile method to insert a table (or rather, a range) from a worksheet of an existing workbook.
the code I have here gives a runtime error 9 - subscript out of range.
VBA Code:
Set oWord = CreateObject(class:="word.application")
oWord.Visible = True
oWord.Activate
Set oDoc = oWord.documents.Open("C:\Examtemp.dotx")

with oDoc
    .bookmarks("Example").range.text = "some text here"
    .bookmarks("Table").Range.Content.InsertFile ThisWorkbook.Path & "\temp.xlsx", Sheets("Results Table").Range("A1:E" & LastRow) '**This line gives runtime error 9'
    .bookmarks("Table").Range.Content.InsertFile ThisWorkbook.Path & "\temp.xlsx", "Sheet3!A1:E" & LastRow '**This line gives runtime error 438 - object doesn't support this property or method
    [more bookmark insertions]
End with
The paths, names and ranges are valid and correct, which makes me think I'm using the insertfile command incorrectly. I've tried looking for examples of what I want to do but most appear to involve inserting a file into a new blank document. Could anyone help with whether I am using the command correctly?
In anticipation of the question "why not just use copy and paste at the bookmark", the problem with that is that I have very particular number formatting in the cells which is lost when using this method (believe me, i've tried everything to retain it and nothing works). Thanks in anticipation.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top