Query Tables

     

  • Query tables are useful to retrieve data from external sources i.e Databases(SQL,Access), web sites..

    •  

     

    Private Sub CommandButton1_Click()
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Result").Delete
    Dim QT As QueryTable
    Dim url As String
    url = "https://in.finance.yahoo.com/q?s=AAPL"
    Dim sh As Worksheet
    Set sh = Worksheets.Add(, Worksheets(Sheets.Count), 1)
    Set QT = sh.QueryTables.Add( _
    Connection:="URL;" & url, _
    Destination:=sh.Range("A1"))
    With QT
    .RefreshOnFileOpen = True
    .FieldNames = True
    .Name = "Information"
    .WebSelectionType = xlAllTables
    .Refresh BackgroundQuery:=False
    End With
    sh.Name = "Result"
    Application.DisplayAlerts = False
    End Sub

     

     

    Connection directly metntioned instead of creating URL:

     

    With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://in.finance.yahoo.com/q?s=AAPL", Destination:=Range("$A$1"))

     

    RefreshOnFileOpen = False

  • True = It refreshes Query table with latest updates\changes when Excel opens

    •  

    FieldNames = True

  • FiledNames of Source will become column headings in excel

    • Sheets(2).Querytables(1).Fieldnames = true

     

    PreserveFormatting = True

  • True: Previous formatting applied to newly added rows in a table
  • False: Previous formatting not applied to newly added rows in a table

    •  

    BackgroundQuery = True

  • True: If queries are performed at background

    •  

    Refresh Style:

  • XlInsertdeletecells:retains matching records, add\delete as per updated data
  • Xloverwritecells:No new cells\rows added, overwrites new cells
  • Xlinsertentirerows:entire rows inserted, no deletion of rows\cells

     

  • .RefreshStyle = xlInsertDeleteCells
  • .RefreshPeriod = 0

    •  

  • .SavePassword = False
  • .SaveData = True

    •  

  • .AdjustColumnWidth = True
  • .RowNumbers = False
  • .FillAdjacentFormulas = False

    •  

    Denotes about specifed tables\All tables

  • .WebSelectionType = xlSpecifiedTables
  • .WebTables = "1,2,3,4,5"

    •  

  • .WebPreFormattedTextToColumns = True
  • .WebConsecutiveDelimitersAsOne = True

    •  

    WebSingleBlockTextImport = False

    True: if data from the HTML tags in the specified Web page is processed all at once

    False: If the data is imported in blocks of contiguous rows, which enables the user to recognise header rows

     

  • .WebDisableDateRecognition = False
  • .WebDisableRedirections = False
  • .WebFormatting = xlWebFormattingNone

    •  

  • .Refresh BackgroundQuery:=False

    •  

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli