Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Querying from excel.
I often update an excel model with a web query every morning. I am
looking for a way to automate this procedure using VBA. I simply copy a an array of data into an excel range. Effectively I have to: 1) go to the website 2) use the websites drop down menus to pick html or CSV file 3) use the websites drop down menues to pick the report that I want 4) use drop down boxes to pick the date range that I want 5) press OK and the site will either download the data in a html table or it will create a CSV file and prompt me with a windows File Download prompt which gives me the option of opening it or saving it. 6) then I simply highlight the arrray of data I want and paste into a range that never changes in an excel spreadsheet. For simplicity I always use the same start date and only ever change the end date. The result is an array that gets bigger every day as new data gets updated. I simply paste this growing array over the previous days data in the excel spread sheet. Is it possible to automate this in an Excel spreadsheet using VBA? Which option would be easier html table or CSV file? Thanks TS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Querying from excel.
Hey Don.
The website is http://www.aeso.ca/. 1) Goto Reports at the top of the page 2) On the left side of the page there are four links, click on Historical 3) At the top of the page under "Select a Report" pick "Pool Price" 4) Under Select Format, click CSV (can also click html) 5) Under Begin Date Choose 01/01/2003 6) Under End Date Choose 12/06/2003 It will take a few moments t download the data. At this point, you will get a "File Download" prompt from windowns. I usually save the file to my desktop and delete it later. I copy from this file to my excel spreadsheet. I just realized that the website actually limits the periods that you can export to a CSV file (722 days). It limits to 366 days if you chose html. The model I use is really just an excel column which starts at B2. It holds the current years hourly data. I update it every morning. Each morning, there are 24 extra rows to fill in my excel spreadsheet. To make it easy, I simply download the entire years with of data each morning. Therefore I can always paste to B2. by year end there will be 8760 rows (365 days *24 hours)of data. Hope that helps. Let me know otherwise. Thanks for your response. TS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Querying from excel.
I went to the url you provided. Usually when you goto the tables the url
changes and the desired info is shown. This can be imported with a macro such as With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, _ Destination:=Sheets("Data").Range("b2")) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With However, this site does not change the url so the best I can do is give you a slightly better url than you provided that you can hyperlink to. If you find out how to do this, please let me know. http://ets.powerpool.ab.ca/Market/reportsIndex.html -- Don Guillett SalesAid Software wrote in message m... Hey Don. The website is http://www.aeso.ca/. 1) Goto Reports at the top of the page 2) On the left side of the page there are four links, click on Historical 3) At the top of the page under "Select a Report" pick "Pool Price" 4) Under Select Format, click CSV (can also click html) 5) Under Begin Date Choose 01/01/2003 6) Under End Date Choose 12/06/2003 It will take a few moments t download the data. At this point, you will get a "File Download" prompt from windowns. I usually save the file to my desktop and delete it later. I copy from this file to my excel spreadsheet. I just realized that the website actually limits the periods that you can export to a CSV file (722 days). It limits to 366 days if you chose html. The model I use is really just an excel column which starts at B2. It holds the current years hourly data. I update it every morning. Each morning, there are 24 extra rows to fill in my excel spreadsheet. To make it easy, I simply download the entire years with of data each morning. Therefore I can always paste to B2. by year end there will be 8760 rows (365 days *24 hours)of data. Hope that helps. Let me know otherwise. Thanks for your response. TS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Querying SQL Server from Excel | Excel Discussion (Misc queries) | |||
Querying Info inside excel | Links and Linking in Excel | |||
Querying 2 Excel files | Excel Worksheet Functions | |||
Querying a range within Excel | Excel Worksheet Functions | |||
Querying an excel sheet | Excel Discussion (Misc queries) |