将 Excel 电子表格数据导入另一个包含 VBA 的 Excel 电子表格 [英] Importing Excel spreadsheet data into another Excel spreadsheet containing VBA

问题描述

我们需要编写一个包含 VBA 代码的 Excel 电子表格；该代码读取第一个工作表中的数据并对其执行操作.

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 代码的电子表格.我们需要能够将包含数据的电子表格中的数据自动导入包含 VBA 代码的电子表格中.包含数据的工作表与包含数据的电子表格的工作表具有相同的列格式和数据类型.

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 代码的电子表格，显示一个 UI，允许用户导航到包含数据的电子表格，单击确定"，数据将被导入.

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.

你会怎么做?必须在 Excel 电子表格中使用 VBA 来完成.

How would you go about doing this? It has to be done using VBA in Excel spreadsheets.

非常感谢.

推荐答案

这应该会让你开始:在您自己的 Excel 工作簿中使用 VBA，让它提示用户输入其数据文件的文件名，然后只需将该固定范围复制到您的目标工作簿中(可以是与启用宏的工作簿相同的工作簿，也可以是第三个工作簿).这是一个快速的 vba 示例，说明其工作原理:

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:

' 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

这篇关于将 Excel 电子表格数据导入另一个包含 VBA 的 Excel 电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！