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

分类:代码, 博客 标签:,

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

'================================
' VBA通过SQL语句查询Excel文件中的数据
'
' http://www.cnhup.com
'================================
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


分类:代码, 博客 标签:,

发表评论

You must be logged in to post a comment.