WPS: VBA+SQL,ADO连接问题

  1. Application.WorksheetFunction.Find("WPS", Application.Path) :返回C:\Program Files (x86)\Kingsoft\WPS Office\11.8.2.10972\office6

  1. 查看Office是WPS还是Microsoft Office

  1. WPS 默认并未安装引用 ADO 库,WPS 中执行 VBA+SQL 时会报错:3706:未找到提供程序。该程序可能未正确安装(引擎没有注册)

  1. Win10_X64+WPS2019+64位Office2019环境,实测:

  1. 安装32位 2010-AccessDatabaseEngine_X86.exe ,安装时可能会被杀毒软件隔离

  1. Office2019 SQL连接 xls 文件时,最好额外安装插件 2010-AccessDatabaseEngine_X86.exe

  1. cnn.open "Provider=..." 前要先关闭被连接 Book,否则报错:Microsoft Access数据库引擎无法打开...已被其他的用户以独占方式打开

  1. WPS只有32位的,必须安装32位的 AccessDatabaseEngine.exe

  1. WPS只能查询 65536 行以内的数据(未测);实测可连接外部Book进行查询:xls/xlsx

  1. 运行时错误:-2147467259(80004005):

  1. WPS当前表连接:Microsoft Jet数据库引擎无法打开文件''或向其写入数据。它已被其他的用户以独占方式打开,或没有查看和写入数据的权限

  1. 【注Bug】,实测WPS无法连接代码所在的 xlsm :(未找到解决办法)

  • cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=2';Data Source=" & ThisWorkbook.FullName

  • Microsoft Access数据库引擎无法打开...已被其他的用户以独占方式打开

  1. 外部表不是预期格式 ,需按Excel版本修改ADO链接字符串,如下:

  1. 需先关闭 Book,再连接 cnn.open "Provider=..."

  1. Office2007-19 的 xls/xlsx:(实测WPS可连接ACE 12.0

  • cnn.open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes;IMEX=2';Data Source=C:\tt.xls"

  1. Office97-03 的 xls

  • cnn.open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes;IMEX=2';Data Source=C:\tt.xls"

  1. 若上述步骤仍无法解决问题,可依次安装尝试:

  1. 2016-AccessDatabaseEngine_X64.exe2016-AccessDatabaseEngine_X86.exehttps://www.microsoft.com/zh-cn/download/details.aspx?id=54920

  1. 2010-AccessDatabaseEngine_X86.exe2010-AccessDatabaseEngine_X64.exehttps://www.microsoft.com/zh-cn/download/details.aspx?id=13255

  1. 按Excel版本修改对应的ADO连接符,如上:


应用测试示例,仅供参考:

🔔

Option Explicit

Sub BookInSQL()

Dim dic_bk As Object, cnnPP As Object, rst As Object, cnnStr$, wb As Workbook, bk, sht As Worksheet, wbPP As Workbook, shtPP As Worksheet, sql$, i%, shtPPName$

'Application.ScreenUpdating = False

Set cnnPP = CreateObject("ADODB.Connection"): Set rst = CreateObject("ADODB.RecordSet"):

cnnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=2';Data Source="

Set dic_bk = 模块2.selectBooks

If Not dic_bk Is Nothing Then

Set wb = ThisWorkbook

For Each bk In dic_bk.keys

Set wbPP = Workbooks.Open(bk): Set shtPP = wbPP.Sheets(1): shtPPName = shtPP.Name

If shtPP.Range("A1").MergeCells Then Call 模块2.cancelCellMerge(shtPP) '取消字段行合并

wbPP.Close SaveChanges:=False '需安装2010-AccessDatabaseEngine_X86.exe

sql = "Select * From [" & shtPPName & "$] Where 产品名 Is Not NULL"

sql = "Select Replace(产品名,'辣条','小辣条'),* From (" & sql & ")"

cnnPP.Open cnnStr & bk: rst.Open sql, cnnPP, 1, 3: sql = ""

If rst.RecordCount Then

With sht

.UsedRange.ClearContents

For i = 0 To rst.Fields.Count - 1

.Cells(1, i + 1).Value = rst.Fields(i).Name

Next: .Range("A2").CopyFromRecordset rst:

.UsedRange.Value = .UsedRange.Value '取消文本型数值

End With

End If: rst.Close: cnnPP.Close

Next

End If

'Application.ScreenUpdating = True

End Sub

【请教】

关于上述“WPS无法连接代码所在的xlsm文件”的问题,请知道如何解决的朋友或官方老师,不吝赐教!!

上海
浏览 2368
收藏
3
分享
3 +1
6
+1
全部评论 6
 
方伟
WPS 不支持ACE12.0 链接字符串写成: Str_coon = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName 其实可以静默安装AccessDatabaseEngine X86 2010 来解决 详见:https://club.excelhome.net/thread-1690886-1-1.html
· 黑龙江省
回复
 
柏安
牛人
· 福建省
回复
 
方伟
我的解决方案
· 黑龙江省
2
回复
wils
wils

创作者俱乐部成员

厉害,应该让更多人看到
· 广东省
回复
 
东
【注Bug】,实测WPS无法连接代码所在的 xlsm :(未找到解决办法) cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=2';Data Source=" & ThisWorkbook.FullName Microsoft Access数据库引擎无法打开...已被其他的用户以独占方式打开 遇到 同样的问题,没有解决办法吗
· 江苏省
回复
 
懒人办公
懒人办公

创作者俱乐部成员

期待答案
· 中国
回复