Excel VBA Visuals: VBA Insert Picture Bug - Episode 2214
Microsoft Excel Tutorial: VBA to insert picture in Excel is creating a link instead of inserting the picture.
Welcome to another episode of the MrExcel Podcast, where we explore all things Excel. In today's episode, we will be discussing a really annoying VBA bug that occurs when trying to insert a picture into a workbook. This bug has been around since Excel 2010 and I recently got burned by it again, so I wanted to share my experience with you all.
So, here's the issue. You want to insert a picture into your workbook and record that action so you can automatically do it in the future. You go to View, Macros, Recorder Macro, and everything seems to be going smoothly. You insert the picture and stop recording. But when you save the workbook and try to open it on another computer, the picture doesn't show up. Instead, you get a red X saying that the picture cannot be found. What's going on here?
Well, it turns out that starting in Excel 2010, the recorded code for inserting a picture actually creates a link to the picture instead of actually inserting it. This means that if the workbook is opened on a computer that doesn't have access to the original picture, the picture will not show up. This is not only frustrating, but it also defeats the purpose of recording the action in the first place.
But fear not, there is a solution. Instead of using ActiveSheet.Pictures.Insert, we can use ActiveSheet.Shapes.AddPicture. This allows us to specify the file name and also includes two important arguments: LinkToFile=msoFalse and SaveWithDocument=msoTrue. These arguments ensure that the picture is actually inserted into the workbook and not just linked to it. We can also specify the height and width of the picture to ensure it is inserted proportionally.
I know this may seem like a small issue, but it can be a major headache when trying to share workbooks with others. So, if you want to avoid this bug, make sure to use ActiveSheet.Shapes.AddPicture instead of ActiveSheet.Pictures.Insert. And if you want to learn more about macros, be sure to check out my book "Excel 2016 VBA Macros" co-authored with Tracy Syrstad. We have versions for every Excel version going back to 2003, so there's something for everyone.
Thank you for tuning in to this episode of the MrExcel Podcast. I hope this information was helpful and saved you from the frustration of dealing with this VBA bug. Don't forget to like and subscribe for more Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
#excel
#microsoftexcel
#excelbug
#evergreen
#excelvba
This video answers these common search terms:
how to attach a picture to a cell in excel vba
how to insert a picture to a cell in excel vba
bug when inserting picture in excel with vba
why does vba insert image link instead of image?
Table of Contents:
(00:00) Annoying VBA Bug When Inserting Pictures
(00:10) Recording the Action
(00:20) Viewing and Editing the Recorded Macro
(00:56) The Problem with the Recorded Code
(01:31) Solution: Using ActiveSheet.Shapes.AddPicture
(02:08) Resizing the Picture Proportionally
(03:12) Wrap-Up and Recommendation for Learning Macros
(03:41) Clicking Like really helps the algorithm
Bill's problem today: I recorded code to insert a picture, and it is creating a link to the picture
Don't use the recorded code of ActiveSheet.Pictures.Insert
Instead use updated code of
ActiveSheet.Shapes.AddPicture(Filename:=NewFN, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
To download this workbook: https://www.mrexcel.com/download-cent...
List of upcoming seminars: https://www.mrexcel.com/press-appeara...
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Смотрите видео Excel VBA Visuals: VBA Insert Picture Bug - Episode 2214 онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь MrExcel.com 05 Июнь 2018, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 14,154 раз и оно понравилось 115 людям.