Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
download .csv file indirectly from website - long
I'm trying to create a spreadsheet that when I click a button it wil
download the .csv file directly to my harddrive then I can use anothe routine to open it after it's downloaded(or better yet open/downloa directly) into another sheet in the workbook. My problem is I'm no sure if, for the given site, I need to learn xml or javascript. I'v seen many references to xml on the forums but the site appears to us java script. Here is a piece of code I was able to make work by reading the post here and a book called "Excel 2000 Power Programming" <code ---------------------------------- Function GetDataButton() Dim DataHere As Range Dim msn As QueryTable Dim address As String Set DataHere = Worksheets("Sheet1").Range("$F1") address = "URL;http://tinyurl.com/2r7l6" Sheets("Sheet1").Select Set msn = ActiveSheet.QueryTables.Add(Connection:=address Destination:=DataHere) msn.Refresh End Function ---------------------------------- </code Please note this code DOES NOT do what I want(and the ticker symbol i hardcoded into it) but it does link to the page where I then have th option to save it. My problem is I can't see for the life of me a url to the .csv file Here's a snip: <code ---------------------------- <SCRIPT LANGUAGE=javascript <!--// document.QlistCtl.Symbol.focus();document.QlistCtl .Symbol.select(); //-- </SCRIPT </TR<TR<TD VALIGN=TOP<TABLE WIDTH=100% BORDER=0 CELLSPACING= CELLPADDING=0<TR<TD VALIGN=TOP <TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 ALIGN=LEFT WIDTH=400 <FORM NAME=Charts ACTION=chartdl.asp METHOD=GET <TR <TD ALIGN=RIGHT WIDTH=307 <INPUT TYPE=IMAGE name="FileDownLoadBt" SRC="images/downlbut.gif border=0 WIDTH=90 HEIGHT=20 </TD <TD WIDTH=8<SPACER TYPE=BLOCK WIDTH=8</TD <TD ALIGN=RIGHT WIDTH=90 <INPUT TYPE=IMAGE name="ShowChartBt" SRC="images/showchrt.gif border=0 WIDTH=90 HEIGHT=20 <INPUT TYPE=HIDDEN NAME="Symbol" VALUE="mrk" </TD </TR </FORM </TABLE <BR CLEAR=ALL <BMerck & Co Inc</B <BR CLEAR=ALL <!-- Begin stock History table -- -------------------------------- </code Is this line - document.QlistCtl.Symbol.focus();document.QlistCtl .Symbol.select(); telling the page/browser what to do if either the download file butto or show chart button is pressed? If it is then it appears I'm going t need to get a grasp of javascript to be able to see what it's doing Once I know that, is that where the XML stuff comes in? Basicall trying to send the page/server/whatever info as if it came from th webpage and the page "responds" accordingly? I glossed over a book a the store on xml but I didn't see any refernce to working within vb code but I could have easily missed it. It's been 6-7 years since I've messed with html(a passing interest). never bothered to learn vbscript or javascript. I've been using exce for a few months to run numbers on stocks and basically cut/paste th info I want but that's getting old. I've written some simple subs an functions up to this point is all. I just started this project Tuesda so I've still got alot more learning to do but that's where the fu is. Thanks for any input, Bra -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
download .csv file indirectly from website - long
A simpler approach might be to do a web query:
Sub AAA_URL_Get_Query() Dim sStr As String sStr = "URL;http://moneycentral.msn.com/investor/charts/" & _ "chartdl.asp?Symbol=mrk&DateRangeForm=1&PT=5&C P=1" & _ "&C5=1&C6=1974&C7=1&C8=2004&C9=2&ComparisonsForm=1 " & _ "&CE=0&CompSyms=&DisplayForm=1&D0=1&D5=0&D7=&D6=&D 3=0" & _ "&ShowTablBt=Show+Tablec" With ActiveSheet.QueryTables.Add(Connection:= _ sStr, _ Destination:=Range("a1")) .BackgroundQuery = False .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub This brings down the whole page, but you could add code to find the table and copy it to a new worksheet and delete the old worksheet. You could also make the symbol value a variable when creating the string. -- Regards, Tom Ogilvy "not2brite " wrote in message ... I'm trying to create a spreadsheet that when I click a button it will download the .csv file directly to my harddrive then I can use another routine to open it after it's downloaded(or better yet open/download directly) into another sheet in the workbook. My problem is I'm not sure if, for the given site, I need to learn xml or javascript. I've seen many references to xml on the forums but the site appears to use java script. Here is a piece of code I was able to make work by reading the posts here and a book called "Excel 2000 Power Programming" <code ---------------------------------- Function GetDataButton() Dim DataHere As Range Dim msn As QueryTable Dim address As String Set DataHere = Worksheets("Sheet1").Range("$F1") address = "URL;http://tinyurl.com/2r7l6" Sheets("Sheet1").Select Set msn = ActiveSheet.QueryTables.Add(Connection:=address, Destination:=DataHere) msn.Refresh End Function ---------------------------------- </code Please note this code DOES NOT do what I want(and the ticker symbol is hardcoded into it) but it does link to the page where I then have the option to save it. My problem is I can't see for the life of me a url to the .csv file. Here's a snip: <code ---------------------------- <SCRIPT LANGUAGE=javascript <!--// document.QlistCtl.Symbol.focus();document.QlistCtl .Symbol.select(); //-- </SCRIPT </TR<TR<TD VALIGN=TOP<TABLE WIDTH=100% BORDER=0 CELLSPACING=0 CELLPADDING=0<TR<TD VALIGN=TOP <TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 ALIGN=LEFT WIDTH=400 <FORM NAME=Charts ACTION=chartdl.asp METHOD=GET <TR <TD ALIGN=RIGHT WIDTH=307 <INPUT TYPE=IMAGE name="FileDownLoadBt" SRC="images/downlbut.gif" border=0 WIDTH=90 HEIGHT=20 </TD <TD WIDTH=8<SPACER TYPE=BLOCK WIDTH=8</TD <TD ALIGN=RIGHT WIDTH=90 <INPUT TYPE=IMAGE name="ShowChartBt" SRC="images/showchrt.gif" border=0 WIDTH=90 HEIGHT=20 <INPUT TYPE=HIDDEN NAME="Symbol" VALUE="mrk" </TD </TR </FORM </TABLE <BR CLEAR=ALL <BMerck & Co Inc</B <BR CLEAR=ALL <!-- Begin stock History table -- -------------------------------- </code Is this line - document.QlistCtl.Symbol.focus();document.QlistCtl .Symbol.select(); telling the page/browser what to do if either the download file button or show chart button is pressed? If it is then it appears I'm going to need to get a grasp of javascript to be able to see what it's doing. Once I know that, is that where the XML stuff comes in? Basically trying to send the page/server/whatever info as if it came from the webpage and the page "responds" accordingly? I glossed over a book at the store on xml but I didn't see any refernce to working within vba code but I could have easily missed it. It's been 6-7 years since I've messed with html(a passing interest). I never bothered to learn vbscript or javascript. I've been using excel for a few months to run numbers on stocks and basically cut/paste the info I want but that's getting old. I've written some simple subs and functions up to this point is all. I just started this project Tuesday so I've still got alot more learning to do but that's where the fun is. Thanks for any input, Brad --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
download .csv file indirectly from website - long
This will just download the .csv file :) http://data.moneycentral.msn.com/scripts/chrtsrv.dll? Symbol=mrk&FileDownload=& keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Tom Ogilvy" wrote: A simpler approach might be to do a web query: Sub AAA_URL_Get_Query() Dim sStr As String sStr = "URL;http://moneycentral.msn.com/investor/charts/" & _ "chartdl.asp?Symbol=mrk&DateRangeForm=1&PT=5&C P=1" & _ "&C5=1&C6=1974&C7=1&C8=2004&C9=2&ComparisonsForm=1 " & _ "&CE=0&CompSyms=&DisplayForm=1&D0=1&D5=0&D7=&D6=&D 3=0" & _ "&ShowTablBt=Show+Tablec" With ActiveSheet.QueryTables.Add(Connection:= _ sStr, _ Destination:=Range("a1")) .BackgroundQuery = False .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub This brings down the whole page, but you could add code to find the table and copy it to a new worksheet and delete the old worksheet. You could also make the symbol value a variable when creating the string. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
download .csv file indirectly from website - long
That is certainly simpler, but doesn't seem to return the same data. Do you
have a source where you got it that perhaps show any arguments that may be passed? -- Regards, Tom Ogilvy "keepITcool" wrote in message ... This will just download the .csv file :) http://data.moneycentral.msn.com/scripts/chrtsrv.dll? Symbol=mrk&FileDownload=& keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Tom Ogilvy" wrote: A simpler approach might be to do a web query: Sub AAA_URL_Get_Query() Dim sStr As String sStr = "URL;http://moneycentral.msn.com/investor/charts/" & _ "chartdl.asp?Symbol=mrk&DateRangeForm=1&PT=5&C P=1" & _ "&C5=1&C6=1974&C7=1&C8=2004&C9=2&ComparisonsForm=1 " & _ "&CE=0&CompSyms=&DisplayForm=1&D0=1&D5=0&D7=&D6=&D 3=0" & _ "&ShowTablBt=Show+Tablec" With ActiveSheet.QueryTables.Add(Connection:= _ sStr, _ Destination:=Range("a1")) .BackgroundQuery = False .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub This brings down the whole page, but you could add code to find the table and copy it to a new worksheet and delete the old worksheet. You could also make the symbol value a variable when creating the string. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
download .csv file indirectly from website - long
Hi Tom..
it's exactly the link produced when you click on the download button.. (at least it's what i copied from my IE history after i downloaded the file) since OP was interested in the csv... i did a quick check, appears csv data is not linked to the onscreen table... csv is ALWAYS last 12 months' stats keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Tom Ogilvy" wrote: That is certainly simpler, but doesn't seem to return the same data. Do you have a source where you got it that perhaps show any arguments that may be passed? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
download .csv file indirectly from website - long
Bum deal, oh well.
The data I want is monthly prices(high,low,close,volume), that ur seems to get daily for the past year. If this is the only way then that's fine. It works and will do what need. A dumb question, so that I can run this routine multiple times withou having to save and re-open each time(not all companys have same tabl lengths),since it needs a clean slate each time for my other routine to run properly(they find the end of the table). Could I "create" th sheet it downloads to and have it delete the sheet after everythin else runs? This way I start with a clean slate each time(I'm hoping t keep all the data on one one sheet and link to it as needed if tha makes sense. From looking at all the things you can do(and Tom's comments) I'm sur I can do it but figured I'd double check. The nice thing is if it' done right(create new sheet, find table in sheet, etc), even if the change the way the page is formatted I hopefully won't have to re-writ my code. Tom, May I ask why you suggested changing the code? Isn't my code a we query as well just written differently? Or would my code be considere a hyperlink of some type? Thanks for such quick replies, Bra -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
download .csv file indirectly from website - long
OK wait I'm confused. How can the .csv only be past 12 months when
can hit the download file button from my link http://moneycentral.msn.com/investor...lBt=Show+Table and it lets me save the data I want as a .csv file? Or do you mean from the /scripts directory it's only linked to the pas 12 months. Thanks, Bra -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
download .csv file indirectly from website - long
Here's this url
URL;http://tinyurl.com/35kfk It seems to get the data I want but when I just paste it into m code(didn't try Tom's) it uses only one column to paste all the data That will work, just needs to be parsed. It's getting closer since it' the data I need and it's only the table without all the rest of th webpage crap. Now I need to figure out what the other parameters/arguments in the ur are. I know C5 & C7 are the months. After work I'll figure the rest ou that way hopefully I'll be able to enter what I want in the spreadshee just as if I went to the site and did it. Now for a really dumb question, why when I run my code multiple time does it shift the previous data right? If I hit my "GetDataButton" times(wait until it runs before rehitting) I end up with four column of data startings at F1. Shouldn't it just paste over top of it since specified a range? Thanks again, Bra -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I download analysis toolpak from the microsoft website? | Excel Discussion (Misc queries) | |||
Download excel doc from website, can this be done? | Excel Worksheet Functions | |||
want to download csv file from website and need to open in Excel. | Excel Discussion (Misc queries) | |||
Download data from website | Excel Programming | |||
Need vb to automatically download from https website | Excel Programming |