Insomnia: Playing with QueryTables In Excel

Playground: Write a code for quick creating and refreshing a QueryTable in Excel - data source is one table (ListObject.Table, one user creates with Ctrl+T) - data is in an excel file - data source relative - button to refresh a query.

Sub main()
  Call mkQryTbx([Table1[#All]], [H2])
End Sub

Sub mkQryTbx(DataRange As Range, OutputRange As Range)
 
 Dim dtaRange As String: dtaRange = "[" & ActiveSheet.Name & "$" & DataRange.Address(False, False) & "]"
  
'create QueryTable
  
  With ActiveSheet.QueryTables.Add("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";", OutputRange)
    .CommandText = "SELECT name, number FROM " & dtaRange & " WHERE number = 1;"
    .Name = "ExternalData"
    .BackgroundQuery = False
    .Refresh False
  End With

'create an Update button 

  Dim t As Range: Set t = ActiveSheet.Range("A1")
   Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
   With btn
     .Name = "btnRefresh"
     .OnAction = "btnS"
     .Caption = "Refresh"
   End With

End Sub

Sub btnS()
    ActiveSheet.Range("H2").QueryTable.Refresh BackgroundQuery:=False
End Sub

http://dailydoseofexcel.com/archives/2008/06/24/create-a-querytable-from-an-excel-source-in-vba/ Other reading against using QueryTables (why?) http://itknowledgeexchange.techtarget.com/beyond-excel/say-goodbye-to-querytables/ Insert button: http://stackoverflow.com/questions/4559094/how-to-add-a-button-programmatically-in-vba-next-to-some-sheet-cell-data