VBA Insert Picture Bug
June 06, 2018 - by Bill Jelen
I needed to have an Excel macro insert a picture in Excel. I turned on the macro recorder, inserted the picture. But when I used that recorded code, the picture will not appear on any other computer. Instead, I get a red X where the picture should be.
The code used to work in Excel 2007 or earlier. But something changed in Excel 2010. When you run code to ActiveSheet.Pictures.Insert, Excel is *not* inserting the picture. Instead, it is inserting a link to the picture.
Of course, when you run the code on your computer, it appears like everything worked. The picture appears.
But when someone opens the workbook on another PC, they get a red X and a message that the picture may have been moved or renamed.
Well, of course the image is not on my Manager's computer. I did not ask for Excel to create a link to the picture. I asked for Excel to insert the picture. But the recorded code is inserting a link to the picture.
The solution is to switch to different code. By using Shapes.AddPicture, you can specify
LinkToFile:=msoFalse. Here is the code to use:
Sub HowToInsertPicture() ' This code fails in 2010 or newer ' ActiveSheet.Pictures.Insert( _ ' "C:\Users\Bill\Desktop\PhotoPrint\All Rocket\aWalg180422-3.jpg").Select NewFN = "C:\Users\Bill\Desktop\PhotoPrint\All Rocket\aWalg180422-3.jpg" ActiveSheet.Shapes.AddPicture(Filename:=NewFN, _ LinkToFile:=msoFalse, _ SaveWithDocument:=msoTrue, _ Left:=1, Top:=1, Width:=140, Height:=195).Select End Sub
Download Excel File
To download the excel file: vba-insert-picture-bug.xlsm
Many times, the macro recorder in Excel records the wrong code. With the fix above, you can successfully insert images using VBA.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"Excel is a tool in service of the people who have to live with the consequences."
Title Photo: Brigitta Schneiter on Unsplash