Using XMLHTTP in VBA

helentoomik · July 16th, 2003, 01:00 PM

I'm trying to capture data from a web form to an Excel spreadsheet. An Excel web query didn't work so, following a suggestion, I am now trying to do it using VBA and XMLHTTP. Never used XMLHTTP before and I'm not even sure whether this is the right forum for this question...

This is the url to the form itself: http://www.bmreports.com/servlet/com....bwp_PanBmData

This is an example url including form inputs, that works if I submit it manually: http://www.bmreports.com/servlet/com...1=i_isg-spow1& param5=2003-06-14& param6=
(Sorry, long url - you might have to copy-paste)

This is my first attempt at VBA code:
------------------------------------------------
Sub XMLTest()
    Dim XMLhttp: Set XMLhttp = CreateObject("msxml.xmlhttp")
    Dim Result
    Dim ReqString: ReqString = "?param1=i_isg-spow1&param5=2003-06-14&param6=*"
    XMLhttp.Open "POST", "http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBmData", False
    XMLhttp.setrequestheader "Content-Type", "application/x-www-form-urlencoded"
    XMLhttp.send ReqString
    Result = XMLhttp.responsetext
    MsgBox Result
End Sub
------------------------------------------------

The result I get back is a "400 Bad Request" error - "Your browser sent a request that this server could not understand".
I tried removing the leading question mark from the ReqString - doesn't make any difference.
Any ideas?

joefawcett · July 17th, 2003, 04:05 AM

You are sending it to the wrong URL. If you look at the form the URL is "http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBMDataServlet". So this code should work. Try to install the latest version of MsXml Core services (version 4), if not install version 3 and make sure you have that referenced in you Excel project.
You may need to change the version number in the code below from 4 to 3.

Code:

Sub XMLTest()
    Dim XMLhttp: Set XMLhttp = New Msxml2.XmlHttp40 
    Dim Result
    Dim ReqString: ReqString = "?param1=i_isg-spow1&param5=2003-06-14&param6=*"
    XMLhttp.Open "POST", "http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBMDataServlet", False
    XMLhttp.setrequestheader "Content-Type", "application/x-www-form-urlencoded"
    XMLhttp.send ReqString
    Result = XMLhttp.responsetext
    MsgBox Result
End Sub

(That's going to give a very large MsgBox!)
I have tested this with a JavaScript file which is below. If you put this file in to c:\scripts as postData.js then navigate to c:\scripts from a command window and type:
CScript //NoLogo postData.js > data.htm
assuming success you can open the file data.htm in IE.

Code:

function postData(Url, Data)
{
  var oHttpReq = new ActiveXObject("Msxml2.XMLHTTP.4.0");
  oHttpReq.open("POST", Url, false);
  oHttpReq.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
  oHttpReq.send(Data);
  return oHttpReq;
}

function main()
{
  var sUrl = "http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBMDataServlet";
  var sData = "param1=i_isg-spow1&param5=2003-06-14&param6=*";
  var oResponse = postData(sUrl, sData);
  if (oResponse.status == 200)
  {
    WScript.echo(oResponse.responseText);    
  }
  else
  {
    WScript.echo("Failed to retrieve data because:\n" + oResponse.statusText + " (" + oResponse.status + ")");     
  }
  oHttpReq = null;
}

main();

--

Joe

helentoomik · July 17th, 2003, 04:22 AM

Hi Joe
I should have guessed you'd be the one to answer again... Thank you so much!
I hadn't spotted the change from lowercase to uppercase M in the url, so when I changed from PanBmData to PanBmDataServlet, I got an error message.
With the new url (and after removing the leading question mark from the ReqString), this works perfectly. Now I can start on the real task (get the data from the tables into Excel).

joefawcett · July 17th, 2003, 04:31 AM

It would be much easier if they provided the data in xml via a web service, then parsing would be easier. If you need help let me know.

--

Joe