Wrox Programmer Forums
Go Back   Wrox Programmer Forums > XML > XML
| Search | Today's Posts | Mark Forums Read
XML General XML discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XML section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 16th, 2003, 01:00 PM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using XMLHTTP in VBA - Error 400

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?
  #2 (permalink)  
Old July 17th, 2003, 04:05 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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
  #3 (permalink)  
Old July 17th, 2003, 04:22 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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).
  #4 (permalink)  
Old July 17th, 2003, 04:31 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reloop XMLHTTP.SEND on Error kevorkian Classic ASP Professional 2 December 6th, 2008 03:15 PM
What Is Error 400? RollingWoodFarm Excel VBA 3 January 2nd, 2007 03:07 PM
error 400 davidspeare Beginning VB 6 1 October 1st, 2005 02:40 PM
Reg :400 Error in Web Services sriram6781 XML 1 August 8th, 2005 07:59 PM
XMLHTTP v3 Automation Error jinyen VB Components 0 May 12th, 2004 03:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.