0

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
3
  • Sure you're sending JSON? Usually you send application/x-www-form-urlencoded form data and you receive JSON . Try the WinHttpRequest and show the HTML-Code of the site. Commented Jul 11, 2018 at 0:33
  • See this --> stackoverflow.com/a/16851758/2227085 Commented Jul 11, 2018 at 4:34
  • There appears to be a typo here: "Moilla". Should be "Mozilla". On that basis I would check the rest of that header as well. Commented Jul 11, 2018 at 5:22

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.