VBA copy paste picture into Word TextBox Problem

AntiqueWhale · #1 11-12-2018, 02:17 PM

Hi everyone, I am new guy here. Got some questions about vba automation

I am developing an automation script in Excel which will copy some excel rng as the picture and paste to the corresponding TextBox in Word.

The second picture pasted will push the first pasted picture down as I expected, however, the third pasted picture will delete the previous 2 pictures and appears on the top, and the 4th pasted picture pushes down the 3rd pasted picture as expected again.

What I want is to paste the four pictures to the textbox, however, after running the code, I only have the last 2 pictures in the textbox and the first 2 are deleted automatically. I don't know what is happening here.

Thanks for your attention and help

I have some setup like this:

Code:

    Dim appWord As Object, docWord As Object
    On Error Resume Next
    Set appWord = GetObject(, "Word.Application")
    If Err.Number <> 0 Then Set appWord = CreateObject("Word.Application")
    appWord.Visible = True
    Dim templatePath As String
    templatePath = "C:\Users\JackMa\Desktop\WordTemplate.docx"
    Set docWord = appWord.Documents.Open(templatePath, Visible:=True)
    Dim WORD_BOX As Object
    Dim EXCEL_RNG As Range

Below is the code snippet

Code:

    Set WORD_BOX = docWord.Shapes("MyTextBox")
    
    Set EXCEL_RNG = ThisWorkbook.Sheets("Data").Range("AW387:BJ409")
    EXCEL_RNG.CopyPicture xlPrinter, xlPicture
    WORD_BOX.TextFrame.TextRange.Paste
    
    With WORD_BOX.TextFrame.TextRange.InlineShapes(1)
        .LockAspectRatio = msoFalse
        .Width = WORD_BOX.Width - 1
        .Height = 200
    End With

    
    Set EXCEL_RNG = ThisWorkbook.Sheets("Data").Range("AW310:BJ330")
    EXCEL_RNG.CopyPicture xlPrinter, xlPicture
    WORD_BOX.TextFrame.TextRange.Paste
    
    With WORD_BOX.TextFrame.TextRange.InlineShapes(1)
        .LockAspectRatio = msoFalse
        .Width = WORD_BOX.Width - 1
        .Height = 180
    End With
    
    Set EXCEL_RNG = ThisWorkbook.Sheets("Data").Range("AW233:BJ260")
    EXCEL_RNG.CopyPicture xlPrinter, xlPicture
    WORD_BOX.TextFrame.TextRange.Paste
    
    With WORD_BOX.TextFrame.TextRange.InlineShapes(1)
        .LockAspectRatio = msoFalse
        .Width = WORD_BOX.Width - 1
        .Height = 210
    End With

    Set EXCEL_RNG = ThisWorkbook.Sheets("Data").Range("AW156:BJ175")
    EXCEL_RNG.CopyPicture xlPrinter, xlPicture
    WORD_BOX.TextFrame.TextRange.Paste
    
    With WORD_BOX.TextFrame.TextRange.InlineShapes(1)
        .LockAspectRatio = msoFalse
        .Width = WORD_BOX.Width - 1
        .Height = 190
    End With

macropod · #2 11-12-2018, 03:19 PM

What is the point of pasting the pictures into textboxes?

AntiqueWhale · #3 11-12-2018, 05:02 PM

Quote:

Originally Posted by macropod

What is the point of pasting the pictures into textboxes?

Thanks, Paul for replying.

My scenario is I have a word template for the report, which consists of a bunch of Textboxes. Some of the Textboxes will be used for just typing comments, some of them are for data presentation.

I have an excel application, which has the charts, tables, and ranges that need to be inserted into the Word report.

What I am doing is writing VBA in Excel, copy those tables, ranges as the picture and paste them into corresponding Textboxes, and format them to fill those Textboxes

AntiqueWhale · #4 11-13-2018, 07:38 AM

After several experiments, I find I should use Collapse method before Paste, the problem solved. Hope this small thing helps people who have similar problem

Code:

WORD_BOX.TextFrame.TextRange.Collapse 0
WORD_BOX.TextFrame.TextRange.Paste

macropod · #5 11-13-2018, 01:21 PM

You really don't need textboxes for the picture insertion - you can insert and resize your pictures quite easily without them. That said, if the purpose of the textboxes is to serve as placeholders, you'd do better to use bookmarks or, better yet, to use tables with cells that have been given the required fixed dimensions; any pictures you insert into such cells will automatically resize to fit the space available whilst maintaining the correct aspect ratio. That, in turn, obviates the need for any resizing code.

To set the fixed:
• row height, use Table Tools>Layout>Properties>Row>Specify Height>Exactly.
• column width, first uncheck the 'automatically resize to fit contents' option under Table Tools>Layout>Properties>Table>Options. Then set the preferred column width under Table Tools>Layout>Properties>Columns.
You might also want to set the cell margins to 0 all round. It's also a good idea to format the cell paragraph with 0 before/after spacing.

For an automated approach to this, see: https://www.msofficeforums.com/word-...html#post47919. Obviously, though, if you're only inserting the pics one at a time, the code would need to be modified to suit.

AntiqueWhale · #6 11-13-2018, 01:36 PM

Thank u very much for the valuable advice. I am actually kind of new to Word. I spend most of the time writing Excel VBA Application. The "Table" and "Bookmark" method is unfamiliar to me. Do you have recommendations for book or tutorial about WORD APPLICATION? I am willing to learn new thing.

For this automation work, my code now is nearly 2000+ lines, I can not just redo it from scratch. But I would like to learn new things and come back to this code to modify it.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
wrapping text around a picture within a textbox	ketanco	Word	3	04-06-2016 11:07 AM
Making an HTML Textbox Clickable (+ copy, paste, etc.)	Jonas.t1989	Outlook	0	08-25-2015 07:11 AM
Problem with copy paste	ayiru	Word	4	03-30-2012 04:27 AM
Copy/Paste from Paint to Word - There is a problem saving the file	one2three	Word	1	10-26-2011 07:35 AM
Word Copy Paste Problem...	haxmania1	Word	1	07-30-2009 06:49 AM