I originally created a VBA macro that would uses IE automation to click specific form buttons on an ASPX page. The page has 5 form options you have to pass through.
- drop down list for group
- drop down list for location
- Graphical calendar where you select the date
- List box for area's
- Generate Report button
I would like to move away from using the IE automation to something more efficient. I have seen other posts where they were able to pull the data back using the MSXML object. This is what I was able to build from what I read but am not having any luck figuring out how to pass more than one of the form options. This is a company specific/internal website so unfortunately it is not available externally for me to be able to post the link example.
The element ID's are as follows; dlDivision, dlLocation, calRptDate, lbAreas, btnGenReport.
Public Sub XMLhttp_Search_Extract()
Dim URL As String
Dim XMLreq As Object
Dim POSTdata As String
Dim i As Integer
URL = "somewebURL.test.aspx"
POSTdata = "(" & Q("dlDivision") & ":" & Q("divisionNAMEHERE") & "," & Q("dlLocation") & ":" & Q("123 - Location") & ")"
Set XMLreq = CreateObject("MSXML2.XMLHTTP")
With XMLreq
.Open "POST", URL, False
.setRequestHeader "User-Agent", "Moilla/5.0 (Windows NT 5.1; rv:23.0) Gecko/20100101 Firefox/23.0"
.setRequestHeader "Referer", "somewebURL.test.aspx"
.setRequestHeader "Content-Type", "application/json; charset=utf-8"
.Send (POSTdata)
For i = 1 To Len(.responseText) Step 1023
MsgBox Mid(.responseText, i, i + 1023), _
Title:=i & " to " & Min(i + 1023 - 1, Len(.responseText)) & " of " & Len(.responseText)
Next i
End With
End Sub
Private Function Q(text As String) As String
Q = Chr(34) & text & Chr(34)
End Function
Private Function Min(n1 As Long, n2 As Long) As Long
Min = IIf(n1 < n2, n1, n2)
End Function
