用vba通过sql查询xls文件的内容

发布网友

共2个回答

懂视网

Excel工作表中的数据，可以直接处理，不过如果想快的话最好读入到数组处理。前面说过读入数组可以循环读取，也可以直接给数组赋值读取。虽然这个方式速度快，但只适合读取连续的列，对于分开的列，这种方法不可行，不过可以用SQL语句读取到记录集中，然后对记录集进行处理，看下面例程：

Sub tt()
 Dim cnn2, rst2, cmd As Object
 Dim sqls, sql1, stName As String
 
 Set cnn2 = CreateObject("ADODB.Connection")
 Set rst2 = CreateObject("ADODB.Recordset")
 'Set cmd = CreateObject("ADODB.Command")
 
 datfile = "身份证邮件名址1.xls"    '文件名称
 datFullName = ThisWorkbook.Path & "" & datfile
 'cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 8.0;Data Source=" & datFullName
 cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended properties=Excel 8.0;Data Source=" & datFullName
 
 cnn2.Open cnnstr
 Windows(datfile).Activate
 stName = ActiveSheet.Name
 sqls = "SELECT 邮件号,收件人姓名 FROM [" & stName & "$]"
 Debug.Print sqls
 Set rst2 = cnn2.Execute(sqls)
 While Not rst2.EOF
 Debug.Print rst2(0) & rst2(1)
 rst2.movenext
 Wend

End Sub

几点说明：

1、本例程中被读取的数据文件“身份证邮件名址1.xls”是打开的，主要是为了取工作表名称。就读取数据本身而言，并不需要打开Excel文件，如果工作表名称是确定的，就可以不用打开；

2、默认工作表第一行是字段名，SQL语句中就是用这些字段名定位的，如程序中的“邮件号”、“收件人姓名”等。如果没有字段名，连接参数中需要增加HDR=NO；

3、连接参数中的“Extended properties=Excel 8.0;”也可以写成“Extended properties=Excel 5.0;”，其它上午数字都不行，这个是Excel的版本号，对应Excel97，对于Excel 97以上版本都用Excel 8.0 。这个扩展属性和前面的Provider配合使用，不同的版本对应不同的Provider。我的环境是Excel2007版，版本号是12.0，如果直接改成12.0会报错，提示：找不到可安装的ISAM，正确的写法是：

cnnstr = "Provider=Microsoft.Ace.OLEDB.12.0;Extended properties=Excel 12.0;Data Source=" & datFullName

4、还有其他一些参数，如下：

参数HDR的值：
HDR=Yes，这代表第一行是标题，不做为数据使用，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES
IMEX ( IMport EXport mode )设置：
IMEX 有三种模式:
　　0 is Export mode
　　1 is Import mode
　　2 is Linked mode (full update capabilities)
　　我这里特别要说明的就是 IMEX 参数了，因为不同的模式代表著不同的读写行为：
　　当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
　　当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
　　当 IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
意义如下:
0 ---输出模式;
1---输入模式;
2----链接模式(完全更新能力)

【VBA研究】用SQL语句读取EXCEL数据例程

标签：

热心网友

以下内容引用于excelhome论坛。建议去论坛查看更多信息。

'在Excel中使用SQL语句总结-1:

'************************************        标准SQL查找代码：       *********************************************************
Sub SQL_Excel_2003_2007()

'On Error Resume Next       '如果出现错误，忽略，然后执行下一行代码。
Application.ScreenUpdating = False '关闭屏幕刷新,成对出现，提高速度
Application.DisplayAlerts = False '关闭提示，,成对出现，避免出现提示框

'---------------------------------------   参数声明部分  ------------------- ------------------- -------------------

    Dim cnn, SQL$   '定义数据库连接和SQL语句
    Set cnn = CreateObject("adodb.connection")  '创建数据库连接
    Set rs = CreateObject("adodb.recordset")   '创建一个数据集保存数据

'---------------------------------------   设置数据库连接  ------------------- ------------------- -------------------

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName

       '将EXCEL文件作为数据库连接，实际并不打开EXCEL，
       'Excel2003版本：cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.Path & "\数据表.xls"
       'Excel2007版本：cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\数据表.xlsx"
       '带参数的连接字符串：cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;HDR=yes;IMEX=2';data source=" & ThisWorkbook.FullName
       '    HDR=Yes   代表 Excel 档中的工作表第一行是标题栏，标题只能是一行，不能使多行，或者合并的单元格。
       '    HDR=no     工作表第一行就是数据了，没有标题栏，不使用栏位，则栏位就以f代表，第一列列名就是：f1,第二列列名：f2
       '    IMEX 汇入模式  0 只读  1 只写   2 可读写
       '              当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
       '              当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
       '              当 IMEX=2 时为“连结模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
       'Data Source  存储查询数据来源的工作薄名称，数据库路径为：数据表.xls  或本表：& ThisWorkbook.FullName

'---------------------------------------   设置SQL语句  ------------------- ------------------- -------------------

  SQL = "select * from  [数据表_1$A1:G100] where 姓名='马拉多纳'"

       '在Sheet1表内查找列名为：姓名中所有：马拉多纳的数据。
       '[XXX$A2:G100]的中括号和$为特别数据表标示符，XXX为Sheet名，A2:G100是选取的区域。
       'SQL语句是一个字符串，双引号开头和结尾，列名两边无单引号，表示一个字符串：‘马拉多纳’，要用单引号扩上，数字就不用了。
       'Nu=36:  AA="马拉多纳":   set  Sh=Sheet1:  SQL = "select * from  [" & Sh.name & “$] where 姓名=‘” & AA & “’  and  年龄=Nu"

'---------------------------------------   SQL结果处理  ------------------- ------------------- -------------------

   Set rs = cnn.Execute(SQL) '将SQL语句获得的数据传递给数据集
   Sheets("结果").Cells.ClearContents       '清理保存数据的区域
   Sheets("结果").Range("a2").CopyFromRecordset rs    '将数据集粘贴到Excel中，左上角为A2，无列名。
   'Sheets("结果").Range("b2").CopyFromRecordset cnn.Execute(SQL)  '可以不声明Y，直接使用

   cnn.Close     '关闭数据库连接
   Set cnn = Nothing    '将CNN从内存中删除。
'--------------------------------------- ------------------------ ------------------- -------------------
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub   '这就是最简单的EXCEL中SQL的应用，其他的都是SQL的运用了

全部栏目

用vba通过sql查询xls文件的内容