Vlookup for picture

**Ken Puls** · 2011-03-30, 07:07 AM

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.

**Zack Barresse** · 2011-03-30, 06:36 PM

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)

**Ken Puls** · 2011-03-30, 06:44 PM

Doh! Thanks Zack. I don't know why I didn't figure that out on my own.

**Zack Barresse** · 2011-03-30, 06:47 PM

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. ??

**Ken Puls** · 2011-03-30, 06:50 PM

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?

**Zack Barresse** · 2011-03-30, 06:52 PM

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.