kbay808
asked on
How to convert existing vba code from Access to work in Excel?
We have an access DB with a form that displays the target website. The below code is used to click the refresh button for the website from within Access. I need to be able to use it in excel for an existing IE window (https://servman/sm/index.do).
Private Sub Command10_Click()
' Click the Refresh Button
Dim HTML As HTMLDocument
Dim btn As HTMLButtonElement
Dim blnRefreshFound As Boolean
Set HTML = Me.WebBrowser0.Object.Document
blnRefreshFound = False
For Each btn In HTML.getElementsByTagName("button")
If btn.qtip = "Refresh" And btn.ID <> "ext-gen28" Then
btn.Click
blnRefreshFound = True
Exit For
End If
Next btn
If Not blnRefreshFound Then
MsgBox ("No refresh button found")
End If
End Sub
to do that I believe you need to launch the webpage from within Excel VBA , so that you get the window handle. otherwise no way of knowing which is the desired browser window/tab.
Private Sub Command10_Click()
' Click the Refresh Button
Dim HTML As HTMLDocument
Dim btn As HTMLButtonElement
Dim blnRefreshFound As Boolean
'Set HTML = Me.WebBrowser0.Object.Document
Dim myWebPage As String
myWebPage = "http://www.behindthename.com/random/"
Set HTML = OpenWebDoc(myWebPage)
blnRefreshFound = False
For Each btn In HTML.getElementsByTagName("button")
If btn.qtip = "Refresh" And btn.ID <> "ext-gen28" Then
btn.Click
blnRefreshFound = True
Exit For
End If
Next btn
If Not blnRefreshFound Then
MsgBox ("No refresh button found")
End If
End Sub
Function OpenWebDoc(targetpage As String) As Object
'Requires reference to "Microsoft Internet Controls"'
'Dim IE As SHDocVw.InternetExplorer
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
'Navigate to the URL'
IE.Navigate (targetpage)
'No need to show the window'
IE.Visible = False
'Wait for IE to load the page'
While IE.Busy: DoEvents: Wend
Do While IE.ReadyState <> 4: DoEvents: Loop
Set OpenWebDoc = IE.Document
End Function
Here's code to get a reference to an open IE window.
Sub GetIE_LateBinding()
Dim IE As Object
With CreateObject("Shell.Application").Windows
If .Count > 0 Then
' Get IE
Set IE = .Item(0) ' or .Item(.Count - 1)
Else
' Create IE
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
End If
IE.Navigate "http://suSub GetIE_LateBinding()
Dim IE As Object
With CreateObject("Shell.Application").Windows
If .Count > 0 Then
' Get IE
Set IE = .Item(0) ' or .Item(.Count - 1)
Else
' Create IE
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
End If
' do your stuff with IE here
End With
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It worked perfect. Thanks
You will need to add a reference to "Microsoft Internet Controls"
The WebBrowser object can be found by right clicking in the forms toolbox and selecting Additional Controls > Microsoft We Browser
After this place a break on the line
Open in new window
Then and add a watch on the "btn" object so that you can determine how to test for the appropiate button (The watch will enable to look at all available options under the btn object). I cannot help here with out access to the siteI have attached a document which has started this process using Google
Book1.xlsm