Inserting an Image and Text into Word Using Excel VBA

[KMacattack]

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.

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

 

[UHsoccer]

Thanks for the update.

I am puzzled by the definition for TopCodeCell and why it is needed and if so, WHERE to define it. Is it in WORD or Excel

' --> User settings, change to suit
  Const TemplateFile = "Test.doc"                 ' Template file name
  Const FieldName = "FIELD"                       ' The first 5 symbols of bookmark name
  Const TopCodeCell = "A2"                        ' The topmost cell address with "FIELDnn" code
  TemplateFolder = ThisWorkbook.Path              ' The folder with Template file
  PicturesFolder = ThisWorkbook.Path & "\Images"  ' The folder with JPG files
  ' <-- End of user settings

Looking at the code, it appears to transfer directly from disk to word via the use of bookmarks. I added the bookmarks (FIELD01, etc) in my Test.doc

Not sure what to do with the "A2" setting. If in Word, how do I define that?

Please advice

 

[ZVI]

Ok, let's try to solve the puzzle

All code are in VBA Excel module.
And according to your post #38 the table Code – Picture name in the Excel active sheet looks as follows:

Excel Workbook
	A	B
1	Code	Picture name
2	Field01	CDlarge
3	Field02	Excel
Sheet1

Address of the cell A2 of this table is defined in the code by the constant TopCodeCell
Generally you can define it according to your real table layout.

You can download archive (39 KB) with full set of testing files from this link: MrExcel_355225.zip
Extract all files, load CopyPicToOpenDocFromExcel_05.xls and use the button in Sheet1 to run the code of Sub FromExcelToWordForm_05().

Let me know if the task is different or if you stuck with implementation of the code.

Regards,

 

[UHsoccer]

Vladimir,

I am staying with the implementation as suggested.

I added some msgbox statements to track progress

1) I checked my doc and there are two bookmarks, FIELD01 and FIELD02. They are in separate tables. Also added bookmarks OUTSIDE the table (makes no difference)

2) It locates and insert 2 pictures
BUT: The word doc does not display them!

3) The "delete bookmark" step did not locate any bookmarks

I can send you my doc and Excel file, but I think there must be a small error in my setup.


PS, I do not have an account to download your example. I can give you my email if you like

Henry

You are living in the "vacation spot" of your part of the world. Hope it is a pleasant setting

 

[ZVI]

Henry, I've PM'ed you my e-mail.

Let me know yours one to send you archive with working example.

You are living in the "vacation spot" of your part of the world. Hope it is a pleasant setting

It's true! But at the same time Sevastopol is the naval base of two fleets

Regards