open a webpage and download a csv file from excel

[Electrader]

I need excel to open this webpage:
https://www.midwestiso.org/MARKETSOPERATIONS/REALTIMEMARKETDATA/Pages/LMPContourMap.aspx

then select the link to the csv file and download it, then open it in excel.

I would like to automate this process to happen every 5 minutes, so that I can create a database.

 

[brettdj]

If the filename doesn't change you could use this XMLHTTP code (courtesy of Matty Vidas) to grab the file, download it and open it

Will the file really update every five minutes?

Cheers

Dave

Option Explicit
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
    Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
    oXMLHTTP.Send 'send request
     
    Do While oXMLHTTP.readyState <> 4
        DoEvents
    Loop
     
    oResp = oXMLHTTP.responseBody 'Returns the results as a byte array
    vFF = FreeFile
    If Dir(vLocalFile) <> "" Then Kill vLocalFile
    Open vLocalFile For Binary As #vFF
    Put #vFF, , oResp
    Close #vFF
     
     'Clear memory
    Set oXMLHTTP = Nothing
End Function
 
Sub TestingTheCode()
Dim strFileName As String
Dim WB As Workbook
strFileName = "c:\file.csv"
    SaveWebFile "https://www.midwestiso.org/ria/Consolidated.aspx?format=csv", strFileName
 Set WB = Workbooks.Open(strFileName)
End Sub

 

[Electrader]

Dave,

thanks for the reply. I tried opening the VB editor and pasting the code into it, then running it. It returns a path/file access error (runtime error 75). The bit of code it is having an issue with is

Open vLocalFile For Binary As #vFF

I am really new to this, so it I am probably making a silly mistake. Also, the data updates every 5 mins and is "real time", so that is the motivation behind updating every 5 mins.

Thank you!