VBA question from a novice 1

[greatscott90]

I'd like to write a form in Access that could do one of two things:
1. Control either my Mozilla or IE webbrowser to refresh the page, search the page for certain words, then notify me.
or
2. Control an activex webbrowser within an Access form to refresh the page, search the page for certain words, then notify me.

I tried to put an activex webbrowser within a Access form then use the:
[control].navigate command, however I get an error message stating "error: Expected: string constant" I think I'm using the wrong references, but I have no idea.

Thanks.

 

[rmikesmith]

I created a form in Access then added a Microsoft Web Browser control. In the form's Load event handler I invoked the Navigate method and that worked for me. Example:

Private Sub Form_Load()
   Me.WB.Navigate "[URL unfurl="true"]http://www.google.com/"[/URL]
End Sub

where WB is the web browser control's Name property.

When I switch from Design to View for the form, Google loads in the web browser control.

HTH,
Mike

 

[greatscott90]

Thanks for the advice - however I get the following error message when I try that:
The expression On Load you entered as the event property setting produced the following error: Expected: string constant.

Am I using the wrong references?

Thank you.

 

[rmikesmith]

I have to say I haven't used Access much in a while, but try this:

Go to the On Load entry (Form Property sheet), click next to [Event Procedure]. Click the elipsis button that appears to the right. This should open the VBA editor and generate the event handler shell. Call the Navigate method using the following format: WebBrowserControlName.Navigate Url
where WebBrowserControlName is either the default Name property or whatever you may have changed it to and Url is a string containing the web address you are trying to access. Again, look at my example code above. You don't strictly need the Me reference (a reference to the parent form).

 

[greatscott90]

That is what I tried. However, in the VBA editor after I type in the name of the webbrowser I entered and hit the period key I am not given a choice of "navigate." IE:
webbrowser.(choices now available)
I can do webbrowser.about -- webbrowser.application -- et cetera, but navigate is not a choice.
I was wondering if I'm using the wrong references from the Tools menue. IE. Tools -> References
Microsoft ADO
Microsoft DAO
Microsoft ActiveX.
I've tried many combinations of all these and all the ActiveX Data Object Libraries.

Thanks for you help.

 

[rmikesmith]

Yes, I noticed that as well, but it still worked properly. In Tools|References, Microsoft Internet Controls is checked.

Can you post your actual On Load event procedure code?


Regards,
Mike

 

[greatscott90]

I named my activex webbrowser: wb

Private Sub Form_Load()
wb.Navigate "

http://www.google.com/"

End Sub

I tried:

Private Sub wb_GotFocus()
Me.Navigate "

http://www.google.com"

End Sub

and I got the same error.

 

[rmikesmith]

I'm stumped. Are you getting the same error as in your original post ("error: Expected: string constant")? When does this occur, when you switch from Design to View mode?


Mike

 

[greatscott90]

Same error - and I get it when I switch from design view to view mode.

 

[rmikesmith]

I'm trying to track down documentation on that error...

In the meantime, to establish that there isn't something else behind-the-scenes going on, try

1. Create a new database
2. Add a single form
3. Add a webbrowser control to the form
4. Add the wb.navigate "

http://www.google.com/"

command to the form's On_Load event handler

see if this bare-bones implementation works for you.

Mike

 

[rmikesmith]

Is there an error number associated with the message?


Mike

 

[TomCologne]

You might want to try an alternative without Web Browser ctl, maybe that works for you:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' References:
''
'' Microsoft Internet Controls (\system32\shdocvw.dll)
'' Microsoft Windows Common Controls 6.0(SP6) (\system32\MSCOMCTL.OCX)
''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Form_Load()
    On Error GoTo Err_Form_Load

    Dim objIE As SHDocVw.InternetExplorer
    Dim strNavigate As String

    Set objIE = New InternetExplorer
       
        strNavigate = "[URL unfurl="true"]http://www.google.com/"[/URL]
    
        objIE.Navigate (strNavigate)
    
            '' Hide IE while page loads

            Do Until objIE.ReadyState = 4   '' READYSTATE_COMPLETE

                objIE.Visible = False

            Loop

                objIE.Visible = True
        
                

