excel - 从已关闭的 excel 文件中导入数据

Question

我知道这个问题已经在网站上的几个例子中得到了回答，但是使用这里的任何例子我仍然无法让它们工作。这是我所拥有的，当涉及到 Set rng1 = b1.sht1.Range("A1") 时我遇到了问题.我是编码新手，这看起来合乎逻辑，但无法弄清楚任何帮助将不胜感激。我需要做的就是在一个打开的 excel 文件中打开另一个文件并从 sheet1 复制数据吗？到打开的工作簿中的数据选项卡。

Sub OpenWorkbookToPullData()
    Dim path As String
    path = "S:\GAM\Middle Office\Equity Admin\aa_RawDataFiles\CollateralUtilisation.xls"

    Dim currentWb As Workbook
    Set currentWb = ThisWorkbook

    Dim openWb As Workbook
    Set openWb = Workbooks.Open(path)

    Dim openWs As Worksheet
    Set openWs = openWb.Sheets("Sheet1")

    Dim b1 As Workbook
    Dim b2 As Workbook

    Set b1 = Workbooks("CollateralUtilisation.xls")
    Set b2 = Workbooks("Collateral Management.xlsm")

    Dim sht1 As Worksheet
    Dim sht2 As Worksheet

    Set sht1 = b1.Sheets(1)
    Set sht2 = b2.Sheets("DATA")

    Dim rng1 As Range
    Dim rng2 As Range

    Set rng1 = b1.sht1.Range("A1")
    Set rng2 = sht2.Range("A1")

    rng1 = rng2

    Debug.Print "rng1 holds: "; rng1 & vbNewLine & "rng2 holds: " & rng2


    'openWb.Close (False)
End Sub

Answer 1

改变

Set rng1 = b1.sht1.Range("A1")

至

Set rng1 = sht1.Range("A1")

您不需要 b1 . Sht1已使用 b1 定义在 Set sht1 = b1.Sheets(1)
就像你为 Set rng2 = sht2.Range("A1") 所做的那样

使用 ADO 从已关闭的 Excel 文件中导入。您可能还想查看 Import data from a closed workbook (ADO) using VBA in Microsoft Excel