Selva’s Site

Option Explicit
Sub QueryExcelData(ByVal xlsFileName As String, ByVal strCommandText As String)
'   This procedure runs and displays the query table data
'   on a new sheet.
'   QueryExcelData

   ActiveWorkbook.Worksheets.Add
   With ActiveSheet.QueryTables.Add _
         (Connection:=Array( _
         "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & xlsFileName & ";" & _
         "Jet OLEDB:Engine Type=35;"), _
         Destination:=Range("A1"))

      .CommandType = xlCmdTable
      .CommandText = Array(strCommandText)
      .Name = "ExcelQuery_" & _
            Format(Hour(Now), "00") & _
            Format(Minute(Now), "00") & _
            Format(Second(Now), "00")
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .SourceDataFile = xlsFileName
      .Refresh BackgroundQuery:=False
   End With
End Sub

Sub RunAboveQuery()
Dim dbFileName As String
Dim TableName As String
dbFileName = "C:\myFolders\SELVA.xls"
TableName = HalfYearData
Call QueryExcelData(dbFileName, "SELECT * FROM " & TableName)
End Sub