Creating a Macro to open CSV files and pasting on a sheet.

[siow89]

Hello,

i was wonder is there was any way I could go to the following website:

https://www.ieso.ca/imoweb/pubs/marketReports/daily/PUB_GenPlan_20140711.csv

and copy paste it into an excel sheet. What i would need is to get this page and the previous 7 days by changing the date "20140711" to "20140710" and so on. Each orevious day needs to be copy pasted to a different sheet.

As a new day occurs, I would like to delete the page 7 days ago and add the new day on a new sheet.
I hope this is clear enough.

Thank you!

 

[VBA Geek]

what did you type in A1? is it really a date?

 

[siow89]

I have a the date: 20140718
which is in the yyyymmdd format...shouldn't this work?

 

[VBA Geek]

your URL is not correct

[COLOR=#333333]Private Const s_URL As String = "[/COLOR][URL="https://www.ieso.ca/imoweb/pubs/marketReports/daily/PUB_GenPlan"]https://www.ieso.ca/imoweb/pubs/mark...ly/PUB_GenPlan[/URL][COLOR=#333333]_"

it should be

[/COLOR]

Private Const s_URL As String = "https://www.ieso.ca/imoweb/pubs/marketReports/daily/PUB_GenPlan_"

 

[siow89]

I have the exact URL...
I copy pasted yours just to make sure but I still get the same error unfortunately...

 

[VBA Geek]

i just ran it and it works on my side

on which line would it bug then?

 

[siow89]

This line:

.Open bstrMethod:="GET", bstrUrl:=s_URL & Format$(Range("A1").Value, "YYYYMMDD") & ".csv", _
varAsync:=False

 

[siow89]

What is the sheet name that you are using?

 

[VBA Geek]

I have a hunch that what you are typing in A1 is not what you think it is

if you type 20140718, this is not July 18th 2014. try to convert the cell to general number format, the right number (i.e. the number of days since 31 December 1899) should be

41838 for July 18th 2014.

you are getting an overflow error because excel does not handle over 20 million days since 31 Dec1899


use the date function to get the date that you want

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A1</th><td style="text-align:left">=DATE(<font color="Blue">2014,7,18</font>)</td></tr></tbody></table></td></tr></table><br />

 

[siow89]

Yes, you are absolutely right!
But is there anyways to make excel read it in yyyymmdd format?

 

[VBA Geek]

Select your cell, type Control 1, this opens up custom formatting

go to Custom and type YYYYMMDD