web query from tim

[Tim]

Hi All,

In a workbook i’ve got this query which takes some data from msn. The
address of the query is:

http://moneycentral.msn.com/investo...1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol=au:wpl

At the moment the query is hard coded and fixed to the symbol AU:WPL .

What i am trying to do is to make the query dynamic and instead for AU:WPL
to look for the variable symbol in Sheet6 CellA1 in the same workbook which
is VCR for example or any other symbol.
Does anybody know how to do this?

Tim

 

[Don Guillett]

Right click the web table>export to excel>record a macro or use this one
that takes the symbol from cell a1.
Send me an email and I can send a workbook with this done.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/21/2007 by Donald B. Guillett
'

'
With Selection.Querytables
.Connection = _
"URL;http://moneycentral.msn.com/investo...1&CFQ=1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol="
& Range("a1")
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Tim]

Hi Don,

Your answer doesn’t seem to work.
When i run your macro from your answer or in the sample workbook you sent to
me i receive the next error report:
“Run-time error '1004':
Application-defined or object-defined error “

I’m running Excel2003 version with macros enabled option.

Here is exactly what happened when i followed your instructions:
1. Right clicked on a table in the msn’s web> export to excel. This step works
2. typed au:vcr in cellG1 and in Sheet6 Range A1
3. then go to View>toolbars>external data>edit web query>changed the address
of the query to:
"URL;http://moneycentral.msn.com/investo...1&CFQ=1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol="
& Range("g1")

Or

"URL;http://moneycentral.msn.com/investo...=1&CFQ=1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol" & Sheets("Sheet6").Range("A1")

And when i click the Go button next to the query’s address instead to get
data for the symbol au:vcr i receive this message in the "Edit Web Query"
window:
“Type a company name or ticker symbol above and click Go.â€
Checked it many times and can’t see any error in the query address but still
can’t go to the right address.

Tim

 

[Tim]

Ignore my previous post.
Your answer WORKS!
The macro needed a small change to select the query table first and after
that works great.
Thanks for the help!!!

Tim

 

[Don Guillett]

It could have been cleaned up to not require a selection
with activesheet.Querytable(1)
or modified to run from another sheet or even another wb