在VBA中，如何引用另一个（打开或关闭的）工作簿，并提取值？ - Excel 2007 [英] How do I reference to another (open or closed) workbook, and pull values back, in VBA? - Excel 2007

问题描述

基本上，我需要从另一个工作簿中收集公平的数字（可以由UserForm打开，因此位置和名称是可变的）。我需要使用VBA，因为我也需要填充这个数据的图表。我宁愿不必打开另一个工作簿来做到这一点，但如果它更容易，那么它的确可以。

UserForm已经完成，工作正常，我有我需要的代码填充图表，但是我不能让VBA从其他工作簿中拉回数据，并将其分配给我需要的变量。

关于如何让VBA做到这一点的任何想法？非常感谢。

谢谢，

Rob。

解决方案

如果要访问其中的数据，您将不得不以某种方式打开该文件。显然，一种方法是在Excel应用程序实例中打开它，例如： -

（未经测试的代码）

  Dim wbk As Workbook 
设置wbk = Workbooks.Open（C：\myworkbook.xls）
 
'现在你可以操纵数据在工作簿中，无论如何，你想要，例如'
 
 Dim x As Variant 
x = wbk.Worksheets（Sheet1）。Range（A6）。value 
 
调用wbk.Worksheets（Sheet2 ）.Range（A1：G100）。复制
调用ThisWorbook.Worksheets（Target）。Range（A1）。PasteSpecial（xlPasteValues）
 Application.CutCopyMode = False 
 
'etc'
 
调用wbk.Close（False）
  

另一种方法是使用Excel ADODB提供程序打开与文件的连接，然后使用SQL从所需的工作表中选择数据，但是由于您无论如何在Excel中进行工作，所以我不相信有理由这样做，而不仅仅是打开工作簿。请注意，Workbooks.Open（）方法可以使用可选参数将工作簿打开为只读等。

Basically I need to gather a fair few figures from another workbook (Which is found and can be opened by a UserForm, therefore the location and names are variable). I need to use VBA for this as I also need to populate a chart with this data. I would prefer to not have to open the other workbook in order to do it, but if it is far easier then its OK.

The UserForm is done and works fine, and I have the code I need to populate the chart, however I cant get VBA to pull back the data from the other workbook and assign it to the variables that I need it to.

Any ideas on how I can get VBA to do this? It would be greatly appreciated.

Thanks,

Rob.

解决方案

You will have to open the file in one way or another if you want to access the data within it. Obviously, one way is to open it in your Excel application instance, e.g.:-

(untested code)

Dim wbk As Workbook
Set wbk = Workbooks.Open("C:\myworkbook.xls")

' now you can manipulate the data in the workbook anyway you want, e.g. '

Dim x As Variant
x = wbk.Worksheets("Sheet1").Range("A6").Value

Call wbk.Worksheets("Sheet2").Range("A1:G100").Copy
Call ThisWorbook.Worksheets("Target").Range("A1").PasteSpecial(xlPasteValues)
Application.CutCopyMode = False

' etc '

Call wbk.Close(False)

Another way to do it would be to use the Excel ADODB provider to open a connection to the file and then use SQL to select data from the sheet you want, but since you are anyway working from within Excel I don't believe there is any reason to do this rather than just open the workbook. Note that there are optional parameters for the Workbooks.Open() method to open the workbook as read-only, etc.

这篇关于在VBA中，如何引用另一个（打开或关闭的）工作簿，并提取值？ - Excel 2007的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！