You can just set it as the old (source) name from the target worksheet...
Code:' Set oPic_new = Selection Set oPic_new = wsReport.Pictures(oPic_src.Name)
We're building a dashboard for our golf course at work, and one of the things that we have implemented is a weather forecast. We pull the weather forecast from an XML data feed, and then I want to put in a picture forecast, rather than the text.
So no problem, we knock up a VLOOKUP for pictures. (This is based off a workbook from McGimpsey.)
Everything was working fine for me until I decided to put all my source pictures on a different sheet then copy them in to the first sheet when needed. I've got it working, but I'm curious if there is a better method for a part of it. Here's the main routine (called from a Worksheet_Change event):
The part that rankles me is the "select" in the middle. I can duplicate my image on the lookup sheet, then cut it and paste it to the report sheet. At that point it loses connection to the object, so I have to restablish that.Code:Public Sub UpdateWeather(rngTarget As Range) Dim oPic_src As Picture Dim oPic_update As Picture Dim oPic_new As Picture Dim wsReport As Worksheet Dim wsPicLookup As Worksheet Dim lPic As Long Application.ScreenUpdating = False 'Set worksheets Set wsReport = Worksheets("Report") Set wsPicLookup = Worksheets("PictureLookup") With wsReport 'Work out which picture should be updated. (Need column of range) lPic = Intersect(.Range("rng_weather"), rngTarget).Column - .Range("rng_weather").Cells(1, 1).Column + 1 'Set reference to pictures to replace/replace with Set oPic_update = .Pictures("pic_day" & lPic) Set oPic_src = wsPicLookup.Pictures(Application.WorksheetFunction.VLookup(rngTarget.Value, wsPicLookup.Range("tbl_weatherpics"), 2, False)) 'Copy picture to be used and place it where old picture resides Set oPic_new = oPic_src.Duplicate oPic_new.Cut .Paste Set oPic_new = Selection With oPic_new .Top = oPic_update.Top .Left = oPic_update.Left .Name = oPic_update.Name End With 'Delete old picture oPic_update.Delete End With rngTarget.Activate End Sub
Does anyone know a way to transfer a picture from one worksheet to another without having to select it in between?
(Also, why is it that "picture" seems invisible to Intellisense in Excel 2010?)
Sample file is attached.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
You can just set it as the old (source) name from the target worksheet...
Code:' Set oPic_new = Selection Set oPic_new = wsReport.Pictures(oPic_src.Name)
Regards,
Zack Barresse
Doh! Thanks Zack. I don't know why I didn't figure that out on my own.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Sometimes it's the simple things we overlook.
Also, I went to open your attachment, an xlsm file, and it tried opening as an xls file. For some reason it thinks that file is a 2003 file. Is there an association on the board you think? I can't duplicate it on any other forums. ??
Regards,
Zack Barresse
Don't know... I don't have 2003 installed here, and it opens fine for me in Excel 2010. In fact, I just click the link above and it opens in 2010.
What's your default app for xlsm files? Are you sure it's not 2003?
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
I don't have any other versions except 2010 installed on this machine. File association is just with Microsoft Excel. Nothing I can't workaround, just had to save and open, which it did just fine. Was just a little weird, I'd never seen that before.
Regards,
Zack Barresse
Okay, that is weird. I've got Excel 2007 and Excel 2010 installed. I'm running IE9 though... I wonder if that's it?
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
This was while using Firefox 4.0, although I tried in IE 8.0 and it works a treat. Must be a Firefox thing. Strange...
Regards,
Zack Barresse
I used to have issues opening xlsm and xlsx files in IE7, iirc. It kept opening them as zip files.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
I reported it to Firefox as an issue. Sorry for the thread hijack, feel free to delete away!
Regards,
Zack Barresse
Bookmarks