Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

https://stackoverflow.com/a/69197865/5079799

https://www.codevba.com/excel/QueryTable.htm

Class `clsQueryTables`

'https://www.codevba.com/excel/QueryTable.htm
Private Const xlEntirePage = 1 ' Entire page
Private Const xlAllTables = 2 ' All tables
Private Const xlSpecifiedTables = 3 ' Specified tables
Private Const xlWebFormattingAll = 1 ' All formatting is imported.
Private Const xlWebFormattingRTF = 2 ' Rich Text Format compatible formatting is imported.
Private Const xlWebFormattingNone = 3 ' No formatting is imported.
Public URLStr As String
Public WSNameStr As String
Public WebSelectionType As Integer
Public WebFormatting As Integer
Public WebTables As String
Private Sub Class_Initialize()
    URLStr = ""
    WSNameStr = ""
    WebSelectionType = xlAllTables
    WebFormatting = xlWebFormattingAll
End Sub

Module:

Public Sub Query_Web_URL(ByRef QueryArgs As clsQueryTables)

 Call WorksheetCreateDelIfExists(QueryArgs.WSNameStr)
 Dim WS As Worksheet
 Set WS = Worksheets(QueryArgs.WSNameStr)

 With WS.querytables.Add(Connection:="URL;" & QueryArgs.URLStr, Destination:=Range("$A$1"))
  .Name = QueryArgs.URLStr
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = False
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .WebSelectionType = QueryArgs.WebSelectionType
  .WebFormatting = xlWebFormattingAll
  .WebPreFormattedTextToColumns = True
  .WebConsecutiveDelimitersAsOne = True
  .WebSingleBlockTextImport = False
  .WebDisableDateRecognition = False
  .WebDisableRedirections = False
  .Refresh BackgroundQuery:=False
 End With

End Sub

Test:

Sub Testing()
 
 Dim QueryArgs As New clsQueryTables
 QueryArgs.URLStr = "http:..."
 QueryArgs.WSNameStr = "Test"
 QueryArgs.WebSelectionType = xlAllTables
 Call Query_Web_URL(QueryArgs)

End Sub
  • No labels