With .Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.Calculation = xlCalculationManual
.Sheets.Add
With .ActiveSheet
With .ListObjects.Add(SourceType:=0, Source:=Array(Array("ODBC;DSN=Excel Files;DBQ=" & ActiveWorkbook.FullName & _
";DefaultDir=" & ActiveWorkbook.Path), Array(ActiveWorkbook.Path & _
";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT" _
, "`My_Sheet$`.Invoice_Number, `My_Sheet$`.Invoice_Date," _
, "Sum(`My_Sheet$`.Quantity) AS Quantity, Sum(`My_Sheet$`.Amount) AS Amount, `My_Sheet$`.Billing_Code1" _
& Chr(13) & "" & Chr(10) & _
"FROM" _
, "`My_Sheet$` `My_Sheet$`" _
& Chr(13) & "" & Chr(10) & _
"GROUP BY" _
, "`My_Sheet$`.Invoice_Number, `My_Sheet$`.Invoice_Date, `My_Sheet$`.Billing_Code1")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_Excel_Files"
.Refresh BackgroundQuery:=True
End With
End With
'------------------------------------------------------------------------------------------------------------------------
Do While IsEmpty(Cells(1, 2))
.Wait Now + TimeSerial(0, 0, 2)
Loop
.Sheets.Add
With .ActiveSheet
Sheets(2).Range("A1", Range("A1").SpecialCells(xlLastCell)).Copy
.Range("A1").PasteSpecial (xlPasteValuesAndNumberFormats)