VBA通过SQL语句查询Excel文件中的数据

日常工作中，经常采用Excel来录入一些表格数据，因为它的方便直观易用，而不是采用Access来完成，但某个时候我们又想能够像Access一样通过SQL语句来查询这些数据，这里水文工具集介绍一个采用VBA宏过程来完成这一功能，当然具体的数据要对代码进行适当的修改，具体源代码如下：

'================================
' VBA通过SQL语句查询Excel文件中的数据
'
' 
'================================
Sub MakeExcelQT()
    Dim sConn As String
    Dim sSQL As String
    Dim oQt As QueryTable
    Dim sh As Worksheet
    
    sConn = "ODBC;DSN=Excel Files;DBQ=Z:\TheDataBook.xls;"
    sConn = sConn & "DefaultDir=Z:;DriverId=1046;"
    sConn = sConn & "MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT Name, Number FROM TheDataBook.xls.TheData WHERE Number >=2 ORDER BY Name DESC"
    
    Set sh = ThisWorkbook.Worksheets.Add
    
    Set oQt = sh.QueryTables.Add(sConn, sh.Range("A1"), sSQL)
    
    oQt.Refresh
    
End Sub

Sub idee()
  ActiveWorkbook.Sheets.Add
  With ActiveSheet.QueryTables.Add("ODBC;DSN=Excel-bestanden;DBQ=E:\TheDatabook.xls;", Range("H1"))
    .CommandText = "SELECT name, number FROM E:\TheDatabook.TheData TheData WHERE number = 1"
    .Refresh False
  End With
End Sub
Sub idee()
  ActiveWorkbook.Sheets.Add
  With ActiveSheet.QueryTables.Add("ODBC;DSN=Excel-bestanden;DBQ=E:\TheDatabook.xls",[H1])
    .CommandText = "SELECT name, number FROM TheData WHERE number =1"
    .Refresh False
  End With
End Sub