Word macros - advanced

I have an 85-page word document with hundreds of bookmarks. I need to be able to pull data from Excel fields and tables into these bookmarks. The tables need to come in as pictures, and the fields are named single-cell ranges whichi correspond to the bookmark names. I have a somewhat-working Excel macro to push these bookmarks in, but nothing to get the tables into Word as a picture.

 

Help...?

 

P.S. I'm an Excel MVP

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Is mailmerge an option?

Check out Graham Mayor's pages:
http://www.gmayor.com/Word_pages.htm
http://www.gmayor.com/ManyToOne.htm

And Doug Robbins' Add-Ins especially the one for merging charts
Merge Tools - SkyDrive

Volunteering to "pay forward" the help I've received in the Microsoft user community.


Charles Kenyon
Sun Prairie, Wisconsin
wordfaq[at]addbalance[dot]com

Legal site: https://addbalance.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

You will need to copy the tables to the clipboard and then use

 

.PasteSpecial DataType: =

 

with the .Range of the bookmark and with the appropriate DataType, which can be one of the following:

 

WdPasteDataType Enumeration

Specifies the format for the Clipboard contents when they are inserted into a document.

Name Value Description
wdPasteBitmap 4 Bitmap.
wdPasteDeviceIndependentBitmap 5 Device-independent bitmap.
wdPasteEnhancedMetafile 9 Enhanced metafile.
wdPasteHTML 10 HTML.
wdPasteHyperlink 7 Hyperlink.
wdPasteMetafilePicture 3 Metafile picture.
wdPasteOLEObject 0 OLE object.
wdPasteRTF 1 Rich Text Format (RTF).
wdPasteShape 8 Shape.
wdPasteText 2 Text.

Remarks

Used with the PasteSpecial method of the Range or Selection object.

 

Hope this helps,
Doug Robbins - MVP Office Apps & Services (Word)
dougrobbinsmvp@gmail.com
It's time to replace ‘Diversity, Equity & Inclusion’ with ‘Excellence, Opportunity & Civility’ - V Ramaswamy

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Mailmerge is not an option.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

thanks for your help...

But how do I replace the bookmark in Word with this picture? Suppose the bookmark name in word is "table11" and it's one of 20 tables strewn throughout the document. I have the table in the clipboard, but now what's the code to put it in the right place?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

ActiveDocument.Bookmarks("table11").Range.PasteSpecial DataType:=wdPasteRTF
_____________________________
https://jay-freedman.info

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Fellow MVPs have been wandering around the subject, but if you are pushing the tables from Excel as opposed to pulling them from Word, you would need something like


Dim wdApp As Object
Dim oDoc As Object
Dim oRng As Object
   
    Range("A25:E28").Copy 'The data range in Excel
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    Set oDoc = wdApp.Documents.Open("C:\Path\MyDocument.docx") 'You don't have to test for an open document in Word. If it is already open, nothing untoward happens
    Set oRng = oDoc.Bookmarks("table11").Range
    oRng.PasteSpecial DataType:=3 'You would have to use the type number when calling from Excel
    oDoc.Bookmarks.Add "table11", oRng 'Reset the bookmark

Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

OK, I tried that but there's a problem. The original word document has a picture as a placeholder for the Excel table (which is being copied as a picture). When I run the suggested code, 2 things happen which are not what I want. 1: The picture of the excel table is sitting ON TOP of the placeholder and some if it bleeds through -- I want it to REPLACE or delete the placeholder, but I think in doing so I'll lose the bookmark. The place holder doesn't NEED to be there in the word doc but currently is. So perhaps you can tell me if I should have the bookmark be just a location in the doc, not an object. 2: adding the bookmark back (resetting it) puts it at the beginning of the document, not where it originally was.

Thanks for pursuing this.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

If the picture is selected when you insert the bookmark, the picture will be replaced by contents of the clipboard when you use pastespecial.

 

The following illustration displays two bookmarks, indicated by [ ] which will be visible if you select the option for bookmarks to be displayed.

 

The smaller set of markers inside the table cell represent the bookmark that will be created if the cell is selected.

 

A screen capture of that table was then made using Snagit and inserted into a document as a picture and then while the picture was selected, a bookmark was inserted which results in the display of the elongated pair of markers.

 

With bookmarks of that type, the contents will be pasted inside the bookmark, replacing whatever is there.

 

I make use of that feature in the Merge with Charts utility in the MergeTools – 20130627 Add-in that I created that you can download from the following page of my Windows Live SkyDrive:

https://skydrive.live.com/?cid=5aedcb43615e886b#!/?cid=5AEDCB43615E886B!cid=5AEDCB43615E886B&id=5AEDCB43615E886B%21566

 

 

Hope this helps,
Doug Robbins - MVP Office Apps & Services (Word)
dougrobbinsmvp@gmail.com
It's time to replace ‘Diversity, Equity & Inclusion’ with ‘Excellence, Opportunity & Civility’ - V Ramaswamy

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

You only need a bookmark at the location where you wish to paste the image. If the placeholder is in the bookmark, the following variation should work for either option.

    Set oRng = oDoc.Bookmarks("table11").Range
    oRng.Delete
    oRng.PasteSpecial DataType:=3 
    oRng.ShapeRange(1).ConvertToInlineShape
    oDoc.Bookmarks.Add "table11", oRng    


Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

This is REAL close. Excellent, thanks! The test Word Doc I used looks like this:

aaaaaaaaaaaa

bbbbbbbbbb

and the position at the beginning of the blank line is the bookmark.

After I run my macro I see the Excel table as a picture, but the bbbbbbbbbb is not in its own line -- it's abutted to the picture, something like this:

aaaaaaaaaaaaa

MYTABLE

MYTABLE

MYTABLE

MYTABLE

MYTABLEbbbbbbbbbbbbb

and I want

aaaaaaaaaaaaa

MYTABLE

MYTABLE

MYTABLE

MYTABLE

MYTABLE

bbbbbbbbbbbbb

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated October 1, 2021 Views 217 Applies to: