VBA code for searching for a keyword and re-directing to the specific page?

[dannyboy198]

Hi all,

First, Happy New Year to all :)

I am stuck with this problem for my project so I am writing to seek some help regarding this matter.

Basically, I am having a form with a search function. Each field of the record returned from the search (using keyword) will be displayed, including an attachment field (hyperlink data type). I want it to work in a way that every time a user double-click on the hyperlink to open the attachment, the same search keyword used above will be used to search inside the attachment, and point directly to that specific page containing the keyword instead of the first page as usual.

I have been able to store and open the attachment so I need help with the searching and page-pointing part.

I look forward to getting any inputs from you. Any help is greatly appreciated. Thank you so much.

[Mihail]

Just an idea:

Say that you open the attachment using Excel.
Doing that you lose control from the Access application.
So you need to manage the search task from Excel.

In order to do that you must save (from Access) somewhere the keyword (maybe in a new file, or table or even in clipboard), then open the attachment, then instruct Excel to read the keyword and perform the task).

I say Excel as an example but can be any other application if you know how to develop programs for that application.

Good luck !

[dannyboy198]

Thanks Mihail,

My idea was to search for the keyword inside the document to get the particular page number, and then open the hyperlink with pointing to that particular page mentioned above.

Putting aside the part of searching first, can anyone please help me with the code for moving to a specific page in a WORD document upon opening ? I am new to VBA so still struggle to get this done.

Thanks a lot

Currently I use this code for opening the hyperlink

Function GetUserAddress() As Boolean
    Dim strInput As String
 
    On Error GoTo Error_GetUserAddress
    strInput = InputBox("Enter a valid address")
    'In fact the address will be passed from another form. This is just for testing purpose only
    Application.FollowHyperlink strInput, , True
    GetUserAddress = True
 
Exit_GetUserAddress:
    Exit Function
 
Error_GetUserAddress:
    MsgBox Err & ": " & Err.Description
    GetUserAddress = False
    Resume Exit_GetUserAddress
End Function
 
Private Sub Cmd_Click()
If GetUserAddress = True Then
        MsgBox "Successfully followed hyperlink."
    Else
        MsgBox "Could not follow hyperlink."
    End If
 
End Sub

[Mihail]

This macro do the job (Word 2007)

Sub Macro1()
    Selection.HomeKey Unit:=wdStory
 
Dim PageNumber As Long, i As Long
    PageNumber = InputBox("Page number ?")
    i = 1
    For i = 1 To PageNumber - 1
        Application.Browser.Next
    Next i
End Sub

[dannyboy198]

Thanks so much for your prompt response Mihail. Works like charms.

Hope you don't mind if I ask you one more thing:

I have managed to do the Find function using Macro in the Word document. The only thing that is left with is passing the keyword from Access to the Word document. The path of the document is stored as hyperlink in Access, which opens upon user clicking it. Is there a way to pass the parameter from Access to Word similar to that between forms in Access ?

Once again, thank you so much for your help. I really appreciate that.

-D

[Mihail]

From Access put the keyword in the clippboard.
In Word I think that must exist an OPEN event for documents. So use this event to store data from clippboard into a variable.
Sorry but I never do a program for Word so I can't say "how to".

Another approach is to write the keyword into a file (from Access) then read it from Word and use it.
I think I can help you to do this if you can not do yourself. Let me know.

[dannyboy198]

Hi Mihail,

Thanks for your input. I will try it out and get back to you asap.

-D

[dannyboy198]

I have found a way to do this. Turned out to be quite simple:

Dim oApp As Object
Set oApp = CreateObject(Class:="Word.Application")
        oApp.Visible = True
        'Open the Document
        oApp.Documents.Open Filename:="Filename"
        'Use Find object from Selection object to look for the keyword
        With oApp.Selection.Find
            .Forward = True
            .Wrap = wdFindStop
            .Text = "keyword"
            .Execute
        End With

This way the control still stays with Access so we do not have to find a way to pass the keyword to Word as my previous approach. Hope it helps anyone who is facing the same problem.

Thanks Mihail for your prompt response.

Cheers,

-D

[Mihail]

Good for you !