Wise Owl Answers How do I paste and resize a picture in Word with Excel VBA?

Check out our other answers here • Wise Owl Answers
Download file here www.wiseowl.co.uk/vba-macros/...
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link www.wiseowl.co.uk/donate?t=1​ to make a donation. Thanks for watching!
Learn how to copy Excel data and paste it as a picture in a Word document. You'll see the difference between inline and floating shapes and how to resize both types by referencing the relevant shapes collection.
00:00 The Question
01:08 Creating a Word Document
02:41 Typing Text into the Word Document
04:21 Pasting Excel Data as a Word Table
05:44 The PasteSpecial Method
06:50 Pasting Excel Data as a Picture
09:15 Setting the Placement of a Picture
11:19 Returning the Current Width and Height
12:16 Resizing a Floating Shape
13:01 Resizing an Inline Shape
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Power BI, DAX, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access and more

[@chrisgray8774]

You answered my unasked question as well. Thank you.

[@WiseOwlTutorials]

Excellent, happy to hear that it helped!

[@natnaelayele4853]

Thank u so much for ur best, fun, and amazing videos

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@krn14242]

Thanks Andrew. Happy Easter!

[@WiseOwlTutorials]

My pleasure Kevin, and a Happy Easter to you and yours too!

[@RohithKK-uh7pp]

Beautifully explained as always. Thank you sir.

[@WiseOwlTutorials]

You're very welcome Rohith, thanks as always for your support!

[@hassanrozh6579]

Excellent

[@WiseOwlTutorials]

Thank you Hassan!

[@shinrafahell]

Thank you Andrew excellent topic.

[@WiseOwlTutorials]

Thanks Rafael, glad you enjoyed it!

[@PankajNegi007]

Great Sir I really enjoy this training session

[@WiseOwlTutorials]

Thanks Pankaj, glad you liked it!

[@KhalilYasser]

Thank you very much for your awesome videos.

[@WiseOwlTutorials]

You're very welcome Yasser! Thank you as always for your support!

[@rock78785]

This is awesome!!!!! Thank you for the video.😁😁

[@WiseOwlTutorials]

You're very welcome Kunal, glad you like it!

[@henrikijonkoping4694]

Exciting, I have for some time been thinking about how to create a quote for a customer from existing Excel data into e.g. Word. Here is an example, right?

[@WiseOwlTutorials]

Hi Henrik, exactly yes! This technique works well if you have a template document in Word which contains bookmarks. We have a video which covers this technique and you can see the relevant section starting at 51:48 kzbin.info/www/bejne/sGjKhqqrhN2Nd7s I hope it helps!

[@evaskoldqvist9214]

As always, your videos are top of the line. You always explain everything so clearly.

[@WiseOwlTutorials]

Hi Eva! Yes, you can copy pictures, you just need to know how to reference them Sub CopyPicToWord() Dim wd As Word.Application Dim ImgToCopy As Shape Set wd = New Word.Application wd.Visible = True wd.Documents.Add Set ImgToCopy = Sheet1.Shapes("My Picture") ImgToCopy.Copy wd.Selection.Paste End Sub You might find the videos on working with shapes useful kzbin.info/www/bejne/fXyampmOgpiUoqs I hope it helps!

[@evaskoldqvist9214]

@@WiseOwlTutorials Thank you so much for the super quick answer. It works!

[@asadmahboob1123]

Thank you Andrew, Plz make videos on "scrapping data from PDF" and how to use PowerShell command and Send keys in VBA. Thank you..

[@WiseOwlTutorials]

Thanks for the suggestions Asad! I'll put the PDF and SendKeys on my list. I don't think that PowerShell will happen in the near future - it needs an entire series to do it justice!

[@marc-andrelachapelle5707]

Great explanation, quick question; Since excel or word doesn't support OCR. But in VBa, you can add a complement for Onenote. Is it possible to extract picture to text by using Vba directly from Word/Excel then past the 'text' to word/excel file? Instead of going through OneNote to past then picture to text, etc.

[@WiseOwlTutorials]

Hi! That's an interesting question! I've not attempted to do this but several links appear to suggest that it is possible www.excelforum.com/excel-programming-vba-macros/1193886-possible-for-vba-to-to-ocr-embedded-picture-objects-and-copy-text-values-to-cells.html#post4703560 social.msdn.microsoft.com/Forums/office/en-US/9569d75c-eb85-4612-98b9-04a45a2b9e7b/onenote-is-not-giving-ocrtext-for-vba-inserted-image-in-onenote-2010how-to-force-onenote-to-do?forum=officegeneral I hope it points you in the right direction!

[@marc-andrelachapelle5707]

@@WiseOwlTutorials Hi, Thanks for your reply, appreciate it. I will take a look :) Hopefully that will help for my current problematic.

[@ravindrareddy7799]

I want one first row of excel as heading all word documents How to do that ?

[@nagarajuboga2532]

Hi sir, I have excel sheet and word template (separately). I can use word template to create another word with excel data filled in it by VBA code. I embedded the word template in to excel sheet. After that I trying to use embedded word template to create new doc with excel data in it ( just like when they are separate) but unable to do. Can you help me..

[@WiseOwlTutorials]

Hi Nagaraju, that's an interesting idea! It's not how Office is designed to work so there's no native support for it but it seems that it is possible. Here's one example stackoverflow.com/questions/54419143/edit-word-document-embedded-in-a-workbook-and-save-as-copy I hope it helps!

[@benjild1]

I have a number of charts on a single worksheet in Excel. I want to export a single picture of all of them in the png format to a folder. I have used the .CopyPicture method but it seems to fail sometimes. What is the best way to export a picture of charts or a specific range in Excel directly to a folder i.e. not to MicrosoftWord?

[@WiseOwlTutorials]

Hi there! I can't say that this is something I've ever needed to do but it appears that there's a range of creative solutions out there. The "simplest" approach seems to be, as you said in your question, using the CopyPicture method to copy the range of cells which the charts cover and then pasting this into an empty Chart object so that you can then use the Export method of the Chart to save the picture (I'm guessing that this is what you've already tried). One post addressed the unreliability of this technique and suggested adding a DoEvents statement between the CopyPicture method and the Paste method. I'm not sure if that helps. You could also try this fantastically complicated solution involving many Windows API function declarations! stackoverflow.com/questions/43904385/using-excel-vba-macro-to-capture-save-screenshot-of-specific-area-in-same-file/55354715#55354715

[@benjild1]

@@WiseOwlTutorials Thanks for the reply Andrew! I will definitely give that a go and yes that is what I’ve been trying to do i.e. paste into an empty Chart object and then use Export method.

[@WiseOwlTutorials]

@@benjild1 No worries, I hope it works!

[@benjild1]

@@WiseOwlTutorials Hi Andrew. It’s been about a week now since I applied your suggested fix to my question - I added DoEvents between CopyPicture and Paste. It worked! Everything has been chugging along very stable this week. Thanks again for your reply and solution.

[@WiseOwlTutorials]

@@benjild1 That's great to hear! I can't take the credit for it but I'm pleased to hear that it's working for you and thanks for taking the time to come back and let us know!

[@maksim0933]

Is mentioned technique the same for PowerPoint?

[@WiseOwlTutorials]

Hi! It's not quite the same, no. You might find this video helpful for PowerPoint kzbin.info/www/bejne/m2LOhoqQpqieiNk