Solved: VBA Interaction with IE

[emoat]

Hi all,
I have used Excel/VBA many times in the past to automate processes, but I've never attempted any kind of web interaction, so this is very new to me (also, I'm not a programmer by trade).
I've done a lot of searching over the past week and made some baby-steps in educating myself about VBA interaction with Internet Explorer, but I think I need some specific help to get me over the next hurdle.

I am using IE 8 and Excel 2007 (VBA 6.5).
All that I really want to do is get the current value of an input box and then send a new value. Following other forum posts and guides this was no problem on a simple website like Google, but I've not been able to get VBA to identify the applicable parts of our ordering web page.
Unfortunately the webpage I'm dealing with (an ordering system) is on our local intranet, so I can't provide a link. However I am attaching a screen cap of the interface as well as a screen cap of the IE Developer Tool breakdown of the relevant part of the html (in zip file to accommodate file size limits). I'd like to get at the input field called "RQH_ACCT_UNIT", both reading the current value and entering a new value.

I've tried modifying code from several other people's examples/forum posts/etc, but seem to be unable to access the interior nested "div"s or any other interior section. My most recent attempt is below (hopefully formatted this correctly - never tried to post code before). This uncovers some of the div tags shown in the attachments but for a reason I don't understand can't see the deeper divs (like "contentArea", "profile", "Accounting", etc.). The goal is the input fields, but I figured those certainly won't work if I can't even see the Divs several indents back from the inputs (see attached image).

[vba]
Sub NewTest()
Dim objShell As SHDocVw.ShellWindows
Dim oIE As WebBrowser
Dim numDIVtags, i As Integer
Set objShell = New SHDocVw.ShellWindows
Set oIE = Nothing
For Each objwin In objShell
NameVar = objwin.FullName
If UCase(objwin.FullName) = UCase("C:\Program Files\Internet Explorer\iexplore.exe") Then
NameVar2 = objwin.LocationURL
If Left(UCase(objwin.LocationURL), Len("web address here")) = UCase("web address here") Then
Set oIE = objwin
End If
End If
Next objwin
If TypeName(oIE) = "Nothing" Then
MsgBox "Can't find target window"
Exit Sub
End If

numDIVtags = oIE.document.all.tags("DIV").Length

For i = 0 To numDIVtags

NameVar = oIE.document.all.tags("DIV")(i).className
If oIE.document.all.tags("DIV")(i).className = "contentArea" Then

Debug.Print "There is a result on the " & i & "th div tag"

'get current value
'Send new value

End If
Next
End Sub
[/vba]


Sorry for the long post and my thanks even if all you've done is read this entire rant. If I can see how to access this one field, I'm fairly certain I can adapt it to the rest of the page and other fields I need to modify. Any suggestions would be greatly appreciated.
Thanks again in advance,
Patrick

[Crocus Crow]

It's easier for people to help you if you post HTML as text between [HTML ] [/HTML ] tags rather than as JPEG images, and then we can copy and paste excerpts of the HTML if necessary.

The id="RQH_ACCT_UNIT" input element is inside an iframe (id="WORK"), so to get this element:

1. Navigate to main page.
2. Get the iframe element with ID="WORK", and extract its src attribute (which is a hyperlink relative to the intranet domain).
3. Navigate to the intranet domain & src attribute string.
4. Get id="RQH_ACCT_UNIT" input element from the resulting HTMLdocument.

Try the following code which does all 4 steps. It compiles successfully, but is obviously untested. The code uses early binding of InternetExplorer and HTML objects so you must set references to Microsoft Internet Controls and Microsoft HTML Object Library in your VBA project.
[vba]Public Sub Set_Acct_Input()

Dim baseURL As String
Dim IE As InternetExplorer
Dim HTMLdoc As HTMLDocument
Dim workFrame As HTMLIFrame
Dim acctInput As HTMLInputElement

baseURL = "http://your.intranet.com"

Set IE = New InternetExplorer

With IE
.Visible = True

'Navigate to the main page

.Navigate baseURL & "/sub1/sub2/yourwebpage.htm"
While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend

'Get the WORK iframe and navigate to it

Set workFrame = .Document.getElementById("WORK")
.Navigate baseURL & workFrame.src
While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend

Set HTMLdoc = .Document
End With

Set acctInput = HTMLdoc.getElementById("RQH_ACCT_UNIT")
acctInput.Focus 'might be needed
acctInput.Value = "12345"

End Sub[/vba]

[emoat]

Thanks for the input Crocus Crow!
Sorry about the HTML jpg. I will try to be better about that in the future (though I was trying to copy from the IE developer tool just now and it would not let me copy multiple lines from the HTML tab layout - and the Edit layout looks like quite the mess).

You've helped me make a big step forward in that I was able to get the value of the field!
A small step backward as the ".Navigate baseURL & workFrame.src" step seemed to break the IE window (went blank white).

Is there a way to focus the program on the workFrame.src or reference the workFrame without actually navigating the browser to that part of the page?

[Crocus Crow]

Is there a way to focus the program on the workFrame.src or reference the workFrame without actually navigating the browser to that part of the page?

After a bit more investigation, yes it is. Here is my complete code with the modified line in bold:
[vba]Public Sub Set_Acct_Input()

Dim baseURL As String
Dim IE As InternetExplorer
Dim HTMLdoc As HTMLDocument
Dim workFrame As HTMLIFrame
Dim acctInput As HTMLInputElement

baseURL = "http://your.intranet.com"

Set IE = New InternetExplorer

With IE
.Visible = True

'Navigate to the main page

.Navigate baseURL & "/sub1/sub2/yourwebpage.htm"
While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend

'Get the WORK iframe document

Set workFrame = .Document.getElementById("WORK")
Set HTMLdoc = workFrame.contentWindow.Document
End With

Set acctInput = HTMLdoc.getElementById("RQH_ACCT_UNIT")
acctInput.Focus 'might be needed
acctInput.Value = "12345"

End Sub
[/vba]

[emoat]

SUCCESS!!!
That worked perfectly. Thanks so much for your quick response Crocus Crow. This breakthrough will keep me busy for a while.
Thanks again!!