vba 从关闭的工作簿宏中提取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24952856/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pulling data from a closed workbook macro
提问by pmcgrath92
I've been having issues concerning pulling data from a closed workbook by using a macro.
我一直在使用宏从封闭的工作簿中提取数据方面遇到问题。
The above image is the main workbook, which I would like to insert the macro into. In cell A1 you can see that there is a filename location - this is the location of the closed secondary workbook which I would like to pull the data from.
上图是主要工作簿，我想在其中插入宏。在单元格 A1 中，您可以看到有一个文件名位置 - 这是我想从中提取数据的已关闭辅助工作簿的位置。
I would like the macro to take the location which is present in cell A1, copy cells A1:J5000 in the closed workbook, and then paste these into this workbook starting in A7 (i.e. A7:J5007). The reason that the filename location is present in A1 is due to the fact that this will be changing; however I would like the macro always to take the location which is shown in A1 (e.g. if A1 were to change from '...\test00218_data.csv' to '...\test00001_data.csv' then I would like the macro to take the data from the new location, test00001).
我希望宏获取单元格 A1 中存在的位置，在关闭的工作簿中复制单元格 A1:J5000，然后将它们粘贴到从 A7 开始的此工作簿中（即 A7:J5007）。文件名位置出现在 A1 中的原因是因为它会发生变化；但是我希望宏始终采用 A1 中显示的位置（例如，如果 A1 要从 '...\test00218_data.csv' 更改为 '...\test00001_data.csv' 然后我希望宏从新位置获取数据，test00001）。
Since then I have written a macro which I believe would open up all the Sheets named "Raw Data x" and paste the required data into the appropriate areas of the primary sheet; the code is as follows:
从那时起，我编写了一个宏，我相信它会打开所有名为“原始数据 x”的工作表，并将所需的数据粘贴到主工作表的适当区域；代码如下：
Sub PullClosedData()
Dim filePath As String
For x = 1 To 1 Step 1
filePath = Sheets("Raw Data " & x).Cells(1, 1).Value
Workbooks.Open Filename:=filePath
Sheets("Raw Data 1").Range("A7:J2113").Value = ActiveWorkbook.ActiveSheet.Range("A1:J2107")
Next x
End Sub
When I run this however I get a Runtime error 9 (out of range). I believe this has something to do with the "ActiveWorkbook.ActiveSheet" part of the script, but am unsure how to re-write this and avoid the error.
但是，当我运行它时，我收到运行时错误 9（超出范围）。我相信这与脚本的“ActiveWorkbook.ActiveSheet”部分有关，但我不确定如何重写它并避免错误。
回答by one angry researcher
First off, do not stick the path into a cell that you plan on overwriting. Instead, create a separate sheet containing vital input parameters (see example below; I'm calling that sheet "System").
首先，不要将路径粘贴到您计划覆盖的单元格中。相反，创建一个包含重要输入参数的单独工作表（参见下面的示例；我称该工作表为“系统”）。
The code below pulls data from the workbooks "Raw Data 1" to "Raw Data 3" from the source book.
下面的代码从源工作簿的“原始数据 1”工作簿中提取数据到“原始数据 3”。
- Make sure you properly define your workbooks in variables (TargetWb and SourceWb).
- When referencing a worksheet, always specify what workbook it is located in when using multiple workbooks (e.g. TargetWb.ActiveWorksheet, not just ActiveWorksheet)
- 确保在变量（TargetWb 和 SourceWb）中正确定义了工作簿。
- 引用工作表时，在使用多个工作簿时始终指定它所在的工作簿（例如 TargetWb.ActiveWorksheet，而不仅仅是 ActiveWorksheet）
.
.
Sub PullClosedData()
Dim filePath As String
Dim SourceWb As Workbook
Dim TargetWb As Workbook
Set TargetWb = ActiveWorkbook
filePath = TargetWb.Sheets("System").Range("A1").Value
Set SourceWb = Workbooks.Open(filePath)
For i = 1 To 3
SourceWb.Sheets("Raw Data " & i).Range("A1:J5000").Copy Destination:=TargetWb.Sheets("Raw Data " & i).Range("A1:J5000")
Next i
SourceWb.Close
MsgBox "All done!"
End Sub
