Inserting an Image and Text into Word Using Excel VBA

K

KMacattack

New Member
Joined
Nov 24, 2008
Messages
11
I am trying to paste a couple cell from excel to word as an image and then insert text underneath the image. The only way that I have been able to get text underneath the image is to use a bunch of line breaks. Is there a way to find the point after the image, it seems like the cursor stays at the top of the page when inserting the image though.


Rich (BB code): 
Sub CopyWorksheetsToWord<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
'This will copy the cell to word as a enhanced metafile.<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
Dim WdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet<o:p></o:p>
<o:p></o:p>
'Creates a New Microsoft Word Document<o:p></o:p>
Application.StatusBar = "Creating new document..."<o:p></o:p>
Set WdApp = New Word.Application<o:p></o:p>
Set wdDoc = WdApp.Documents.Add<o:p></o:p>
<o:p></o:p>
'Copies The ranges and will paste into the crated microsoft word document<o:p></o:p>
Set ws = ActiveSheet<o:p></o:p>
        Application.StatusBar = "Copying data from " & ws.Name & "..."<o:p></o:p>
        ws.Range("B4:H27").Copy<o:p></o:p>
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.PasteSpecial DataType:=wdPasteEnhancedMetafile<o:p></o:p>
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter<o:p></o:p>
    <o:p></o:p>
'Inserts Text into the Word Doc for the particular Points for discussion.<o:p></o:p>
    wdDoc.Content.InsertAfter  Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13)
    wdDoc.Content.InsertAfter  "My Text Here"
<o:p></o:p>
Set ws = Nothing<o:p></o:p>
Application.StatusBar = "Cleaning up..."
<o:p></o:p>
'Sets the Word Doc view to Print View and Zoom 85%<o:p></o:p>
WdApp.ActiveWindow.ActivePane.Zooms(wdPrintView).Percentage = 85<o:p></o:p>
WdApp.Visible = False<o:p></o:p>
        <o:p></o:p>
Set wdDoc = Nothing<o:p></o:p>
Set WdApp = Nothing<o:p></o:p>
Application.StatusBar = False
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
This seems to work Ok :-
Code: 
'=============================================================================
'- COPY/PASTE EXCEL RANGE AS PICTURE INTO WORD
'- Brian Baulsom November 2008
'=============================================================================
Sub PASTE_PICTURE()
    Dim WordApp As Object
    '------------------------------------------------------------------------
    '- COPY EXCEL RANGE
    ActiveSheet.Range("A1:C7").Copy
    '------------------------------------------------------------------------
    '- OPEN WORD
    Set WordApp = CreateObject("Word.Application")
     '-----------------------------------------------------------------------
    '- PASTE PICTURE
    With WordApp
        .Visible = True
        .Documents.Add
        .Selection.PasteSpecial Link:=False, DisplayAsIcon:=False, _
            DataType:=wdPasteMetafilePicture, Placement:=wdFloatOverText
        .Selection.TypeParagraph
        .Selection.TypeText "My text here"
        .ActiveWindow.ActivePane.Zooms(wdPrintView).Percentage = 85
    End With
    '-----------------------------------------------------------------------
    Set WordApp = Nothing
    Beep
End Sub
'=============================================================================
 
Upvote 0
Hi Brian,

Please pay your attention on a fact that for late binding method ( using of CreateObject() ) the named constants of the created application should be explicitly replaced to the numeric one.

i.e. DataType:=wdPasteMetafilePicture, Placement:=wdFloatOverText
should be replaced to DataType:=3, Placement:=1

Without Option Explicit Statement the such constants will be silently set by compiler to zero.
Therefore the unexpected result will be: DataType:=wdPasteOLEObject, Placement:=wdInLine because Word application constants are: wdPasteOLEObject=0 and wdInLine=0

Constant wdPrintView also should be replaced to 3.

Using reference to MS Word Object Library solves a problem, but with late binding it looks a little bit superfluously. At least the necessity of such reference should be underlined.

Regards,
Vladimir
 
Upvote 0
Hi ZVI

As the macro works perfectly well with the reference to the Word Library I see no point in your comments.

