Vlookup for picture

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,531
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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):

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

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.

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.
 

Attachments

  • Picture Lookup.xlsm
    40.7 KB · Views: 471
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)
 
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. ??
 
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?
 
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.
 
Okay, that is weird. I've got Excel 2007 and Excel 2010 installed. I'm running IE9 though... I wonder if that's it?
 
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...
 
I reported it to Firefox as an issue. Sorry for the thread hijack, feel free to delete away!
 
Hi together...

Thanks, it helped me, as I have the same problem and firstly thought it may perhaps be a forum issue.
Downloading the file and then opening it from the download folder works.

Regards :)
 
Back
Top