在vba中的关联数组，例如php

您可以对打开的工作簿中的工作表进行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    MsgBox MyvalEnd Sub

在查询字符串中，带空格的列名称以及包含数据的工作表名称应放在方括号中，后跟

$

Excel8.0

Excel 12.0

strConnection