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

imnewhere

New Member
Joined
Mar 20, 2019
Messages
2
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
Welcome to the Board

Code:
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

New Member
Joined
Mar 20, 2019
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,587
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top