XmlHttp Post in Excel VBA not updating website for

问题:

I routinely have to search the state of NV for unclaimed property and put the results in an Excel spreadsheet. I am trying to automate the process but I'm limited to using Excel 2010 and VBA. Below is the URL to the site I'm trying to submit a form using xmlhttp.

URL: https://nevadatreasurer.gov/UPSearch/

I created a class to automate submitting forms on other websites but no matter what I enter in the postdata the form is never submitted. Below is my submission, and method to submit the form.

Call to class:

cXML.openWebsite "Post", "https://nevadatreasurer.gov/UPSearch/Index.aspx", _
                 "ctl04$txtOwner=" & strSearchName

Class method:

Public Sub openWebsite(strOpenMethod As String, strURL As String, _
Optional strPostData As String)

pXmlHttp.Open strOpenMethod, strURL


If strPostData <> "" Then
    strPostData = convertSpaceToPlus(strPostData)
    pXmlHttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    pXmlHttp.send (strPostData)
Else
    pXmlHttp.send
End If

'Create DOM html documnet
pHtmlObj.body.innerHTML = pXmlHttp.responseText

End Sub

Each time the responseText is the main website with no updates, as if I submitted no postdata. I'm fairly new to IE automation but can someone provide a reason why this isn't working and a code example that works?

Thanks!

Update: 7/26/13 8:30am PST

Without any changes to my method I was able to submit forms through another website. The state of OR unclaimed property form. It worked perfect!

URL: https://oregonup.us/upweb/up/UP_search.asp

However I ran into the same problem when I tried the state of CA unclaimed property website. No matter what I do, the responseText is always the original search page with no update.

URL: https://scoweb.sco.ca.gov/UCP/Default.aspx

It still does not work with the state of NV on my original post. I am using the proper post data, URL encoded for each website and can see no difference. Any help would be appreciated.

回答1:

Try below code

Public Sub openWebsite(strOpenMethod As String, strURL As String, Optional strPostData As String)

    Dim pXmlHttp As Object
    Set pXmlHttp = CreateObject("MSXML2.XMLHTTP")
    pXmlHttp.Open strOpenMethod, strURL, False
    pXmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    pXmlHttp.send (strPostData)


    Dim pHtmlObj As Object
    Set pHtmlObj = CreateObject("htmlfile")
    pHtmlObj.body.innerHTML = pXmlHttp.ResponseText
    MsgBox pXmlHttp.ResponseText

End Sub

Sub test()
    Dim btnSearch As String, strSearchType As String, strSearchName As String, PostData As String
    btnSearch = "Search"
    strSearchType = "Owner"
    strSearchName = "Santosh"
    PostData = "ctl04%24txtOwner=" & strSearchName & "&ctl04%24btnSearch=" & btnSearch & "&ctl04%24rblSearchType=" & strSearchType
    openWebsite "POST", "https://nevadatreasurer.gov/UPSearch/Index.aspx", PostData
End Sub

Post Data view using Firebug

URL encode

ResponeText

回答2:

You should urlencode the characters in this string:

"ctl04$txtOwner=" & strSearchName

Ways to do this are discussed here: SO link, as VBA doesn't have a built-in function for this.

The dollar sign needs to be replaced with %24 and any spaces with %20. If these are the only non-alphanumeric characters in the string they you could take a simple approach, using VBA.Replace() (twice). You are currently replacing spaces with '+' which will usually work, but the dollar-sign may be an issue.