您可以对打开的工作簿中的工作表进行SQL查询(与其他任何工作簿相同)。在这种情况下,查询字符串将如下所示:
SELECT SUM([Training Hours]) AS Myval FROM [data sheet$] WHERE Country = 'USA' AND [Training Status] = 'Completed';
这是代码
Sub TestsqlRequest()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Select Case LCase(Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".")))
Case ".xls"
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='" & ThisWorkbook.FullName & "';Mode=Read;Extended Properties=""Excel 8.0;HDR=YES;"";"
Case ".xlsm"
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source='" & ThisWorkbook.FullName & "';Mode=Read;Extended Properties=""Excel 12.0 Macro;HDR=YES;"";"
End Select
With CreateObject("ADODB.Connection")
.Open strConnection
With .Execute("SELECT SUM([Training Hours]) AS Myval FROM [data sheet$] WHERE Country = 'USA' AND [Training Status] = 'Completed';")
Myval = .Fields("Myval")
End With
.Close
End With
Msg@R_560_2419@ Myval
End Sub
在查询字符串中,带空格的列名称以及包含数据的工作表名称应放在方括号中,后跟$
。不言而喻,查询无法访问数据,在对工作表进行一些更改之后,该数据并未保存到文件中。请注意,Excel 8.0
提供程序不适用于64位Excel版本,请尝试改用Excel 12.0
提供程序(第二个strConnection
分配)。