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

问题描述

我们需要编写一个带有VBA代码的Excel电子表格;代码读取并对第一个工作表中的数据执行操作。

用户将收到包含数据但不包含VBA代码的电子表格。我们需要能够将数据从包含数据的电子表格导入包含VBA代码的电子表格。包含数据的工作表与包含数据的电子表格的工作表具有相同的列格式和数据类型。

理想情况下，您将打开包含VBA代码的电子表格，提供了一个UI，允许用户导航到包含数据的电子表格，单击确定，并导入数据。

您将如何去做？必须在Excel电子表格中使用VBA。

非常感谢。

解决方案>

这应该让你开始：
在你自己的Excel工作簿中使用VBA，让它提示用户他们的数据文件的文件名，
然后只是把这个固定范围复制到你的目标工作簿（这可能是与您的宏启用的工作簿相同的工作簿，或第三个工作簿）。
这是一个快速的vba示例，如何工作：

 '获取客户工作簿... 
 Dim customerBook As Workbook 
 Dim filter As String 
 Dim caption As String 
 Dim customerFilename As String 
 Dim customerWorkbook As Workbook 
 Dim targetWorkbook As Workbook 
 
'做出弱点假设活动工作簿是目标
设置targetWorkbook = Application.ActiveWorkbook 
 
'获取客户工作簿
 filter =文本文件（* .xlsx），* 
 caption =请选择一个输入文件
 customerFilename = Application.GetOpenFilename（filter，，caption）
 
设置customerWorkbook = Application.Workbooks.Open（customerFilename） 
 
'假设范围是sheet1中的A1  -  C10 
'从客户到目标工作簿复制数据
 Dim targetSheet As Worksheet 
设置targetSheet = targetWorkbook.Worksheets（1） 
 Dim sourceSheet As Worksheet 
设置sourceSheet = customerWorkbook.Worksheets（1）
 
 tar getSheet.Range（A1，C10）Value = sourceSheet.Range（A1，C10）值
 
'关闭客户工作簿
 customerWorkbook.Close 
  

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.

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.

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.

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

Many thanks.

解决方案

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屋！