通过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, _&nbsp; &nbsp; wbName As String, wsName As String, cellRef As String) As VariantDim arg As String&nbsp; &nbsp; GetInfoFromClosedFile = ""&nbsp; &nbsp; If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""&nbsp; &nbsp; If Dir(wbPath & "" & wbName) = "" Then Exit Function&nbsp; &nbsp; arg = "'" & wbPath & "[" & wbName & "]" & _&nbsp; &nbsp; &nbsp; &nbsp; wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)&nbsp; &nbsp; On Error Resume Next&nbsp; &nbsp; GetInfoFromClosedFile = ExecuteExcel4Macro(arg)End Function

Answer 2

将数据从工作簿复制到另一个工作簿的最佳（也是最简单）方法是使用Excel的对象模型。Option ExplicitSub test()&nbsp; &nbsp; Dim wb As Workbook, wb2 As Workbook&nbsp; &nbsp; Dim ws As Worksheet&nbsp; &nbsp; Dim vFile As Variant&nbsp; &nbsp; 'Set source workbook&nbsp; &nbsp; Set wb = ActiveWorkbook&nbsp; &nbsp; 'Open the target workbook&nbsp; &nbsp; vFile = Application.GetOpenFilename("Excel-files,*.xls", _&nbsp; &nbsp; &nbsp; &nbsp; 1, "Select One File To Open", , False)&nbsp; &nbsp; 'if the user didn't select a file, exit sub&nbsp; &nbsp; If TypeName(vFile) = "Boolean" Then Exit Sub&nbsp; &nbsp; Workbooks.Open vFile&nbsp; &nbsp; 'Set targetworkbook&nbsp; &nbsp; Set wb2 = ActiveWorkbook&nbsp; &nbsp; 'For instance, copy data from a range in the first workbook to another range in the other workbook&nbsp; &nbsp; wb2.Worksheets("Sheet2").Range("C3:D4").Value = wb.Worksheets("Sheet1").Range("A1:B2").ValueEnd Sub