Transferring Hyperlink Question

[bmsteffe]

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.

 

[Andrew Poulsom]

I don't know what you mean by generic, but in Excel hyperlink paths are by default relative to the location of the workbook:

http://office.microsoft.com/en-us/excel/HP030784851033.aspx

 

[bmsteffe]

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]

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]

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]

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

 

[MickG]

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

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.

 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]

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.