A quick check shows that the variables are correctly interpreted. I am no Word expert, and use of the Library gives me access to other facilities - such as the F1 help, error checking, and Object Browser - as well as using the mentioned Word constants rather than trying to remember numbers.
 
Upvote 0
Yes, with reference it works perfectly.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
By the way if reference is already used then instead of late binding:<o:p></o:p>
Rich (BB code): 
Dim WordApp As Object
Rich (BB code): 
Set WordApp = CreateObject("Word.Application")
the following early binding syntax can be used:
Rich (BB code): 
Dim WordApp As New Word.Application

But I would highlight only one disadvantage of working with reference to object library. It is in incompatibility with previous version of such libraries. <o:p></o:p>
<o:p></o:p>
For example, if workbook with your code is created in Excel 2002(XP) of MS Office10 with reference to MS Word 10.0 Object library, then it also will work in Excel 2003 and 2007. <o:p></o:p>
But it will not work in Excel 2000 and Excel 97. <o:p></o:p>
MISSING Reference to MS Word 10.0 Object library is appeared in this case.You can find it in VBE – Tools - Reference. Manual or by code resetting of missing reference is required.<o:p></o:p>
And if Option Explicit Statement is not present in a code then the code runs silently with replacing all word application constant to zero (refer to my previous post above).<o:p></o:p>
<o:p></o:p>

Furthermore, saving the mentioned workbook in Excel 2003 cause to auto resetting reference to MS Word 11.0 Object library. And after that it could not work even in Excel 2002 where it was burned and compiled.<o:p></o:p>
<o:p></o:p>

Main message of my previous post was in avoiding mixing of early and late binding methods. Instead, using of only late binding method gives compatibility with all MS version of Excel because in this case the installed version is created without direct reference. But is this case all named constants of created application should be replaced by its numeric values at development.<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

The common recommendations could be as follows:<o:p></o:p>
<o:p></o:p>

1. Development stage.<o:p></o:p>
<o:p></o:p>

1.1 Use early binding on development stage as it gives access to the type library of created application.<o:p></o:p>
Example:<o:p></o:p>
Rich (BB code): 
Dim WordApp As New Word.Application
<o:p></o:p>
After that place the dot char after WordApp object variable and all its available method and properties can be chosen from appeared listbox.<o:p></o:p>
Debugging in this case is clear and simple.<o:p></o:p>
<o:p></o:p>

2. Finalizing stage <o:p></o:p>
<o:p></o:p>

2.1 Use Option Explicit statement in code module (it is recommended to use it always).<o:p></o:p>
<o:p></o:p>

2.2 Replace all named constants of created application to its values.<o:p></o:p>
For example, find named constant of Word application with wd prefix like wdPasteMetafilePicture.
Select it, press Shift-F2 and look on the button line of appeared window.<o:p></o:p>
You can see that: Const wdPasteMetafilePicture = 3<o:p></o:p>
Close type library window and replace wdPasteMetafilePicture constant to 3.<o:p></o:p>
<o:p></o:p>

2.2 Replace early binding declaration to the later binding one. <o:p></o:p>
Example:
Rich (BB code): 
Dim WordApp As Object
Rich (BB code): 
Set WordApp = CreateObject("Word.Application")
<o:p></o:p>
<o:p></o:p>

2.3 Compile project (VBE menu: Debug - Compile VBA Project).<o:p></o:p>
Compiler warms and select one by one each not converted to values named constant (refer to 2.2). Replace it in a way described above.

After successful compilation of project test it.


That’s all. Sorry for lengthy post ;)
<o:p></o:p>

Regards,<o:p></o:p>
<?xml:namespace prefix = st1 /><st1:City><st1:place>Vladimir</st1:place></st1:City>
 
Last edited:
Upvote 0
While searching for my problem, I came across this thread. This is close to what I need except 2 things...


1) It didn't keep the source formatting from Excel

Does anyone know how to modify the code in this thread to paste the picture with all the formatting from Excel intact?

Here's a range/picture in Excel that I would like to look the same in Word after it's been pasted...

