Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Feb 2001
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an excel file that funtions as a log. This log has numbers that are created dynamically. I then have another excel file that is saved as this unique log number. For instance, log number 12345 has a file that is named 12345. I would like to have a hyperlink from the log number in one file to the external file with the same name. The log number is the variable. Any ideas on how to accomplish this automatically?

    Thank you in advance for your help

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Assuming the list is in Column A and the documents are stored in My Documents, would this do your job?

    [codebox]Sub carango()
    Dim strFileName As String
    Dim i As Integer
    Dim x As Integer

    i = ActiveSheet.UsedRange.Rows.Count
    Range("A1").Select

    For x = 1 To i

    strFileName = ActiveCell.Value
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    "C:\Documents and Settings\carango\My Documents\" & strFileName & ".xls"
    ActiveCell.Offset(1, 0).Select
    Next x

    End Sub
    [/codebox]
    Jerry

  3. #3
    Lounger
    Join Date
    Feb 2001
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your prompt response.

    Yes, that is exactly what I am looking for - however - I continue to get the excel error that indicates "Cannot Open the Specified File".

    I'm not able to get around that yet.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Have you made sure that you replaced

    "C:\Documents and Settings\carango\My Documents\"

    in Jezza's code with the correct path to your log files? The path must end in a backslash.

    Also check that the extension is correct - ".xls" is for Excel 97-2003 workbooks, ".xlsx" is for Excel 2007 workbooks.

  5. #5
    Lounger
    Join Date
    Feb 2001
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Got it - sorry, my data started on A3 instead of A1 and I needed to allow for that. All set now. Thank you very much for your help!

  6. #6
    Lounger
    Join Date
    Feb 2001
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Also, Just thought I would add the code that I used. What I learned was that once you set a hyperlink, you cannot update it without removing it first

    Private Sub workbook_open()
    Call hyperlink_Remove
    Call hyperlink_Add
    End Sub


    Sub hyperlink_Add()
    Dim strFileName As String
    Dim i As Integer
    Dim x As Integer
    i = ActiveSheet.UsedRange.Rows.Count - 2
    Range("A3").Select
    For x = 1 To i
    strFileName = ActiveCell.Value
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    "C:\Documents and Settings\carango\desktop\" & strFileName & ".xls"
    ActiveCell.Offset(1, 0).Select
    Next x
    End Sub


    Sub hyperlink_Remove()

    Dim i As Integer
    Dim x As Integer
    i = ActiveSheet.UsedRange.Rows.Count - 2
    Range("A3").Select
    For x = 1 To i

    ActiveSheet.Hyperlinks.Delete
    ActiveCell.Offset(1, 0).Select
    Next x
    Range("A3").Select
    End Sub


    thanks again!

Posting Permissions

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