Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save data retreived from query without saving query | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |