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-center/2018/06/vba-insert-picture-bug.xlsm
List of upcoming seminars: Excel Seminar Schedule
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-center/2018/06/vba-insert-picture-bug.xlsm
List of upcoming seminars: Excel Seminar Schedule
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2214.
Really Annoying VBA Bug When You're Trying to Insert a Picture.
Alright.
So, hey, this started happening in Excel 2010.
I just got burnt by it again recently. So I'm going to insert a picture in this workbook, but I want to record that action so I can automatically do it.
View, Macros, Recorder Macro, HowToInsertAPicture.
Perfect.
And I'm just going to insert a picture here: Illustrations, Pictures, let's choose one of our rocket photos and Insert.
Alright, stop recording.
Beautiful.
Now, I'm going to get rid of that.
I want to take a look at the macros, so Alt+F8, HowToInsertAPicture, Edit, and it says is this: ActiveSheet.Pictures.Insert and then the path to the picture.
Alright.
Yeah, that sounds good.
And, in fact, we should be able to run this.
So Alt+8, HowToInsertAPicture, and Run, and we get the picture-- that's beautiful.
Until I save this workbook and let you download it or send it to someone else, and then the picture doesn't show up at all-- all I get is a red X saying, Hey, we can't find the picture anymore.
Like what do you mean you can't find the picture?
I asked you to insert a picture, not a link to the picture.
But starting in Excel 2010, this recorded code is actually inserting a link to the picture.
And if I open this workbook somewhere on a computer that doesn't have access to this drive and that picture: red X. Super annoying.
Alright.
So, for some reason, in Excel 2010, the new thing to do, is instead of ActiveSheet.Pictures.Insert you do ActiveSheet.Shapes.AddPicture.
Alright.
And we can still specify a file name, but then these extra arguments that we have, LinkToFile=msoFalse.
In other words, don't create the stupid red link-- and then, SaveWithDocument:=msoTrue-- which means, actually put the darn picture in there and they can specify where it's supposed to be-- the left, the top… Now, how do we figure out the height and the width?
Alright.
Well, we want to resize this proportionally, right?
So I'm going to hold down the Shift key, like, get this back to less than one screen full of data, maybe like that right there.
So that's my goal.
I want to insert the picture and have a beam that size with that selected.
I'll come back to VBA Alt+F11, Ctrl+G for the immediate window, and I'll ask for: ?
selection.width-- so that's question mark, space, selection dot width and a question mark, selection dot height (? Selection.height).
Alright, and that tells me about 140 and 195-- so the width, 140, and 195, like that.
Get rid of the immediate window, and then here we'll delete this and run the code, and it actually inserted it.
It's the right size, it'll be able to be open when you download this, or I download this, if you don't have access to the original picture.
I get it, things change, they had to change the code.
But the fact they didn't update the macro recorder, and the macro recorder was giving us the bad code, that doesn't work.
That's super annoying.
Well, hey to learn more about macros check out this book, Excel 2016 VBA Macros, by Tracy Syrstad and myself.
We actually have a version of this for every version going back to excel 2003.
So, whatever you have, provided it's Windows, there's a version for you.
Alright, wrap-up today-- it's my problem.
I recorded code to insert a picture and it's creating a link to the picture, so anyone else I send the workbook to can't see the picture.
Instead, I'm using the Macro recorder that does ActiveSheets.Shape.Picture, use this new ActiveSheet.Shapes.AddPicture.
Or we can specify LinkToFile, no; save with document, yes; and you'll be good to go.
Well, hey, thanks for watching, I'll see you next time for another netcast from MrExcel.
Really Annoying VBA Bug When You're Trying to Insert a Picture.
Alright.
So, hey, this started happening in Excel 2010.
I just got burnt by it again recently. So I'm going to insert a picture in this workbook, but I want to record that action so I can automatically do it.
View, Macros, Recorder Macro, HowToInsertAPicture.
Perfect.
And I'm just going to insert a picture here: Illustrations, Pictures, let's choose one of our rocket photos and Insert.
Alright, stop recording.
Beautiful.
Now, I'm going to get rid of that.
I want to take a look at the macros, so Alt+F8, HowToInsertAPicture, Edit, and it says is this: ActiveSheet.Pictures.Insert and then the path to the picture.
Alright.
Yeah, that sounds good.
And, in fact, we should be able to run this.
So Alt+8, HowToInsertAPicture, and Run, and we get the picture-- that's beautiful.
Until I save this workbook and let you download it or send it to someone else, and then the picture doesn't show up at all-- all I get is a red X saying, Hey, we can't find the picture anymore.
Like what do you mean you can't find the picture?
I asked you to insert a picture, not a link to the picture.
But starting in Excel 2010, this recorded code is actually inserting a link to the picture.
And if I open this workbook somewhere on a computer that doesn't have access to this drive and that picture: red X. Super annoying.
Alright.
So, for some reason, in Excel 2010, the new thing to do, is instead of ActiveSheet.Pictures.Insert you do ActiveSheet.Shapes.AddPicture.
Alright.
And we can still specify a file name, but then these extra arguments that we have, LinkToFile=msoFalse.
In other words, don't create the stupid red link-- and then, SaveWithDocument:=msoTrue-- which means, actually put the darn picture in there and they can specify where it's supposed to be-- the left, the top… Now, how do we figure out the height and the width?
Alright.
Well, we want to resize this proportionally, right?
So I'm going to hold down the Shift key, like, get this back to less than one screen full of data, maybe like that right there.
So that's my goal.
I want to insert the picture and have a beam that size with that selected.
I'll come back to VBA Alt+F11, Ctrl+G for the immediate window, and I'll ask for: ?
selection.width-- so that's question mark, space, selection dot width and a question mark, selection dot height (? Selection.height).
Alright, and that tells me about 140 and 195-- so the width, 140, and 195, like that.
Get rid of the immediate window, and then here we'll delete this and run the code, and it actually inserted it.
It's the right size, it'll be able to be open when you download this, or I download this, if you don't have access to the original picture.
I get it, things change, they had to change the code.
But the fact they didn't update the macro recorder, and the macro recorder was giving us the bad code, that doesn't work.
That's super annoying.
Well, hey to learn more about macros check out this book, Excel 2016 VBA Macros, by Tracy Syrstad and myself.
We actually have a version of this for every version going back to excel 2003.
So, whatever you have, provided it's Windows, there's a version for you.
Alright, wrap-up today-- it's my problem.
I recorded code to insert a picture and it's creating a link to the picture, so anyone else I send the workbook to can't see the picture.
Instead, I'm using the Macro recorder that does ActiveSheets.Shape.Picture, use this new ActiveSheet.Shapes.AddPicture.
Or we can specify LinkToFile, no; save with document, yes; and you'll be good to go.
Well, hey, thanks for watching, I'll see you next time for another netcast from MrExcel.
