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

S

siow89

New Member
Joined
Jun 16, 2014
Messages
21
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!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
To Import from today to 7 days before run: dhImport_Data
To import today and delete 7 days ago run: dhImport_Data_And_Delete7DaysBefore
Code: 
Option Explicit
Private Const s_URL As String = "https://www.ieso.ca/imoweb/pubs/marketReports/daily/PUB_GenPlan_"
Private oHTTP As Object
Sub dhImport_Data_And_Delete7DaysBefore()
Dim sResp As String
Set oHTTP = CreateObject("MSXML2.XMLHTTP")


Application.DisplayAlerts = False
On Error Resume Next
Sheets(Format$(Date - 7, "YYYYMMDD")).Delete
On Error GoTo 0
Application.DisplayAlerts = True


With oHTTP
    .Open bstrMethod:="GET", bstrUrl:=s_URL & Format$(Date, "YYYYMMDD") & ".csv", _
                                varAsync:=False
    .send
    sResp = .responseText
    dh_ImportToSheet sResp, Format$(Date, "YYYYMMDD")
End With
Set oHTTP = Nothing
End Sub


Sub dhImport_Data()
Dim oHTTP As MSXML2.XMLHTTP
Dim sResp As String
Dim lDays As Long
Set oHTTP = CreateObject("MSXML2.XMLHTTP")
With oHTTP
    For lDays = (Date - 7) To Date
        .Open bstrMethod:="GET", bstrUrl:=s_URL & Format$(lDays, "YYYYMMDD") & ".csv", _
                                    varAsync:=False
        .send
        sResp = .responseText
        dh_ImportToSheet sResp, Format$(lDays, "YYYYMMDD")
    Next lDays
End With
Set oHTTP = Nothing
End Sub


Sub dh_ImportToSheet(ByVal sData As String, _
                        ByVal wksName As String)
Dim aData As Variant
Dim wks As Excel.Worksheet
On Error Resume Next
Set wks = ThisWorkbook.Sheets(wksName)
On Error GoTo 0
If wks Is Nothing Then
    Set wks = ThisWorkbook.Sheets.Add
    wks.Name = wksName
End If
aData = Split(sData, vbLf)
With wks
    .UsedRange.Clear
    With .Range("A1").Resize(UBound(aData) + 1)
        .Value = Application.Transpose(aData)
        .TextToColumns DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
                        Consecutivedelimiter:=False, Tab:=False, SemiColon:=False, _
                        Comma:=True, Space:=False, Other:=False
                        
    End With
End With
End Sub
 
Upvote 0
Hello,

Thank you very much. The "dhImport_Data_And_Delete7DaysBefore" macro seems to work perfectly.

However, when I try to run the "dhImport_Data" macro, I get "user defined type not defined" for the line:
Sub dhImport_Data()
Dim oHTTP As MSXML2.XMLHTTP

Is there any reason for this?
 
Upvote 0
yes, declare it as an OBJECT as you are good to go (dim oHTTP as object)

Alternatively you can add a reference to Microsoft XML

here is a link that explains early binding and late binding

Using early binding and late binding in Automation



siow89 said:
Hello,

Thank you very much. The "dhImport_Data_And_Delete7DaysBefore" macro seems to work perfectly.

However, when I try to run the "dhImport_Data" macro, I get "user defined type not defined" for the line:
Sub dhImport_Data()
Dim oHTTP As MSXML2.XMLHTTP

Is there any reason for this?
Click to expand...
 
Upvote 0
I just noticed that i duplicated some declarations, you might wanna get rid of the line in bold

thanks

Code: 
Option Explicit
Private Const s_URL As String = "https://www.ieso.ca/imoweb/pubs/marketReports/daily/PUB_GenPlan_"
Private oHTTP As Object


Sub dhImport_Data_And_Delete7DaysBefore()
Dim sResp As String
Set oHTTP = CreateObject("MSXML2.XMLHTTP")

Application.DisplayAlerts = False
On Error Resume Next
Sheets(Format$(Date - 7, "YYYYMMDD")).Delete
On Error GoTo 0
Application.DisplayAlerts = True


With oHTTP
    .Open bstrMethod:="GET", bstrUrl:=s_URL & Format$(Date, "YYYYMMDD") & ".csv", _
                                varAsync:=False
    .send
    sResp = .responseText
    dh_ImportToSheet sResp, Format$(Date, "YYYYMMDD")
End With
Set oHTTP = Nothing
End Sub




