Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Vlookup for picture

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14

    Vlookup for picture



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  2. #2
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Doh! Thanks Zack. I don't know why I didn't figure that out on my own.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  4. #4
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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 (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  6. #6
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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 (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  8. #8
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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 (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  10. #10
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    I reported it to Firefox as an issue. Sorry for the thread hijack, feel free to delete away!
    Regards,
    Zack Barresse

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •