Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to manipulate an image in word via excel vba

0

Hello,

I wrote code to export a letter (a series of letters, as a matter of fact) with dates and other info from an Excel-workbook to Word via 'late binding'. This all works fine, except that an image (a logo of the firm) cannot be manipulated. It should be resized and text should wrap around it (right of the logo). I found all sorts of suggestions on internet, but none worked and by now I'm desperate. I don't know how to select the image once it has been imported in Word, and I don't know what the instructions for resizing are. Unfortunately this cannot be done in Word itself by making a macro there and copy this to the workbook, as Word unselects the image as soon as you start recording the macro...

I hope anyone has found a solution or workaround for this problem already. I cannot send you a file as I never found a clue...

By now I can add a small file in which you can see what I try to do. Comment and further explanation of my problem(s) is in this file as remarks...

Answer
Discuss

Discussion

Bill, did my Answer help? 
John_Ru (rep: 6172) May 16, '23 at 3:35 pm
Hello John,
Thank you for your reply. Unfortunately it didn'tt work, caused error 424. I prepared a short example.xlsm to show you what I am doing in the macro, but now I cannot find how to send that to you...
BillBroom (rep: 2) May 16, '23 at 6:33 pm
Bill

It's late here but please edit your original question and use the Add Files... button to upload your representative Excel file (without any personal data) to show your existing data and macros. I'll try to look at that tomorrow (UK time)
John_Ru (rep: 6172) May 16, '23 at 6:50 pm
Thanks again, John. I edited my question and added 'example.xlsm' with further remarks and questions inside this file.
BillBroom (rep: 2) May 17, '23 at 2:08 am
Okay, I'll try to look later but for future please note that key question detail should not be contained (solely) in the file.
John_Ru (rep: 6172) May 17, '23 at 3:06 am
Bill. Please see replacement Answer.
John_Ru (rep: 6172) May 17, '23 at 6:25 pm
Thank you John, resizing now works fine!
Next will be to find out how there can be text wrapped to the right of the logo.... Enjoy your weekend!
BillBroom (rep: 2) May 19, '23 at 10:00 am
You'll need to set wrapping to square (but first convert to a shape to set that). Please ask a new question, remembering to attach a representative Excel file (embedding any logo, like I did).

Have a great weekend too (thanks).
John_Ru (rep: 6172) May 19, '23 at 3:33 pm
Add to Discussion

Answers

0
Selected Answer

Hi Bill and welcome to the Forum.

Replacement Answer 17 May 2023:

Now you've added a file and clarified you're adding InlineShapes to a single document (rather than to several individual ones), please try the attached, revised file. I didn't have your logo so embedded "Wedding logo full.jpg" in the file- please save that out to your test macro location first then use F8 to step through the new maco line by line and see the image resized. The code is indented (for easier reading of blocks/loops) and commented below, with key changes in bold:

Sub write_letter()

ttb = 10

' ### Dim sh As Shape
' ### Application.ScreenUpdating = False

Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")

With WordApp
    ' display the document
    .Visible = True
    .Documents.Add
    
    For t = 1 To ttb    'starts a loop of letters with invitation for medical research, according to data in the file
    
        If t > 1 Then .Selection.insertnewpage
        
        ' ### didn't have your logo
        '.Selection.InlineShapes.AddPicture Filename:="C:\test_macro\logo_rgb.jpg", _
                                               LinkToFile:=False, SaveWithDocument:=True
        
        ' ### used this 1992x824 pixels (=1494x618 points) picture instead (saved as icon on revised file)
        ' adds InShape number t:
        .Selection.InlineShapes.AddPicture Filename:="C:\test_macro\Wedding logo full.jpg", _
        LinkToFile:=False, SaveWithDocument:=True
                                               
        ' scale the "t"th inline shape
        With .ActiveDocument.InlineShapes(t)
           ' ## set size directly to 1/6 by points
           '.Width = 249
           '.Height = 103
           
           ' ## or set set width to 5 cm and appropriate height
           .Width = Application.CentimetersToPoints(5)
           .Height = Application.CentimetersToPoints(2.07)
        End With
        
        With .Selection
            .Font.Name = "Times New Roman"
            .Font.Size = 12
            .Paragraphs.Alignment = 0
            .Font.Italic = True
            .typetext "Some text to open the letter"
            .TypeParagraph
            .Font.Italic = False
        End With
    Next t
End With

You'll need to work out the scaling of your logo (in cemtimetres as above or in points not pixels, as commented out above). The code above sets the logo width to 5cm using the Excel's CentimetersToPoints method (with height to match the logo's aspect ratio) since I suspect this will be easier. E.g. Application.CentimetersToPoints(5) converts 5cm to that measurement in points (141.732283464567pts.). 

Hope this helps- if so, please remember to return and mark this Answer as Selected.

Discuss

Discussion

Glad that worked. Thanks for selecting my Answer, Bill. 
John_Ru (rep: 6172) May 19, '23 at 2:49 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login