将 Excel 电子表格数据导入另一个包含 VBA 的 Excel 电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7876178/
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
Importing Excel spreadsheet data into another Excel spreadsheet containing VBA
提问by Anthony
We need to write an Excel spreadsheet with VBA code in it; the code reads and performs operations on the data in the first worksheet.
我们需要编写一个包含 VBA 代码的 Excel 电子表格；该代码读取第一个工作表中的数据并对其执行操作。
The user will be receiving spreadsheets containing data but that do not contain the VBA code. We need to be able to import the data from the spreadsheets containing the data into the spreadsheet containing the VBA code automatically. The worksheets containing the data have the same column format and datatypes as the worksheet of the spreadsheet containing the data.
用户将收到包含数据但不包含 VBA 代码的电子表格。我们需要能够将包含数据的电子表格中的数据自动导入包含 VBA 代码的电子表格中。包含数据的工作表与包含数据的电子表格的工作表具有相同的列格式和数据类型。
Ideally, you would open the spreadsheet containing the VBA code, be presented with a UI allowing the user to navigate to the spreadsheet containing the data, click OK and the data will be imported.
理想情况下，您将打开包含 VBA 代码的电子表格，显示一个 UI，允许用户导航到包含数据的电子表格，单击“确定”，数据将被导入。
How would you go about doing this? It has to be done using VBA in Excel spreadsheets.
你会怎么做呢？必须在 Excel 电子表格中使用 VBA 来完成。
Many thanks.
非常感谢。
回答by jdh
This should get you started: Using VBA in your own Excel workbook, have it prompt the user for the filename of their data file, then just copy that fixed range into your target workbook (that could be either the same workbook as your macro enabled one, or a third workbook). Here's a quick vba example of how that works:
这应该让您开始：在您自己的 Excel 工作簿中使用 VBA，让它提示用户输入其数据文件的文件名，然后将该固定范围复制到您的目标工作簿中（可以是与启用宏的工作簿相同的工作簿） ，或第三本工作簿）。这是一个关于它如何工作的快速 vba 示例：
' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
targetSheet.Range("A1", "C10").Value = sourceSheet.Range("A1", "C10").Value
' Close customer workbook
customerWorkbook.Close
回答by KumaraPush
Data can be pulled into an excel from another excel through Workbook method or External reference or through Data Import facility.
数据可以通过工作簿方法或外部引用或通过数据导入工具从另一个 excel 中提取到一个 excel 中。
If you want to read or even if you want to update another excel workbook, these methods can be used. We may not depend only on VBA for this.
如果你想阅读甚至你想更新另一个excel工作簿，这些方法都可以使用。为此，我们可能不仅仅依赖 VBA。
For more info on these techniques, please click here to refer the article