Exit_Form_Load:

    Set objIE = Nothing
    
    Exit Sub
    
Err_Form_Load:

    MsgBox Err.Description
    Resume Exit_Form_Load
    
    
End Sub

TomCologne

 

[greatscott90]

Thanks Mike & Tom - both ideas worked.
Another query though...
Once a page has been loaded is there a way to (within Access):
1. Search the page for specific words on the page
or
2. Edit controls on the page, or dropdown boxes, fill in blanks, or emulate a mouse click on a control?

Thanks.

 

[rmikesmith]

The following refreshes the webpage displayed in the webbrowser control at a specified interval then inspects the page text for a list of keywords. I modified the form containing the webbrowser to include a listbox, which displays the keywords that were found. Requires that a reference be set to the Microsoft HTML Object Library. Also, I was surprised to learn that Access forms have built-in Timer support, which simplified things.

There may be other ways to do this but I know almost nothing about the HTML DOM; I couldn't find a method or funciton that would do the searching for me.

Here is the code behind the form:

Option Compare Database
Option Explicit


Dim strUrl As Integer
Dim FoundList() As String

Private Sub Form_Load()
   With WB
     .Navigate "[URL unfurl="true"]http://www.tek-tips.com/"[/URL]
   End With
   Me.TimerInterval = PAGE_REFRESH_INTERVAL
End Sub


Private Sub Form_Timer()
   With WB
     .Refresh
     Do
       DoEvents
     Loop Until .ReadyState = READYSTATE_COMPLETE
   End With
   SearchWebpageForKeywords
End Sub


Private Sub SearchWebpageForKeywords()
Dim Doc As HTMLDocument
Dim PageText As String
Dim i As Integer

   Set Doc = WB.Document
   PageText = Doc.Body.innerText
   
   ClearListBox lstKeywordsFound
   With lstKeywordsFound
     If FindKeywords(PageText, KEYWORDS, FoundList) Then
       For i = LBound(FoundList) To UBound(FoundList)
         .AddItem FoundList(i)
       Next i
     Else
       .AddItem "No Keywords found"
     End If
   End With
   Set Doc = Nothing
End Sub


Private Sub ClearListBox(ByRef LB_Name As ListBox)
Dim i As Long

   With LB_Name
     For i = 0 To .ListCount - 1
       .RemoveItem 0
     Next i
   End With
End Sub

The following code I placed into a standard module:

Option Compare Database
Option Explicit


Public Const KEYWORDS As String = "Tek-Tips,tipmaster"
Public Const PAGE_REFRESH_INTERVAL As Long = 10000 '(milliseconds; = 10 sec)

Function FindKeywords(ByVal strInput As String, ByVal strKeywords As String, ByRef arrWordsFound() As String) As Boolean
Dim arrKeywords As Variant
Dim i As Long
Dim Pos As Long
Dim FoundCount As Long

   arrKeywords = Split(KEYWORDS, ",", , vbTextCompare)
   
   Erase arrWordsFound
   FoundCount = 0
   For i = LBound(arrKeywords) To UBound(arrKeywords)
     Pos = InStr(1, strInput, arrKeywords(i), vbTextCompare)
     If Pos > 0 Then
       FoundCount = FoundCount + 1
       ReDim Preserve arrWordsFound(1 To FoundCount)
       arrWordsFound(FoundCount) = arrKeywords(i)
     End If
   Next i

   If FoundCount > 0 Then
     FindKeywords = True
   Else
     FindKeywords = False
   End If
End Function

The keyword list is supplied as a string constant and is parsed within the FindKeywords function each time it is called. From an efficiency standpoint, this could be separated out into a separate procedure and executed just once when the form loads. The list of keywords could also be read in from a file or from some other source (e.g. database table).

I also suggest searching these forums for "webbrowser control" which returns many interesting threads, including topics similar to your second query above.

Regards,
Mike

 

[greatscott90]

I'm following as well as my dim brain can. However - for the line:
.additem
I get the following error:
Compile error:
Method or data member not found

I don't really need to see a list of the found words, just send up a msgbox to alert the user.

Thanks for all your help.

 

[greatscott90]

Scratch that last post - I think I figured out how to search without using the list box.

Thanks for all your help.