<TABLE style="WIDTH: 242pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=323 x:str><COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5233" width=147><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3271" width=92><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2986" width=84><TBODY><TR style="HEIGHT: 33.6pt; mso-height-source: userset" height=45><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: red; WIDTH: 110pt; HEIGHT: 33.6pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 height=45 width=147>Phone Color</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; WIDTH: 69pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=92>Phone Type</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; WIDTH: 63pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl30 width=84>Total</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=18>Grey</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>Cell</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 1.5pt solid" class=xl27 align=right x:num>3</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>Silver</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Landline</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl23 align=right x:num>42</TD></TR><TR style="HEIGHT: 22.2pt; mso-height-source: userset" height=30><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: yellow; HEIGHT: 22.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=30>Grand Total</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32></TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl33 align=right x:num>45</TD></TR></TBODY></TABLE>

I don't care if the data is editable or not, as a matter of fact, if it's quicker to code without the data, leave it out and truly paste it as a picture only.


2) There seems to be no code for it to be placed into a specific FIELD when pasting into Word.


I have working code below here (well, one line of it, the relevant one) that does just that but it will only copy a range of cells as raw data which looks ugly and makes no sense on the Word side.

Code: 
        .formfields("FIELD01").Result = ws.Range("E01").Value



Can anyone figure out how to do these 2 things?

Thanks a bunch to anyone that can figure this one out, I've been searching for this solution for hours and hours.
 
Upvote 0
Hi,

In Excel instead of code like this: ActiveSheet.Range("A1:C7").Copy
Use that one: ActiveSheet.Range("A1:C7").CopyPicture Appearance:=xlScreen, Format:=xlPicture

And paste it in Word by this method: WordApp.Selection.Paste

Vladimir
 
Upvote 0
ZVI,

If not too much trouble, could you provide the code to open up a Word Doc named "Apple, 01" and paste this into FIELD20 into that Word Document?


Thanks
 
Upvote 0
This is the full code template for copying of selection from Excel to the end of already open MyWordDocument.doc document:

Rich (BB code): 

' ZVI:2009-08-25 http://www.mrexcel.com/forum/showthread.php?t=355225
Sub CopyPicToOpenDocFromExcel()
  
  Dim objWord, objDoc
  
  Const DocName = "MyWordDocument.doc"  ' <-- tune it to suit
  
  ' Try to find already open Word application
  Set objWord = GetObject(, "Word.Application")
  If objWord Is Nothing Then
    MsgBox ("Open Word Application not found"), vbExclamation
    Set objWord = Nothing
    Exit Sub
  End If
    
  ' Try to find Word document
  On Error Resume Next
  Set objDoc = objWord.Documents(DocName)
  If Err <> 0 Then
    MsgBox (DocName & " window not found"), vbExclamation
    Set objDoc = Nothing
    Set objWord = Nothing
    Exit Sub
  End If
  
  ' Activate Microsoft Word window(comment it if not required)
  objWord.Tasks("Microsoft Word").Activate
    
  ' Go to the end of Word-document (use your code for proper selection)
  objDoc.Activate
  With objWord.Selection
    .EndKey Unit:=6 ' 6 = wdStory
    .TypeParagraph
  End With
  
  ' Copy from Excel to Word
  Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture ' copy Excel selection
  objWord.Selection.Paste         ' paste into Word
  Application.CutCopyMode = False ' set off copy mode of Excel
  
  ' Release the memory
  Set objDoc = Nothing
  Set objWord = Nothing
  
End Sub

Regards,
Vladimir
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Create Word document and center title
Replies
5
Views
368
Tetra201
Tetra201
C
  • Question
Macro run from Excel to transfer data in new Word doc won't output into Word
Replies
5
Views
299
cpu97
C
M
  • Solved
Insert a picture into a Word document
Replies
4
Views
153
RoryA
RoryA
M
  • Solved
Word Header Text
Replies
1
Views
111
RoryA
RoryA
D
  • Question
Create empty table (with headings) in Word document after some text
Replies
5
Views
460
dwsteyl
D

Forum statistics

Threads
1,221,783
Messages
6,161,938
Members
451,730
Latest member
BudgetGirl

Share this page

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