I am having difficulities getting an excel web query to work. I am
very, very new to this, so apologies if I say anything stupid...
The website I am trying to import data from publishes commodity metal
prices, and is post method. I can't seem to get the PostText I am
writing in the web query to work with the website. Part of the
problem may be that the URL where the site user selects what data they
want to look at (e.g., metal, date, units, currency) is different from
the URL that returns the data. The former is
http://www.metalprices.com/subscript...DB_search.asp; the
latter is
http://www.metalprices.com/subscript...DB_display.asp.
Below is the web query I've written:
Sub AluminumPriceFetch()
Dim ScratchSheet As Worksheet
Dim ConnectURL As String
Dim PostStr As String
Set ScratchSheet = Sheets("aluminum data")
ConnectURL = "URL;http://www.metalprices.com/subscription/xml/xmlDB_display.asp"
PostStr = "metal=Al" _
& "date=7/21/2003" _
& "unit=mt" _
& "currency=usd" _
& "submit=view"
On Error Resume Next
With ScratchSheet.QueryTables.Add(Connection:=ConnectUR L, _
Destination:=Range("C3"))
..PostText = PostStr
..WebDisableRedirections = False
..BackgroundQuery = True
..TablesOnlyFromHTML = False
..Refresh BackgroundQuery:=False
..SaveData = True
End With
End Sub
It isn't working; the result is like a default page you receive if you
haven't selected search criteria. The website is paid subscription
based, so I can't post my password and username. However, you can get
a free 2-day trial subscription (which has no automatic roll-over, nor
does it require any credit card info) if you are really interested in
helping :). Thanks in advance for any help you can offer!
Brian
Below is the source code from the "xmlDB_search.asp" URL:
..searchtable {
</style
<script language="JavaScript"
function ValidateForm() {
var metal;
metal = document.forms.view.metal.value;
if (metal == "") {alert("Please choose a metal");return false;}else
{return true;}}
</script
</head
href="javascript
:history.back()"Back</a</td
<td align="center"<a href="/registered/index.asp"<img border="0"
src="/images/flyingbear/flyingbear_120-bevT.gif"</a</td
<td align="right" valign="bottom"<a
href="/registered/index.asp"Home</a</td
</tr
</table
<br
<b<font face="Arial" color="#000080" size="5"Metalprices.com Feed
Archive</font</b
<form action="xmlDB_display.asp" method="POST" id="view" name="view"
<table class="searchtable" cellpadding="3" cellspacing="0"
<tr
<td align="left"
<span style="COLOR:000080;FONT-WEIGHT: bolder"Choose a: </span
<SELECT NAME="metal"
<OPTION value=""Metal
<OPTION value="Al" LME Aluminum
<OPTION value="Al Alloy LME" Al Alloy LME
<OPTION value="Al Alloy-NASAAC LME" LME Aluminum
Alloy US
<OPTION value="Cu" LME Copper
<OPTION value="Pb LME" LME Lead
<OPTION value="Ni LME" LME Nickel
<OPTION value="Sn LME" LME Tin
<OPTION value="Zn LME" LME Zinc
</SELECT
</td
<td align="left"
<span style="COLOR:000080;FONT-WEIGHT: bolder"For Date *: </span
<input type="text" name="date" size="8" maxlength="10"
value="7/21/2003"
</td
</tr
<tr
<td align="left"
<select name="unit"
<option value="lb" Pounds (LB)</option
<option value="kg" Kilogram (KG)</option
<option value="mt" Metric Tonne (MT)</option
</select
/
<select name="currency"
<option value="usd" US Dollar</option
<option value="eur" Euro</option
<option value="gbp" Sterling</option
<option value="jpy" Yen</option
</select
</td
<td align="center"
<input type="Submit" name="submit" value="View"
onClick="return(ValidateForm());"
input type="Reset" name="reset" value="Reset"
onClick="document.location='/subscription/xml/xmlDB_search.asp'"
</td
</tr
</table
</form
Below is the source code from the "xmlDB_display.asp" URL:
<html
<head
<titleMetalprices.com LME Prices</title
<link rel="stylesheet" type="text/css" href="xsl/scheme6.css"
</head
<body bgcolor="#9EABDF"
<div align="center"
<table width="674" border="0" cellspacing="0" cellpadding="0"
<tr
<td width="46" height="45" align="center"
<a href="javascript
:history.back()"Back</a<br
<a href="xmlDB_search.asp"Search</a<br
<a href="/registered/index.asp"Home</a
</td
<td width="593" align="center"
<span style="FONT-SIZE: medium;COLOR: 000080;FONT-WEIGHT:
bolder"
Feed data for Monday, July 21, 2003
</span
</td
</tr
<tr
<td width="46" </td
<td width="593" align="center"
<form action="xmlDB_display.asp" method="POST" id="view" name="view"
<input type="hidden" name="metal" value="Al"
<input type="hidden" name="date" value="7/21/2003"
<select name="unit"
<option value="lb" Pounds (LB)</option
<option value="kg" Kilogram (KG)</option
<option value="mt" selectedMetric Tonne (MT)</option
</select
/
<select name="currency"
<option value="usd" selectedUS Dollar</option
<option value="eur" Euro</option
<option value="gbp" Sterling</option
<option value="jpy" Yen</option
</select
<input type="Submit" name="submit" value="View"
</form
</td
</tr
</table
</div
<DIV ALIGN="center" <a href="javascript
:history.back()"</a
<Span Style='Font-Family:Arial;color:Navy;font-size:xx-small'</Span<?xml
version="1.0" encoding="UTF-16"?<table border="0" cellpadding="2"
cellspacing="0" xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:user="urn:my-functions:xslt"<tr class="qOddRow"<td colspan="9"
align="center" class="qTitle"LME Aluminum - US$/MT</td</tr<tr
class="qEvenRow"<td colspan="9" align="center" class="qFootNote"
and then the data follows - a sample:
</td<td class="qFieldChgNone" align="right" 1396