Results 1 to 4 of 4
  1. #1
    Ron is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    5

    VBA Exporting to Word & Adding Hyperlinks


    I have a database that successfully exports data to a word document and formats areas that are enclosed by predefined markers. For example words that are enclosed by <bold>I want this text bold<bold/> turns the text bold and then markers are deleted.
    I now have to do something similar for hyperlinks but cannot work out how to do it. Essentially I have text in fields in my database for example www.webpage.com which I want to turn into links once they are in word using vba. I was thinking of tagging them as I did for the formatting but I can't seem to get it to work. Any assistance would be appreciated.

    I have tried using hyperlinks.add method but I can't work it out. I can either tag the hyperlinks like I have done for the areas i have formatted (eg <link1>www.webpage.com<link1/> or just search for the actual text "www.webpage.com" in the document. I can't seem to select the the areas like I have for the formatting and implement the hyperlinks.add method.

    Any assistance is greatly appreciated.

    This is what I currently successfully use for formatting. I know it is long and ugly but i am a hack and it works so I'm happy.

    Code:
    Private Sub Export_Click()
    
    
    
    
    Dim objWord As Word.Application
    Dim doc As Word.Document
    Dim myrange As Range
    Dim CCTitle As String
    Dim CCText As String
    
    
    
    
    CCTitle = Forms!frm_StandardConCheckEdit!ConditionTitle
    CCText = Forms!frm_StandardConCheckEdit!ConditionText
    
    
    
    
    'Open Word
    Set objWord = CreateObject("Word.Application")
    
    
    With objWord
        .Visible = True
        
          Set doc = .Documents.Add
        doc.SaveAs CurrentProject.Path & "\TestDoc.doc"
    End With
      
    'Title/IntroPage
    
    
    With objWord.Selection
    
    
    .Font.Name = "Arial"
    .Font.Size = 10
    .Font.Bold = True
    .Font.Italic = False
        .TypeText Text:=CCTitle
       .TypeParagraph
        .TypeParagraph
    
    
    .Font.Bold = False
       .TypeText Text:=CCText
       
    End With 
    
    
    
    
    'INDENT
    
    
    'Format Word Document
    With objWord
    
    
    'Move selection to start of document
    .Selection.HomeKey wdStory
    
    
    'To ensure that formatting isn't included as criteria in a find or replace operation, use this method before carrying out the operation
    .Selection.Find.ClearFormatting
    
    
    End With
    
    
    'Find <indent> set range at <indent/>
    With objWord.Selection.Find
    
    
    'expression .Execute(FindText, MatchCase, MatchWholeWord, MatchWildcards, MatchSoundsLike, MatchAllWordForms, Forward, Wrap, Format, ReplaceWith, Replace, MatchKashida, MatchDiacritics, MatchAlefHamza, MatchControl)
    Do While .Execute(FindText:="<indent>", Forward:=True, MatchWildcards:=False, Wrap:=wdFindStop, MatchCase:=False) = True
    Set myrange = objWord.Selection.Range 'Setting property of range
    myrange.End = objWord.ActiveDocument.Range.End 'Set Range to rest of Document
    'Instr Returns an integer specifying the start position of the first occurrence of one string within another.
    myrange.End = myrange.Start + InStr(myrange, "<indent/>")
    myrange.Select
    
    
    'with range make formatting changes
    
    
        With objWord.Selection.ParagraphFormat
    .SpaceBeforeAuto = False
    .SpaceAfterAuto = False
    .LeftIndent = objWord.CentimetersToPoints(2)
    .FirstLineIndent = objWord.CentimetersToPoints(-1)
        End With
    objWord.Selection.MoveRight Unit:=wdCharacter, Count:=1
    
    
    'Loop to next
    
    
    Loop
    End With
    
    
    'BOLD
    
    
    'Restart at beggining
    
    
    With objWord
    .Selection.HomeKey wdStory
    .Selection.Find.ClearFormatting
    
    
    End With
    
    
    'Define Range
    
    
    With objWord.Selection.Find
    Do While .Execute(FindText:="<bold>", Forward:=True, MatchWildcards:=False, Wrap:=wdFindStop, MatchCase:=False) = True
    Set myrange = objWord.Selection.Range
    myrange.End = objWord.ActiveDocument.Range.End
    myrange.End = myrange.Start + InStr(myrange, "<bold/>")
    myrange.Select
    
    
    'format
    
    
        With objWord.Selection.Font
    .Bold = True
        End With
    
    
    objWord.Selection.MoveRight Unit:=wdCharacter, Count:=1
    
    
    'Loop to next
    Loop
    End With
    
    
    'ITALICS
    
    
    With objWord
    .Selection.HomeKey wdStory
    .Selection.Find.ClearFormatting
    
    
    End With
    
    
    'Define Range
    
    
    With objWord.Selection.Find
    Do While .Execute(FindText:="<italics>", Forward:=True, MatchWildcards:=False, Wrap:=wdFindStop, MatchCase:=False) = True
    Set myrange = objWord.Selection.Range
    myrange.End = objWord.ActiveDocument.Range.End
    myrange.End = myrange.Start + InStr(myrange, "<italics/>")
    myrange.Select
    
    
    'format
    
    
        With objWord.Selection.Font
    .Italic = True
        End With
    
    
    
    
    objWord.Selection.MoveRight Unit:=wdCharacter, Count:=1
    
    
    'Loop to next
    Loop
    End With
    
    
    'Delete formating symbols
    
    
    objWord.Selection.HomeKey wdStory
    objWord.Selection.WholeStory
    With objWord.Selection.Find
        .ClearFormatting
        .Text = "<indent>"
        .Replacement.ClearFormatting
        .Replacement.Text = ""
        .Execute Replace:=wdReplaceAll, Forward:=False, _
            Wrap:=wdFindContinue
    End With
    
    
    objWord.Selection.HomeKey wdStory
    objWord.Selection.WholeStory
     With objWord.Selection.Find
        .ClearFormatting
        .Text = "<indent/>"
        .Replacement.ClearFormatting
        .Replacement.Text = ""
        .Execute Replace:=wdReplaceAll, Forward:=True, _
            Wrap:=wdFindContinue
    End With
    
    
    objWord.Selection.HomeKey wdStory
    objWord.Selection.WholeStory
    With objWord.Selection.Find
        .ClearFormatting
        .Text = "<bold>"
        .Replacement.ClearFormatting
        .Replacement.Text = ""
        .Execute Replace:=wdReplaceAll, Forward:=True, _
            Wrap:=wdFindContinue
    End With
    
    
    objWord.Selection.HomeKey wdStory
    objWord.Selection.WholeStory
     With objWord.Selection.Find
        .ClearFormatting
        .Text = "<bold/>"
        .Replacement.ClearFormatting
        .Replacement.Text = ""
        .Execute Replace:=wdReplaceAll, Forward:=True, _
            Wrap:=wdFindContinue
    End With
    
    
    objWord.Selection.HomeKey wdStory
    objWord.Selection.WholeStory
    With objWord.Selection.Find
        .ClearFormatting
        .Text = "<italics>"
        .Replacement.ClearFormatting
        .Replacement.Text = ""
        .Execute Replace:=wdReplaceAll, Forward:=True, _
            Wrap:=wdFindContinue
    End With
    
    
    objWord.Selection.HomeKey wdStory
    objWord.Selection.WholeStory
     With objWord.Selection.Find
        .ClearFormatting
        .Text = "<italics/>"
        .Replacement.ClearFormatting
        .Replacement.Text = ""
        .Execute Replace:=wdReplaceAll, Forward:=True, _
            Wrap:=wdFindContinue
    End With
    
    
    objWord.Selection.HomeKey wdStory
    objWord.Selection.WholeStory
     With objWord.Selection.Find
        .ClearFormatting
        .Text = "<tab>"
        .Replacement.ClearFormatting
        .Replacement.Text = vbTab
        .Execute Replace:=wdReplaceAll, Forward:=True, _
            Wrap:=wdFindContinue
    End With
    
    
    objWord.Selection.HomeKey wdStory
    
    
       Set objWord = Nothing
    
    
    
    
    End Sub

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You don't show how you export the text from Access to Word. Have you tried to store the link (www.webpage.com) in Access (using the rich text property of the form's textbox control that displays the big text to be exported)?
    Here is some info that might help you with the original question:
    https://stackoverflow.com/questions/...k-in-word-2010

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Ron is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    5
    Gicu,

    Thanks for your assistance the link helped me work through the problem. I have some ugly but working code now . Yuupeeee. Thankyou. It looks like this and seems to get rid of the tags at the same time.

    Code:
    With objWord.Selection.FindDo While .Execute(FindText:="<z1>", Forward:=True, MatchWildcards:=False, Wrap:=wdFindStop, MatchCase:=False) = True
    Set myrange = objWord.Selection.Range
    myrange.End = objWord.ActiveDocument.Range.End
    myrange.End = myrange.Start + InStr(myrange, "<z1/>") + 4
    myrange.Select
    
    
    objWord.ActiveDocument.Hyperlinks.Add Anchor:=myrange, _
    Address:="https://www.georgesriver.nsw.gov.au/Home", _
    SubAddress:="", ScreenTip:="", TextToDisplay:="www.georgesriver.nsw.gov.au"
    
    
    objWord.Selection.MoveRight Unit:=wdCharacter, Count:=1
    
    
    Loop
    End With

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear, good luck with your project!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 18
    Last Post: 12-18-2017, 07:32 PM
  2. Adding Hyperlinks to a Listbox
    By Tectu in forum Forms
    Replies: 2
    Last Post: 08-08-2015, 05:58 PM
  3. Replies: 13
    Last Post: 07-27-2014, 12:12 AM
  4. Replies: 3
    Last Post: 01-25-2014, 03:40 PM
  5. Adding Hyperlinks to Linked Tables
    By rwilso29 in forum Access
    Replies: 1
    Last Post: 07-02-2013, 09:08 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums