如何在Excel 2002中设置连接查询.xls文件数据并使用SQL操作

错误3706修复 #

你出现报错的核心原因是连接配置语法错误：同时给Provider属性和ConnectionString属性重复赋值，且Provider属性的赋值多写了一次Provider=前缀，导致驱动识别失败。

修正后的连接代码如下：

Dim cn As Object, rs As Object, output As String, sql As String

'---连接数据源---
Set cn = CreateObject("ADODB.Connection")
With cn
    ' 无需单独设置Provider属性，所有配置统一写在连接字符串中即可
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
    .Open
End With

Excel 8.0对应Excel 97-2002的xls格式，和你的开发环境完全匹配，无需更换驱动。

疑问解答 #

1. 选择ODBC还是OLE DB？ #

• OLE DB完全支持查询Excel文件，对于VBA操作当前工作簿数据的场景，OLE DB比ODBC配置更简单、运行效率更高，优先选择OLE DB即可。
• ODBC需要额外配置系统数据源，不符合你不想新增额外配置/文件的需求，不推荐使用。

2. 能否用SQL执行插入操作？ #

可以，仅需要调整两个配置：

• 执行写入/插入/更新操作时，不要设置IMEX=1，该参数会开启只读的文本读取模式，写入时改为IMEX=0或者直接移除IMEX参数即可。
• 插入语句需要遵循Excel SQL语法规则：工作表名需要加$后缀并用中括号包裹，示例代码如下：

' 假设要写入的工作表名为Sheet1，第一行表头为列1、列2
sql = "INSERT INTO [Sheet1$] (列1, 列2) VALUES (123, '测试字符串')"
cn.Execute sql

内容的提问来源于stack exchange，提问作者Mister Equis