How to import .xls file from web with VBA using querytables?

[imnewhere]

Hello all,

I'm getting "Invalid Query" on .Refresh BackgroundQuery:=False when stepping through the code. Is there a way to import .xls files from the web using querytables? I've seen it done for .csv and I'm trying to adapt that code to xls.

Thank you for helping!

Sub XLSImport()

Dim ImportSht As Worksheet
Set ImportSht = Sheet6

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With

ImportSht.Cells.ClearContents

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



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

If web.Status = "200" Then

With ImportSht.QueryTables.Add(Connection:="URL;https://docs.misoenergy.org/marketreports/20190315_sr_nd_is.xls" _
, Destination:=ImportSht.Range("A1"))
.Name = "XLS_IMPORT"
.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
End If


With Application
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With

End Sub

 

[Worf]

Welcome to the Board

Sub Import_to_Sheet()
Dim ab, sp$
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "https://docs.misoenergy.org/marketreports/20190315_sr_nd_is.xls"
    .Send
    ab = .responseBody                     ' Get binary content
    sp = ThisWorkbook.Path & "\temp.xls"
End With
With CreateObject("ADODB.Stream")          ' Save binary content to xls file
    .Type = 1
    .Open
    .Write ab
    .SaveToFile sp, 2
    .Close
End With
With Workbooks.Open(sp, , True)
    ab = .Worksheets(1).UsedRange.Value     ' Get values to array
    .Saved = True
    .Close
End With
CreateObject("Scripting.FileSystemObject").DeleteFile sp, True
' Insert array to worksheet
ThisWorkbook.Sheets("Sheet2").Cells(1, 1).Resize(UBound(ab, 1), UBound(ab, 2)).Value = ab
End Sub

 

[imnewhere]

Works like a charm. Thank you so much Worf!!! If you had to recommend only one source to gain proficiency in VBA for someone new, what would you recommend?

Thanks again!

Welcome to the Board

 

[Worf]

You are welcome.
You may like this book: https://www.amazon.com/Excel-Programming-Dummies-Computer-Tech/dp/1119518172/ref=dp_ob_image_bk