从封闭的工作簿宏中拉取数据 [英] Pulling data from a closed workbook macro

问题描述

我一直在使用宏从封闭的工作簿中提取数据。

上面的图片是主要的工作簿，我想把宏插入。在单元格A1中，您可以看到有一个文件名位置 - 这是关闭的辅助工作簿的位置，我想从中提取数据。

我想宏将采取单元格A1中存在的位置，在封闭的工作簿中复制单元格A1：J5000，然后将其粘贴到从A7开始的工作簿（即A7：J5007）中。文件名位置在A1中的原因是由于这将改变;然而，我希望宏总是采取在A1中显示的位置（例如，如果A1从... \test00218_data.csv更改为... \test00001_data.csv，那么我想要宏从新位置获取数据，test00001）。从那时起，我写了一个宏，我相信会打开所有名为原始数据x的表格，并将所需的数据粘贴到主页面的相应区域;代码如下：

  Sub PullClosedData（）
 
 Dim filePath As String 
 
对于x = 1至1步骤1 
 
 filePath =表（原始数据和x）.Cells（1，1）.Value 
 
工作簿.Open文件名：= filePath 
 
表格（原始数据1）。范围（A7：J2113）。Value = ActiveWorkbook.ActiveSheet.Range（A1：J2107）
下一个x 
 
 End Sub 
  

当我运行这个但是我得到一个运行时错误9（超出范围）。我相信这与脚本中的ActiveWorkbook.ActiveSheet有关，但不确定如何重新写入并避免错误。

解决方案

首先，不要将路径插入到计划覆盖的单元格中。相反，创建一个包含重要输入参数的单独的工作表（参见下面的示例;我正在调用该表System）。

下面的代码将数据从工作簿原始数据1拉到

• 确保您在变量（TargetWb和SourceWb）中正确定义了工作簿。


• 在引用工作表时，请始终在使用多个工作簿时指定它所在的工作簿（例如，TargetWb.ActiveWorksheet，而不仅仅是ActiveWorksheet）

。

  Sub PullClosedData（）
 
 Dim filePath As String 
 Dim SourceWb As Workbook 
 Dim TargetWb As Workbook 
 
设置TargetWb = ActiveWorkbook 
 
 filePath = TargetWb.Sheets（System）。范围（A1 ）.Value 
设置SourceWb = Workbooks.Open（filePath）
 
对于i = 1到3 
 SourceWb.Sheets（Raw Data& i）.Range（A1：J5000）。复制目的地：= TargetWb.Sheets（原始数据& i）.Range（A1：J5000）
下一步i 
 
 SourceWb.Close 
 
 MsgBox全部完成！ 
 
 End Sub 
  

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.

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).

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:

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.

解决方案

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.

• 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)

.

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

这篇关于从封闭的工作簿宏中拉取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！