Connection & Query for data in Excel Spreadsheet

The following code can be used to run SQL query for data in an excel sheet and it is really simple to pull data.
–       Selva V Pasupathy, Hyderabad.

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


For some more resources about database techniques and other resources  
CLICK HERE

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: