By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,500 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

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

P: 6
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.
Jan 3 '12 #1
Share this Question
Share on Google+
8 Replies


100+
P: 759
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 !
Jan 3 '12 #2

P: 6
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

Expand|Select|Wrap|Line Numbers
  1. Function GetUserAddress() As Boolean
  2.     Dim strInput As String
  3.  
  4.     On Error GoTo Error_GetUserAddress
  5.     strInput = InputBox("Enter a valid address")
  6.     'In fact the address will be passed from another form. This is just for testing purpose only
  7.     Application.FollowHyperlink strInput, , True
  8.     GetUserAddress = True
  9.  
  10. Exit_GetUserAddress:
  11.     Exit Function
  12.  
  13. Error_GetUserAddress:
  14.     MsgBox Err & ": " & Err.Description
  15.     GetUserAddress = False
  16.     Resume Exit_GetUserAddress
  17. End Function
  18.  
  19. Private Sub Cmd_Click()
  20. If GetUserAddress = True Then
  21.         MsgBox "Successfully followed hyperlink."
  22.     Else
  23.         MsgBox "Could not follow hyperlink."
  24.     End If
  25.  
  26. End Sub
Jan 4 '12 #3

100+
P: 759
This macro do the job (Word 2007)
Expand|Select|Wrap|Line Numbers
  1. Sub Macro1()
  2.     Selection.HomeKey Unit:=wdStory
  3.  
  4. Dim PageNumber As Long, i As Long
  5.     PageNumber = InputBox("Page number ?")
  6.     i = 1
  7.     For i = 1 To PageNumber - 1
  8.         Application.Browser.Next
  9.     Next i
  10. End Sub
Jan 4 '12 #4

P: 6
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
Jan 5 '12 #5

100+
P: 759
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.
Jan 5 '12 #6

P: 6
Hi Mihail,

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

-D
Jan 6 '12 #7

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

Expand|Select|Wrap|Line Numbers
  1. Dim oApp As Object
  2. Set oApp = CreateObject(Class:="Word.Application")
  3.         oApp.Visible = True
  4.         'Open the Document
  5.         oApp.Documents.Open Filename:="Filename"
  6.         'Use Find object from Selection object to look for the keyword
  7.         With oApp.Selection.Find
  8.             .Forward = True
  9.             .Wrap = wdFindStop
  10.             .Text = "keyword"
  11.             .Execute
  12.         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
Jan 9 '12 #8

100+
P: 759
Good for you !
Jan 9 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.