About Us

Brian

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Update database from ODBC query (or another method)	Richard Edwards	Excel Worksheet Functions	4	June 24th 08 11:47 AM
Please post this thread a correct full method, method about	Nast Runsome	New Users to Excel	8	February 25th 08 03:29 PM
Please post this thread a complete correct method, method about te	Nast Runsome	New Users to Excel	0	February 23rd 08 09:42 PM
How do you post a query in excel?	Justin Kline	Excel Worksheet Functions	1	February 11th 07 04:41 PM
Previous Post - Correct Syntax Query	Clarence Crow	Excel Worksheet Functions	0	December 7th 04 05:35 AM

Menu

About Us