Share via

How do I automatically open Hyperlink (picture) into Excel Worksheet?

Anonymous
2013-06-27T13:36:43+00:00

I'm using HLOOKUP at Sheet2 to find details from database to Sheet1. One of the details is a Link to a picture from a file on my computer. The link to a file varies because I want to use a different pic on every lookup. The intention is to print the details WITH the picture to A4-sheet without printing the picture separetly or scaling the pic every time I want to print. I don't want to save all the pictures to another excel sheet because file would get too big. So I would want this to workout from a hyperlink.

I have this example for you, hope you get it:

https://www.dropbox.com/s/91x3326fw44fhe8/example.xlsm

I manage to show the pic via this code automatically but of course the pic-link (v = "") does not vary as I would want to:

Private Sub Worksheet_Activate()

Set Target = Range("B9:I19")

On Error Resume Next

Dim v As Variant

Dim p As Picture

Application.ScreenUpdating = False

For Each p In ActiveSheet.Pictures

If Not Intersect(p.TopLeftCell, Target) Is Nothing Then p.Delete

Next

v = "http://www.captainsshop.fi/verkkokauppa/images/73169ce4a606c14fb49be1c6e64c043d_1103_df1.jpg"

Set p = ActiveSheet.Pictures.Insert(v)

With p

.Height = Target.Height

.Width = Target.Width

.Top = Target.Top

.Left = Target.Left

End With

Application.ScreenUpdating = True

End Sub

Changing link as a cell doesn't work.

More here about the same topic (not quite):

http://answers.microsoft.com/en-us/office/forum/office\_2007-excel/automatically-open-hyperlink-into-excel-worksheet/3d9a1f8d-f9b3-4ca5-a056-d677555973be?tab=AllReplies

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2013-07-01T21:30:55+00:00

    I am not sure which version of Excel you are using, but after doing a little research, it looks like there are two ways to handle adding pictures within Excel 2007.  I have modified my original code and have tested it on my 2007 environment.  With this, I give you a caveat.  All of the locally saved pictures should be in the same format (i.e. jpg, bmp, png, etc), and the ".jpg" portion of the code modified to match.  Leaving off the extension does not appear to work properly for me and changing between format does not work without changing the code.  Take a look at it and see if you can use it, or modify it to what you need.

    [CODE]

    Option Explicit

    Private Sub Worksheet_Activate()

    ActiveSheet.Pictures.Delete

    On Error Resume Next

    Dim cell As Range

    For Each cell In Range("B7")

    If InStr(1, cell, "/", 1) Then

        Dim u As Variant

        Dim p As Picture

        u = Range("B7").Value

        p = ActiveSheet.Pictures.Insert(u)

            With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)

                .Top = Range("B7").Top

                .Left = Range("B7").Left

                .Height = 240

            End With

    Else

    If InStr(1, cell, "", 1) Then

        Dim strPicName As String

        strPicName = Range("B7").Value

            With ActiveSheet.Pictures.Insert(strPicName & ".jpg")

                .Left = Range("B7").Left

                .Top = Range("B7").Top

                .ShapeRange.LockAspectRatio = msoTrue

                .ShapeRange.Height = 240

            End With

    End If

    End If

    Next

    End Sub

    [/CODE]

    Was this answer helpful?

    0 comments

18 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-07-01T06:42:21+00:00

    Umm.. this works nicely with links from the internet. But it doesn't work that well on pictures in my computer (the main purpose). Sometimes it opens the pic, sometimes it doesn't. It didn't depend on the file because the same file works sometimes and sometimes not. E.g. I have one link to a web picture and the next is a file on a HD: web works, file doesn't (atm).

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-06-27T16:37:02+00:00

    I am not sure if you are able to have both boundary cells and keep the original image ratio because of the actual picture sizes.  For example, looking at the picture, the lifering picture is nearly a square, at 400x328, while the building picture is more rectangle, at 1000x665.  Adding the code below should allow you to tweak it to get to approximately where you need.  Just replace the ActiveSheet.Shapes from earlier.

    [CODE]

    With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)

        .Top = Range("B7").Top

        .Left = Range("B7").Left

        '.Height = 240 'Change to modify to keep all at the same height

        .Width = 385 'Change this to modify width - 385 should get it close to I19

    [/CODE]

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-06-27T15:44:35+00:00

    Omg. That works.  Thank You! Only one thing. I'm going to print the page so I want the pic to have boundary cells (with keeping the original image ratio). E.g. B7 to I19 in that example.xlsm. Any clue?

    I bet nobody else have had this problem because I didn't find a solution by 9 hours of googling. Fortunately I got paid during that time.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-06-27T14:46:42+00:00

    So, since we started this on the other thread, here you go.  The code below will work for what I think that you are trying to accomplish.  It does take a minute or two to run, since it is pulling the jpg from the internet, but it worked for me.

    [CODE]

    Private Sub Worksheet_Activate()

    On Error Resume Next

    Dim u As Variant

    Dim p As Picture

    u = Range("B7").Value

    ActiveSheet.Pictures.Delete

    p = ActiveSheet.Pictures.Insert(u)

    With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)

        .Top = Range("B7").Top

        .Left = Range("B7").Left

    End With

    End Sub

    [/CODE]

    Was this answer helpful?

    0 comments