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

Give me a sample of the final url and more info and I can help. What you
copy and where to. You might send your workbook to me to look at.

--
Don Guillett
SalesAid Software

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

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

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