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!

 

[siow89]

I have tried it that but it still seem to get "overflow" error...I don't think it is reading it properly

 

[VBA Geek]

have you used the date function to generate your date and then format it? you mustn't enter 20140718 in your cell because these are in reality many many days in the future

 

[siow89]

it works when using the date function. But is there anyway to not use the date function and just put in the values?

something along the lines:
Const sDateCELL = "A1"
Dim myDate As Date
Dim sDate As String
Dim sYear As String
Dim sMonth As String
Dim sDayOfMonth As String


'Get the date chosen by the User
'Reformat the date to 'yyyymmdd' format
sDate = Trim(Sheets("Date").Range(sDateCELL).Text)

 

[VBA Geek]

there is

type 2014/07/18

OR type 18 July 2014, excel will understand it is a date

 

[haleem100]

I have to create an excel spreadsheet from a folder that has 7 CSV files. Is there an macro that can do it
I have looked around, but unable to do one or find one

 

[VBA Geek]

Hi

can you start a new thread with a more detailed description? maybe show what your CSV looks like as well

I have to create an excel spreadsheet from a folder that has 7 CSV files. Is there an macro that can do it
I have looked around, but unable to do one or find one

 

[siow89]

Hello,

So I have been playing around with your code and have been able to successfully combine it with my macro where i fetch other data. However, I was wondering if it would be possible for a small alteration. I was wondering if it would be possible to have a sheet consistently called "today" and another called "yesterday"...so when you run the today macro it fetch's today's data and pastes it the today sheet and fetch's yesterday's data and pastes it in "yesterday" sheet.

Thank you very much!