|
各位大师好!今天我在运行满足2个条件的销售金额的以下代码时,其提示“未检测到64位ACE驱动,请先安装:https://aka.ms/access64”,我下载安装后,仍有提示,请问是什么原因,怎么解决。' 64位兼容声明(必须放在模块顶部)
#If Win64 Then
Private Declare PtrSafe Function CoInitializeEx Lib "ole32" (ByVal pvReserved As LongPtr, ByVal coInit As Long) As Long
Private Const COINIT_MULTITHREADED = &H0
#End If
Sub 计算满足条件的销售金额_ADOSQL_64位兼容版()
On Error GoTo ErrorHandler
#If Win64 Then
CoInitializeEx 0, COINIT_MULTITHREADED ' 初始化COM为多线程模式
#End If
Dim t As Single: t = Timer
Dim conn As Object, cmd As Object, rs As Object
Dim strSQL As String, strPath As String
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("数据源")
' ==== 64位驱动检查 ====
If Dir("C:\Program Files\Microsoft Office\root\vfs\ProgramFilesCommonX64\Microsoft Shared\OFFICE16\ACEOLEDB.DLL") = "" Then
MsgBox "未检测到64位ACE驱动,请先安装:https://aka.ms/access64", vbCritical
Exit Sub
End If
' ==== 数据末行计算 ====
Dim lastRow As Long
lastRow = Application.Max( _
ws.Cells(ws.Rows.Count, "G").End(xlUp).Row, _
ws.Cells(ws.Rows.Count, "J").End(xlUp).Row _
)
If lastRow < 2 Then lastRow = 2
' ==== 64位专用连接字符串 ====
Dim safePath As String
safePath = ThisWorkbook.FullName
safePath = Replace(safePath, "[", "%5B") ' 转义特殊字符
safePath = Replace(safePath, "]", "%5D")
strPath = "Provider=Microsoft.ACE.OLEDB.16.0;" & _
"Data Source=" & safePath & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"""
Set conn = CreateObject("ADODB.Connection")
conn.Open strPath
' ==== 64位优化SQL ====
strSQL = "SELECT SUM(金额) " & _
"FROM [数据源$G1:J" & lastRow & "] " & _
"WHERE 所属区域=? AND 产品类别=?"
Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = conn
.CommandText = strSQL
.Parameters.Append .CreateParameter("所属区域", 200, 1, 255, ws.Range("N5").Value)
.Parameters.Append .CreateParameter("产品类别", 200, 1, 255, ws.Range("O5").Value)
Set rs = .Execute()
End With
' 结果输出
ws.Range("P5").Value = rs.Fields(0).Value
Cleanup:
On Error Resume Next
rs.Close: conn.Close
Set rs = Nothing: Set cmd = Nothing: Set conn = Nothing
MsgBox "64位查询完成,耗时:" & Format(Timer - t, "0.000s")
Exit Sub
ErrorHandler:
MsgBox "64位环境错误 " & Err.Number & ": " & Err.Description, vbCritical
Resume Cleanup
End Sub
|
|