emoat
10-04-2012, 10:22 AM
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).
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
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
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).
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
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