Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Location
    Iowa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlinks and Export (Access 2002)

    We use a database to help us keep track of some .pdf documents on our web site and the many pages that link to them. The number of links is too numerous to mention! But I'd like to be able to generate some of the pages from Access instead of having to manually update numerous pages.

    Anyway, there is a field for the internet URL. Obviously, within Access everything hyperlinks perfectly. If I save my query as html, the hyperlinks still work, but the format of the page is funky for our purposes and the html code leaves much to be desired when trying to re-format it.

    I can do a mail-merge with Word and format the data closer to what we want, but the hyperlinks don't work. They just display as plain text and having to go back and create the hyperlinks defeats the purpose.

    I've been searching archives and other websites and I suspect that it can't be done, but I have to ask anyway. Is it possible to export hyperlinks from Access and have them stay hyperlinks rather than text, other than just saving a query as html?

    Thanks in advance, even if the answer is no!

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

    Re: Hyperlinks and Export (Access 2002)

    You could use Automation to export the hyperlinks to Word in code. To do this, you must set a reference to the Microsoft Word 10.0 Object Library (in the Visual Basic Editor, using Tools | References...). The code also uses ADO, so you must have a reference to the Microsoft ActiveX Data Objects 2.n Library, but this is standard in Access 2002.

    The following is not meant as a finished product, but just to give you an idea; you can add bells and whistles to suit your needs.

    Sub CreateHyperlinkDoc()
    Dim objWord As New Word.Application
    Dim objDoc As Word.Document
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strHyperlink As String
    Dim intPos1 As Integer
    Dim intPos2 As Integer

    On Error GoTo ErrHandler

    Set objDoc = objWord.Documents.Add

    Set cnn = CurrentProject.Connection
    rst.Open "tblHyp", cnn, adOpenForwardOnly

    Do While Not rst.EOF
    strHyperlink = rst!Hyp
    intPos1 = InStr(strHyperlink, "#")
    intPos2 = InStrRev(strHyperlink, "#")
    strHyperlink = Mid(strHyperlink, intPos1 + 1, intPos2 - intPos1 - 1)
    objDoc.Hyperlinks.Add Anchor:=objWord.Selection.Range, Address:=strHyperlink
    objWord.Selection.TypeParagraph
    rst.MoveNext
    Loop

    objWord.Visible = True
    objWord.Activate

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Set objDoc = Nothing
    Set objWord = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    If Not objDoc Is Nothing Then
    objDoc.Close SaveChanges:=wdDoNotSaveChanges
    End If
    If Not objWord Is Nothing Then
    objWord.Quit
    End If
    Resume ExitHandler
    End Sub

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Location
    Iowa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks and Export (Access 2002)

    Thanks so much Hans! I'll give it a try.

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

    Re: Hyperlinks and Export (Access 2002)

    Or much easier: copy and paste the records, or the hyperlink column, into a Word document or Excel worksheet, and format the result the way you like. Hyperlinks are preserved when copying and pasting records into Word or Excel.

Posting Permissions

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