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

Copy data from another Workbook through VBA

提问by Andrei Ion

Guys here's what I want to do and I have a little trouble doing it. I have 1 Workbook where I want to collect data from different files doing something like this.

伙计们，这就是我想做的事情，但我在做这件事时遇到了一些麻烦。我有 1 个工作簿，我想从不同的文件中收集数据，做这样的事情。

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           

If it is I'll quit the WhileLoop, if it's not I'll copy everything. Actually this won't work as I want but I won't have trouble finding the right algorithm.

如果是，我将退出WhileLoop，如果不是，我将复制所有内容。实际上这不会像我想要的那样工作，但我不会很难找到正确的算法。

My problem is that I don't know how to access different workbooks.

我的问题是我不知道如何访问不同的工作簿。

采纳答案by Patrick Honorez

You might like the function GetInfoFromClosedFile()

您可能喜欢函数GetInfoFromClosedFile()

---

---

Edit: Since the above link does not seem to work anymore, I am adding alternate link 1and alternate link 2+ code:

编辑：由于上面的链接似乎不再起作用，我添加了备用链接 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

回答by JMax

The best (and easiest) way to copy data from a workbook to another is to use the object model of Excel.

将数据从一个工作簿复制到另一个工作簿的最佳（也是最简单的）方法是使用 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

回答by Ed Bolton

There's very little reason not to open multiple workbooks in Excel. Key lines of code are:

几乎没有理由不在 Excel 中打开多个工作簿。关键代码行是：

Application.EnableEvents = False
Application.ScreenUpdating = False

...then you won't see anything whilst the code runs, and no code will run that is associated with the opening of the second workbook. Then there are...

...那么在代码运行时您将看不到任何内容，并且不会运行与打开第二个工作簿相关的代码。然后有...

Application.DisplayAlerts = False
Application.Calculation = xlManual

...so as to stop you getting pop-up messages associated with the content of the second file, and to avoid any slow re-calculations. Ensure you set back to True/xlAutomatic at end of your programming

...以免您收到与第二个文件的内容相关的弹出消息，并避免任何缓慢的重新计算。确保在编程结束时重新设置为 True/xlAutomatic

If opening the second workbook is not going to cause performance issues, you may as well do it. In fact, having the second workbook open will make it very beneficial when attempting to debug your code if some of the secondary files do not conform to the expected format

如果打开第二个工作簿不会导致性能问题，您也可以这样做。事实上，如果某些辅助文件不符合预期格式，打开第二个工作簿将在尝试调试代码时非常有用

Here is some expert guidance on using multiple Excel filesthat gives an overview of the different methods available for referencing data

以下是有关使用多个 Excel 文件的一些专家指南，概述了可用于引用数据的不同方法

An extension question would be how to cycle through multiple files contained in the same folder. You can use the Windows folder picker using:

一个扩展问题是如何循环浏览同一文件夹中包含的多个文件。您可以使用 Windows 文件夹选择器：

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
     If .Selected.Items.Count = 1 the InputFolder = .SelectedItems(1)
End With

FName = VBA.Dir(InputFolder)

Do While FName <> ""
'''Do function here
FName = VBA.Dir()
Loop

Hopefully some of the above will be of use

希望上面的一些会有用

回答by jonsca

Are you looking for the syntax to open them:

您是否正在寻找打开它们的语法：

Dim wkbk As Workbook

Set wkbk = Workbooks.Open("C:\MyDirectory\mysheet.xlsx")

Then, you can use wkbk.Sheets(1).Range("3:3")(or whatever you need)

然后，您可以使用wkbk.Sheets(1).Range("3:3")（或您需要的任何东西）

回答by Alex_P

I had the same question but applying the provided solutions changed the file to write in. Once I selected the new excel file, I was also writing in that file and not in my original file. My solution for this issue is below:

我有同样的问题，但应用提供的解决方案更改了要写入的文件。一旦我选择了新的 excel 文件，我也在该文件中而不是在我的原始文件中写入。我对这个问题的解决方案如下：

Sub GetData()

    Dim excelapp As Application
    Dim source As Workbook
    Dim srcSH1 As Worksheet
    Dim sh As Worksheet
    Dim path As String
    Dim nmr As Long
    Dim i As Long

    nmr = 20

    Set excelapp = New Application

    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        .Show
        path = .SelectedItems.Item(1)
    End With

    Set source = excelapp.Workbooks.Open(path)
    Set srcSH1 = source.Worksheets("Sheet1")
    Set sh = Sheets("Sheet1")

    For i = 1 To nmr
        sh.Cells(i, "A").Value = srcSH1.Cells(i, "A").Value
    Next i

End Sub

With excelappa new application will be called. The withblock sets the path for the external file. Finally, I set the external Workbook with sourceand srcSH1as a Worksheet within the external sheet.

随着excelapp一个新的应用程序将被调用。该with块设置外部文件的路径。最后，我设置外部工作簿source和srcSH1作为工作表的外部片内。