Transferring Hyperlink Question

bmsteffe

New Member
Joined
Jul 18, 2008
Messages
4
I am currently creating a spreadsheet to send to another company. This speadsheet has links to photos that are on my firms server. So when we send the spreadsheet to them they will not be able to access our server and therefore the links will be useless. I was wondering what is the best way to create a generic hyperlink so that anybody could use it no matter what server they are on.

Thanks for all the help.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

bmsteffe

New Member
Joined
Jul 18, 2008
Messages
4
I guess what I mean't by "generic" was a hyperlink that could be used by anybody regardless of what server they are viewing the pictures on. After looking at the website you sent me, would the best solution to my problem be to use a relative hyperlink?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi,bmsteffe
You could import the Pictures into the sheet and Hide them .
You could then make them Visible as required by a bit of code run from a Button , Shape etc.
Regards Mick
 

bmsteffe

New Member
Joined
Jul 18, 2008
Messages
4

ADVERTISEMENT

Thanks MickG, that seems like it could be a very good option. By the end of acquiring the data I will have about 250 pictures in this particular spreadsheet. So would that be a problem? Also, is there any easy, quick way you could give me a little direction on how to hide the pictures and to make a button?
 

random_noise

Active Member
Joined
Dec 19, 2007
Messages
367
An easier solution might be to upload the images to an online photo store or ftp site if you have one (assuming they are not confidential etc) then link to the full url of the picture.

Might take a while to upload them but it means anyone anywhere can access them and it keeps your excel file size down.

Just make sure you read the T's & C's of any online site as some of them state you no longer own the copyright to the pics once uploaded :eek:
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi This first bit of code will insert all you pictures on the sheet.
It will also Places there Names (These will be names like "Picture 1" -Excel gives them these names) in column "A" starting in row 2.
You will have to alter the file path and extension to suit.
If you dont want to do this you will have to insert them (and there new names) all individually, and that could take some time.

The second Bit of code will Insert them on the sheet.
The code must be run from a "Selectionchange" event.
When you Select a name from Column "A" that picture will become "Visible"
When you Select another name the first Picture is "Hidden" and the new Pictures Shows.
You will see in the code some details about size and range position, that you can alter to suit.
Place on you sheet, somewhere suitable the words "Picture Delete" ( No Commas)
When you select "Picture Delete" any Pictures on the page will be Hidden.

Place Picture on Sheet from File :- Code
Code:
Dim myDir As String, fn As String
Dim Fpth As String, c As Integer
myDir = "C:\Documents and Settings\test\My Documents\My Pictures"
fn = Dir(myDir & "\*.jpg")
c = 1
Do While fn <> ""
 Fpth = myDir & "\" & fn
 
 ActiveSheet.Pictures.Insert(Fpth).Select
    With Selection
        .Height = 50
         .Width = 100
          .Top = Range("c1").Top
           .Left = Range("c1").Left
            c = c + 1
         Cells(c, 1) = Selection.name
       End With
    fn = Dir
Loop

Select Picture (Visible) on sheet:-Code.
Code:
 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oPic As shape, allpic As String, Pic As String
 Dim oP As String, c As Integer
 Application.ScreenUpdating = False
If Target.Value = "" Then Exit Sub
If Target.Value <> "" Then
    If split(Target.Value, " ")(0) = "Picture" Then
 
    Me.Pictures.Visible = True
        For Each oPic In Me.Shapes
            If oPic.Type = 13 Then
                oPic.Visible = False
            End If
         Next oPic
 
    If Not Target.Value = "Picture Delete" Then
         Pic = Target.Value
            ActiveSheet.Shapes(Pic).Visible = True
        With ActiveSheet.Shapes(Pic)
             .Height = 200
                .Width = 300
                 .Top = Range("b1").Top
                    .Left = Range("b1").Left
        End With
    End If
  End If
End If
Application.ScreenUpdating = True
End Sub
Have a Go
Good Luck
Mick
 

bmsteffe

New Member
Joined
Jul 18, 2008
Messages
4
Basically what I am looking for is something I could do that would recognize if I changed the folder the pictures were in, the hyperlink would recongnize it too and update by itself without me having to go back and redo all the links. Thanks again for all the help guys.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,918
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top