web query from tim
 

Hi All,

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

http://moneycentral.msn.com/investor...&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

web query from tim
 

Right click the web tableexport to excelrecord 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/investor/research/sreport.asp?CR=1&AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1 &ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&I TP=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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tim" wrote in message
...
Hi All,

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

http://moneycentral.msn.com/investor...&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

the answer doesn't seem to work
 

Hi Don,

Your answer doesnt 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 €œ

Im 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 msns web export to excel. This step works
2. typed au:vcr in cellG1 and in Sheet6 Range A1
3. then go to Viewtoolbarsexternal dataedit web querychanged the address
of the query to:
"URL;http://moneycentral.msn.com/investor/research/sreport.asp?CR=1&AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1 &ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&I TP=1&Type=Equity&Symbol="
& Range("g1")

Or

"URL;http://moneycentral.msn.com/investor/research/sreport.asp?CR=1&AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1 &ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&I TP=1&Type=Equity&Symbol" & Sheets("Sheet6").Range("A1")

And when i click the Go button next to the querys 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 cant see any error in the query address but still
cant go to the right address.

Tim




"Don Guillett" wrote:

Right click the web tableexport to excelrecord 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/investor/research/sreport.asp?CR=1&AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1 &ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&I TP=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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tim" wrote in message
...
Hi All,

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

http://moneycentral.msn.com/investor...&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

the answer doesn't seem to work
 

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

the answer doesn't seem to work
 

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



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tim" wrote in message
...
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