读取工作簿而不使用 ADO 打开它

在这个问题之后：无需打开另一个工作簿即可获取值/图表 https://stackoverflow.com/questions/16840573/get-value-charts-in-another-workbooks-without-opening-it

我已经编码了：

Sub test()

Dim oConn As New ADODB.Connection
Dim rst As New ADODB.Recordset

oConn.Provider = "Microsoft.Jet.OLEDB.4.0"
oConn.Properties("Extended Properties").Value = "Excel 8.0"
oConn.Open "C:\Workbook1.xlsm"
rst.Open "SELECT * FROM [A1:A2];", oConn, adOpenStatic

rst.MoveFirst
MsgBox rst.Fields(0)

rst.Close
oConn.Close

End Sub

目前我的目标是获得价值cell A1 of the sheet 1 of workbook1.xlsm.

我遇到了两个问题。

当。。。的时候workbook1没有打开我有一个

Run time error '-214767259 (80004005)': Automation error Unspecified Error on the line      oConn.Open "C:\Workbook1.xlsm`   

这很烦人，因为我想在不打开工作簿的情况下工作。当工作簿打开时它效果很好。

第二个问题：我无法只获取单个单元格值。我试过只输入[A1] in rst.open但它不起作用。如何获得唯一的单元格值及其地址？及其名字？

如果您不介意，我将为您提供一些不同的尝试来获取您的数据。区别在于连接数据库的方式（Excel 表）。但是，您可以将一些重要元素合并到代码中。因此，请检查下面代码中的注释。

Sub Closed_excel_workbook()

    Dim myConnection As String
    Dim myRecordset As ADODB.Recordset
    Dim mySQL As String

'connection string parameters
'CHANGE PATH TO YOUR CLOSED WORKBOOK
    myConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=" & ThisWorkbook.Path & "\Dane\BazaDanych.xlsx;" & _
               "Extended Properties=Excel 12.0"

'here is important, YOU CAN'T MISS SHEET NAME
    mySQL = "SELECT * FROM [ARKUSZ1$a1:a2]"

'different way of getting data from excel sheet
    Set myRecordset = New ADODB.Recordset
    myRecordset.Open mySQL, myConnection, adOpenUnspecified, adLockUnspecified

'let's clear sheet before pasting data
'REMOVE IF NOT NEEDED
    ActiveSheet.Cells.Clear

'HERE WE PASTING DATA WE HAVE RETRIEVED
    ActiveSheet.Range("A2").CopyFromRecordset myRecordset

'OPTIONAL, IF REQUIRED YOU CAN ADD COLUMNS NAMES
    Dim cell As Range, i!
    With ActiveSheet.Range("A1").CurrentRegion
        For i = 0 To myRecordset.Fields.Count - 1
            .Cells(1, i + 1).Value = myRecordset.Fields(i).Name
        Next i
        .EntireColumn.AutoFit
    End With
End Sub