How do I import a dated xls file from the web VBA

Question

I need to import an xls file from the web address https://docs.misoenergy.org/marketreports/YYYYMMDD_sr_nd_is.xls where YYYYMMDD is inputed by the user on another worksheet in the same workbook. In the code below nsiday = 20190316 - 1. I don't know how to actually paste the data in the worksheet I want. I am trying to adapt code that grabs a csv file so that it works for the xls file (https://docs.misoenergy.org/marketreports/YYYYMMDD_rt_lmp_final.csv). I hope that makes sense and thank you all for reading/helping! Note: I haven't included the full csv code I'm trying to adapt.

Option Explicit

Sub NSI()
    Dim xday As String
    Dim todaystamp As String
    Dim nsiday As String
    Dim MISORTSht As Worksheet
    Dim Selection As Range

    Set MISORTSht = Sheet3

    MISORTSht.Cells.ClearContents
    If MISORTSht.QueryTables.Count > 0 Then
    MISORTSht.QueryTables(1).Delete
    End If


    Dim web As Object
    Set web = CreateObject("Microsoft.XMLHTTP")

    todaystamp = Format(Sheet1.Cells(6, 1).Value, "yyyymmdd")
    xday = Format(Sheet1.Cells(1, 1).Value, "yyyymmdd")
    'xday is user defined
    nsiday = xday - 1


start:
    web.Open "GET", "https://docs.misoenergy.org/marketreports/" & nsiday & "_sr_nd_is" & ".xls", False
    web.send

    If web.Status = "200" Then

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With MISORTSht.QueryTables.Add(Connection:="URL;https://docs.misoenergy.org/marketreports/" & nsiday & "_sr_nd_is" & ".xls" _
    , Destination:=MISORTSht.Range("A1"))
    .Name = "NSI_MISO"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

You should use `Date` variable for Date calculations. Format() gives you sting only. — PatricK, Mar 19 '19 at 06:18
Thanks PatricK but, I believe I require string to input to the URL. I may be wrong but I think that's why the original coder did it that way. — imnewhere, Mar 20 '19 at 13:32
I keep getting "Invalid Query" even though the file is accessible when I paste https://docs.misoenergy.org/marketreports/20190315_sr_nd_is.xls for example in browser. I can see in the Locals window, the date variables are set correctly too... — imnewhere, Mar 20 '19 at 13:34
This post https://stackoverflow.com/questions/44752805/import-excel-file-using-something-similiar-to-querytable I think answered my question. I wrote new code using workbooks.open ("http....etc") to bypass the whole querytables conundrum. Anyone know of a better/faster way of importing an xls file from the web? — imnewhere, Mar 20 '19 at 16:07
You should convert dates type to string to construct the URL. Not convert to string from cell value (can't do date maths). if the web site does not require login, you can instruct Excel to open the URL directly then save as local file. — PatricK, Mar 20 '19 at 22:49
I don't follow. If I plug in https://docs.misoenergy.org/marketreports/20190315_sr_nd_is.xls in the lines below "start" where the addresses go, I still get "Invalid Query" on .Refresh BackgroundQuery:=False. How can formatting have an impact here as I know I'm using the proper URL (if plugged into browser, it works)....Thanks again! — imnewhere, Mar 20 '19 at 23:18

score 0 · Answer 1 · answered Mar 21 '19 at 23:26

Disregarding the use of QueryTable, you can open online files directly from Excel. Below is an example of how to generate the URL based on a date input and opens it from Excel.

Option Explicit

Private Const DATE_FMT As String = "yyyymmdd"
Private Const BASE_URL As String = "https://docs.misoenergy.org/marketreports/"
Private Const POSTFIX1 As String = "_sr_nd_is.xls"
Private Const POSTFIX2 As String = "_rt_lmp_final.csv"

Sub Main()
    Dim dDataDate As Date, dToday As Date, oWB As Workbook

    dToday = CDate(ThisWorkbook.Sheets(1).Cells(6, 1).Value) ' Not sure what to do with this
    dDataDate = CDate(ThisWorkbook.Sheets(1).Cells(1, 1).Value) - 1 ' 1 day before it

    Set oWB = GetOnlineFile(CreateURL1(dDataDate))

    If Not oWB Is Nothing Then
        ' Do whatever you need with the opened file

        oWB.Close
        Set oWB = Nothing
    End If
End Sub

Private Function GetOnlineFile(URL As String) As Workbook
    On Error Resume Next
    Set GetOnlineFile = Workbooks.Open(URL)
End Function

Private Function CreateURL1(DataDate As Date) As String
    CreateURL1 = BASE_URL & Format(DataDate, DATE_FMT) & POSTFIX1
End Function

Private Function CreateURL2(DataDate As Date) As String
    CreateURL2 = BASE_URL & Format(DataDate, DATE_FMT) & POSTFIX2
End Function

Thank you PatricK! As a newbie, I really appreciate your feedback and code example. I used the workbooks.open but my code wasn't as elegant as yours. — imnewhere, Mar 23 '19 at 13:18

How do I import a dated xls file from the web VBA

1 Answers1