Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlink amend thru code (2000)

    I have a table, with one of the columns contains a hyperlink type.

    ie: The text may well say "Document about Chocolate"

    and the hyperlink set for that may well be "R:confectionarychoclateschoccie.doc"

    What i need to do is change the start bit thru code. ie, as above i need to change it to "S:folder2otherchoociefolderchoccie.doc"

    I can manage the string conversion ok.. but im not sure how to handle the hyperlink property in vba. the help file doesn't give any examples.
    This is my code thus far. (the field is called Document Link)

    It bombs out when setting hyper.

    <font color=blue>Private Sub hyperbutton_Click()
    ' Change all the Hyperlinks

    Dim strsql As String, rst As Recordset
    Dim Myloop As Integer, pos As Integer, hyper As String

    strsql = "SELECT * FROM Documents"
    Set rst = CurrentDb.OpenRecordset(strsql)

    Do While Not (rst.EOF)

    hyper = (rst![Document Link].HyperllinkAddress)

    Stop

    Loop

    End Sub</font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink amend thru code (2000)

    Do all links refer to a file in R:ConfectionaryChoclates and do they all have to be modified to refer to S:Folder2OtherChoccieFolder, or can this vary?

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink amend thru code (2000)

    Hans,

    They all refer to the same folder.

    Although for some reason some of them use that thingamie jip way of doing a url .. ie:
    one way is to have <font color=blue>R:/folder/folder/file.doc </font color=blue> and the other can be <font color=blue>//scsdf/folder/folder/folder.file.doc</font color=blue>

    I forget what its called, but both still work.

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink amend thru code (2000)

    Steve,

    Here is amended code to amend hyperlinks through code. Substitute the appropriate names. I have assumed that the hyperlink paths contain backslashes . If they can also contain forward slashes, you'll have to add extra instructions to take that into account, and if there are relative paths of the form ..Chocolates, you'll have to add yet more instructions.

    BTW, a path of the form serverfolderfolder is called a UNC path (for Uniform Naming Convention.)

    Sub HyperButton_Click()
    ' Change all the Hyperlinks
    Dim strOld1 As String
    Dim strOld2 As String
    Dim strNew As String
    Dim strHyperlink As String
    Dim rst As DAO.Recordset

    ' Adapt these paths!
    strOld1 = "R:ConfectionaryChocolates"
    strOld2 = "CocoaConfectionaryChocolates"
    strNew = "S:SweetsDeathByChocolate"

    Set rst = CurrentDb.OpenRecordset("Documents")

    ' Loop through records
    Do While Not rst.EOF
    ' Start edit mode
    rst.Edit
    ' Change path
    strHyperlink = Replace(rst![Document Link], strOld1, strNew)
    rst![Document Link] = Replace(strHyperlink, strOld2, strNew)
    ' Save record
    rst.Update
    ' Move to next record, otherwise you'll loop forever
    rst.MoveNext
    Loop

    ' Close recordset and clean up
    rst.Close
    Set rst = Nothing
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink amend thru code (2000)

    Cheers for the code Hans.

    I just knew you would know that was a Uniform Naming Convention ! .. but thingamajig is easier to remember (especially for my poor brain) <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink amend thru code (2000)

    "" Currently owing 13 beers to other helpful forum members (especially Hans!) ""

    Nice !!

    I would have to remortgage my house to repay the amount of beers I owe. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink amend thru code (2000)

    Don't worry, there is a limit to the number of beers I can drink
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    ...
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    <img src=/S/barf.gif border=0 alt=barf width=64 height=23>

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink amend thru code (2000)

    Hans, (or anyone!)...

    There seem to be problems doing it this way. it does seem to work. but i suspect that the nature of the hyperlinks, and some nulls are throwing it astray.. (nulls do generate an error message, although it does seem to loop thru all records)

    Is there a way that i can just assign the hyperlink to a variable. ?

    <font color=blue>HyperLinkString = rst![Document Link].Hyperlinkaddress</font color=blue>
    <font color=448800>' muck about with the string.. what i will actually do, is count back from the end of the string to the first "/" or "", and take the name of the file as everything to the right of that.

    'and then Set the hyperlink in code.</font color=448800>
    <font color=blue>rst![Document Link].Hyperlinkaddress = HyperLinkString</font color=blue>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink amend thru code (2000)

    Steve,

    The problem is that you can't assign a Null value to a string variable. You can test for Null values before doing that: (additions to my original code in bold):

    ' Loop through records
    Do While Not rst.EOF
    If Not IsNull(rst![Document Link]) Then
    ' Start edit mode
    rst.Edit
    ' Change path
    strHyperlink = Replace(rst![Document Link], strOld1, strNew)
    rst![Document Link] = Replace(strHyperlink, strOld2, strNew)
    ' Save record
    rst.Update
    End If
    ' Move to next record, otherwise you'll loop forever
    rst.MoveNext
    Loop
    rst.Close

    If you want to much some more with it, here is some "technical" information about how a hyperlink string is composed. A hyperlink string is of the form

    Text_to_display#Hyperlink_address#Subaddress

    The first part is the text that is actually shown. This may be empty; in that case the actual address is displayed.
    The second part is required; this is the actual hyperlink address.
    The third part is an optional subaddress; this can be a bookmark in a Word document, or a range within an Excel workbook, or a slide in a Powerpoint presentation. It may be empty.

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink amend thru code (2000)

    I guess thats a 'no' then! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Changed it Hans, and a lot more are now sorted, however, i can't seem to get it to do it for hyperlinks that are like this

    ../folder/folder/file.doc

    It still baffling me how those two lines of code work, i understand its replacing one with another, but the first replace contributes to the second replace.. (very confusing! <img src=/S/clever.gif border=0 alt=clever width=15 height=15> )


    these hyperlinks are 'set' from with another access program (that i didnt't have any involvement in), incidentally, is there an option box (or perhaps a windows option) for how hyperlinks are set, ???
    ie:
    type 1: R:folderfolderfile.doc
    type 2 : //SCFSDSDS/folder/folder/file.doc
    type 3 : ../folder/folder/file.doc (first bit it like the dos command, 2 dots)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink amend thru code (2000)

    In Office 97, there was an option in the Insert Hyperlink dialog to store a hyperlink as an absolute path or as a relative path (with ..). In Office 2002, this option is not available any more. I don't know about Office 2000.

    Try this - I fear that the code is becoming rather convoluted, but that can't be helped if you need to take all these variants into consideration. This is not the complete procedure, only the central part!

    Dim intPos1 As Integer
    Dim intPos2 As Integer
    Dim intPos3 As Integer

    Dim strFilePath As String
    Dim strFileName As String
    Dim strNewPath As String

    strNewPath = "serverfolderfolder" ' replace by your new path, including trailing

    strHyperlink = rst![Document Link]

    intPos1 = InStr(strHyperlink, "#") ' position of first #
    intPos2 = InStr(intPos1 + 1, strHyperlink, "#") ' position of second #

    strFilePath = Mid(strHyperlink, intPos1 + 1, intPos2 - intPos1 - 1) ' Actual file name and path

    intPos3 = InStrRev(strFilePath, "") ' Position of last
    If intPos3 = 0 Then ' If not present, look for /
    intPos3 = InStrRev(strFilePath, "/") ' Position of last /
    End If

    strFileName = Mid(strFilePath, intPos3 + 1) ' file name only

    ' Get part up to first #, then new path, then file name, then part after second #
    strHyperlink = Left(strHyperlink, intPos1) & strNewPath & strFileName & Mid(strHyperlink, intPos2)

    rst![Document Link] = strHyperlink

Posting Permissions

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