Sub dhImport_Data()
[SIZE=3][COLOR=#ff0000][B]Dim oHTTP As MSXML2.XMLHTTP[/B][/COLOR][/SIZE]
Dim sResp As String
Dim lDays As Long
Set oHTTP = CreateObject("MSXML2.XMLHTTP")
With oHTTP
    For lDays = (Date - 7) To Date
        .Open bstrMethod:="GET", bstrUrl:=s_URL & Format$(lDays, "YYYYMMDD") & ".csv", _
                                    varAsync:=False
        .send
        sResp = .responseText
        dh_ImportToSheet sResp, Format$(lDays, "YYYYMMDD")
    Next lDays
End With
Set oHTTP = Nothing
End Sub




Sub dh_ImportToSheet(ByVal sData As String, _
                        ByVal wksName As String)
Dim aData As Variant
Dim wks As Excel.Worksheet
On Error Resume Next
Set wks = ThisWorkbook.Sheets(wksName)
On Error GoTo 0
If wks Is Nothing Then
    Set wks = ThisWorkbook.Sheets.Add
    wks.Name = wksName
End If
aData = Split(sData, vbLf)
With wks
    .UsedRange.Clear
    With .Range("A1").Resize(UBound(aData) + 1)
        .Value = Application.Transpose(aData)
        .TextToColumns DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
                        Consecutivedelimiter:=False, Tab:=False, SemiColon:=False, _
                        Comma:=True, Space:=False, Other:=False
                        
    End With
End With
End Sub
 
Upvote 0
Hello,

I was wondering, would it be possible to change the code slightly. I would like to be able to basically have one macro that will give me the present date .CSV file. I would like to also have a macro that you can put in for instance cell A1, the specific date that you want to retrive in "yyyymmdd" format.

Your help would be much appreciated!
 
Upvote 0
you can change the Date variable in red to make it whatever day you like

Date represent the current date, you can type your date in a1 and replace date with range("A1").value

Code: 
Sub dhImport_Data()
Dim sResp As String
dim oHTTP as object
Set oHTTP = CreateObject("MSXML2.XMLHTTP")


With oHTTP
    .Open bstrMethod:="GET", bstrUrl:=s_URL & Format$([COLOR=#ff0000][B]Date[/B][/COLOR], "YYYYMMDD") & ".csv", _
                                varAsync:=False
    .send
    sResp = .responseText
    dh_ImportToSheet sResp, Format$([B][COLOR=#ff0000]Date,[/COLOR][/B] "YYYYMMDD")
End With
Set oHTTP = Nothing
End Sub





Sub dh_ImportToSheet(ByVal sData As String, _
                        ByVal wksName As String)
Dim aData As Variant
Dim wks As Excel.Worksheet
On Error Resume Next
Set wks = ThisWorkbook.Sheets(wksName)
On Error GoTo 0
If wks Is Nothing Then
    Set wks = ThisWorkbook.Sheets.Add
    wks.Name = wksName
End If
aData = Split(sData, vbLf)
With wks
    .UsedRange.Clear
    With .Range("A1").Resize(UBound(aData) + 1)
        .Value = Application.Transpose(aData)
        .TextToColumns DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
                        Consecutivedelimiter:=False, Tab:=False, SemiColon:=False, _
                        Comma:=True, Space:=False, Other:=False
                        
    End With
End With
End Sub
 
Upvote 0
Hello,

Thank you so much! When i replace date with range("A1").value I seem to get runtime error overflow.

Am i doing something wrong?
 
Upvote 0
did you add this line before the sub begins

Private Const s_URL As String = "https://www.ieso.ca/imoweb/pubs/marketReports/daily/PUB_GenPlan_"
 
Upvote 0
Yes I did,


this is what i have:

Option Explicit
Private Const s_URL As String = "https://www.ieso.ca/imoweb/pubs/marketReports/daily/PUB_GenPlan_"
Private oHTTP As Object
Sub dhImport_Data()
Dim sResp As String
Dim oHTTP As Object
Set oHTTP = CreateObject("MSXML2.XMLHTTP")

With oHTTP
.Open bstrMethod:="GET", bstrUrl:=s_URL & Format$(Range("A1").Value, "YYYYMMDD") & ".csv", _
varAsync:=False
.send
sResp = .responseText
dh_ImportToSheet sResp, Format$(Range("A1").Value, "YYYYMMDD")
End With
Set oHTTP = Nothing
End Sub


Sub dh_ImportToSheet(ByVal sData As String, _
ByVal wksName As String)
Dim aData As Variant
Dim wks As Excel.Worksheet
On Error Resume Next
Set wks = ThisWorkbook.Sheets(wksName)
On Error GoTo 0
If wks Is Nothing Then
Set wks = ThisWorkbook.Sheets.Add
wks.Name = wksName
End If
aData = Split(sData, vbLf)
With wks
.UsedRange.Clear
With .Range("A1").Resize(UBound(aData) + 1)
.Value = Application.Transpose(aData)
.TextToColumns DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
Consecutivedelimiter:=False, Tab:=False, SemiColon:=False, _
Comma:=True, Space:=False, Other:=False

End With
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
186
Insurance Dave
I
S
  • Question
Create Excel Packing Slips using CSV Data
Replies
4
Views
249
StaceyVECL
S
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
331
Agnarr
Agnarr
D
  • Question
Updating multiple table entries by date with VBA
Replies
1
Views
194
NoSparks
N
X
  • Solved
How To Consolidate Multiple CSV Files In A Single CSV File Automatically?
2
Replies
16
Views
702
xlmaniac
X

Forum statistics

Threads
1,214,394
Messages
6,119,262
Members
448,880
Latest member
aveternik

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top