通过VBA从另一个工作簿复制数据

Question

伙计们，这就是我想要做的，我在做这件事时遇到了一些麻烦。我有一本工作簿，我想从做类似这样的不同文件中收集数据。Do While THAT_DIFFERENT_FILE_SOMEWHERE_ON_MY_HDD.Cells(Rand, 1).Value <> "" And Rand < 65536        then 'I will search if the last row in my main worksheet is in this file... End Loop           如果是，我将退出While循环，如果不是，我将复制所有内容。实际上，这将无法按我希望的方式工作，但我不会在寻找合适的算法时遇到麻烦。我的问题是我不知道如何访问不同的工作簿。

Answer 1

您可能喜欢函数GetInfoFromClosedFile（）

编辑：由于上述链接似乎不再起作用，因此我添加了备用链接1 和备用链接2 +代码：

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _

    wbName As String, wsName As String, cellRef As String) As Variant

Dim arg As String

    GetInfoFromClosedFile = ""

    If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""

    If Dir(wbPath & "" & wbName) = "" Then Exit Function

    arg = "'" & wbPath & "[" & wbName & "]" & _

        wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)

    On Error Resume Next

    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)

End Function

Answer 2

将数据从工作簿复制到另一个工作簿的最佳（也是最简单）方法是使用Excel的对象模型。

Option Explicit

Sub test()

    Dim wb As Workbook, wb2 As Workbook

    Dim ws As Worksheet

    Dim vFile As Variant

    'Set source workbook

    Set wb = ActiveWorkbook

    'Open the target workbook

    vFile = Application.GetOpenFilename("Excel-files,*.xls", _

        1, "Select One File To Open", , False)

    'if the user didn't select a file, exit sub

    If TypeName(vFile) = "Boolean" Then Exit Sub

    Workbooks.Open vFile

    'Set targetworkbook

    Set wb2 = ActiveWorkbook

    'For instance, copy data from a range in the first workbook to another range in the other workbook

    wb2.Worksheets("Sheet2").Range("C3:D4").Value = wb.Worksheets("Sheet1").Range("A1:B2").Value

End Sub