vba - Excel VBA : Can I query external excel without opening the target file?

Question

我想省略下面的“.Open”行，但它说“对象关闭时不允许操作”，有什么方法可以在不打开文件的情况下查询文件吗？

Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & "C:\data.xls" & ";" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With
Set rs = cn.Execute("SQL statement here")

Answer 1

您打开的是连接，而不是文件本身。
因此，它确实是您所需要的。
尝试以下操作并查看文件未打开:

Option Explicit

Public Sub TestMe()

    Dim cn  As Object
    Dim rs  As Object

    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & "C:\DB.xlsm" & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With

    Set rs = cn.Execute("SELECT * FROM [Sheet1$]")

    Do Until rs.EOF
        Debug.Print rs.Fields.Item(1), rs.Fields.Item(2)
        rs.MoveNext
    Loop

    'cn.Close <- you need this only if you work with some crazy guys, who do not
                'understand the open-close principle

End Sub

如果文件在多个用户之间共享，则 ReadOnly可以使用连接权限(只需更改 ConnectionString如图所示):

With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & "C:\DB.xlsm" & ";" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=YES application intent=ReadOnly"";"
    .Open
End With