将数据从 Visual Basic 传输到 Excel 的方法

2025-05-26
适用于: Excel 2010, Office Excel 2007, Office Excel 2003

摘要

本文讨论从 Microsoft Visual Basic 应用程序将数据传输到 Microsoft Excel 的多种方法。本文还介绍了每种方法的优点和缺点，以便你可以选择最适合你的解决方案。

更多信息

最常用于将数据传输到 Excel 工作簿的方法是自动化。自动化为你提供了最大的灵活性，可以指定数据在工作簿中的位置，以及在运行时设置工作簿的格式和进行各种设置。借助自动化，可以使用多种方法来传输数据：

逐个单元格传输数据
将数组中的数据传输到单元格区域
使用 CopyFromRecordset 方法将 ADO 记录集中的数据传输到单元格区域
在 Excel 工作表上创建一个 QueryTable，其中包含 ODBC 或 OLEDB 数据源上的查询结果
将数据传输到剪贴板，然后将剪贴板内容粘贴到 Excel 工作表中

还有一些方法可用于将数据传输到 Excel，这些方法不一定需要自动化。如果运行应用程序服务器端，则这是一种很好的方法，用于从客户端中处理大量数据。以下方法可用于在不使用自动化的情况下传输数据：

将数据传输到制表符或逗号分隔的文本文件，Excel 稍后可以将其分析为工作表上的单元格
使用 ADO 将数据传输到工作表
使用动态数据交换 (DDE) 将数据传输到 Excel

以下部分提供有关其中每个解决方案的更多详细信息。

注意使用 Microsoft Office Excel 2007 时，可以在保存工作簿时使用新的 Excel 2007 工作簿 (*.xlsx) 文件格式。为此，请在以下代码示例中找到以下代码行：

oBook.SaveAs "C:\Book1.xls"

将此代码替换为以下代码行：

oBook.SaveAs "C:\Book1.xlsx"

此外，默认情况下，Northwind 数据库不包括在 Office 2007 中。但是，可以从 Microsoft Office Online 下载 Northwind 数据库。

使用自动化逐个单元格传输数据

使用自动化，可以一次将数据传输到工作表一个单元格：

Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object 
    
'Start a new workbook in Excel    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add
      
'Add data to cells of the first worksheet in the new workbook    
Set oSheet = oBook.Worksheets(1)    
oSheet.Range("A1").Value = "Last Name"    
oSheet.Range("B1").Value = "First Name"    
oSheet.Range("A1:B1").Font.Bold = True    
oSheet.Range("A2").Value = "Doe"    
oSheet.Range("B2").Value = "John"     

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

如果数据量很小，则逐个单元格传输数据可能是一种完全可接受的方法。可以灵活地将数据放在工作簿中的任何位置，并且可以在运行时有条件地设置单元格的格式。但是，如果有大量数据要传输到 Excel 工作簿，则不建议使用此方法。在运行时获取的每个 Range 对象都会产生接口请求，因此以这种方式传输数据的速度可能会很慢。此外，Microsoft Windows 95 和 Windows 98 对接口请求有 64K 限制。如果对接口请求达到或超过此 64k 限制，则自动化服务器 (Excel) 可能会停止响应，或者可能会收到指示内存不足的错误。

再一次，仅对少量数据可以接受逐个单元格传输数据。如果需要将大型数据集传输到 Excel，应考虑稍后介绍的解决方案之一。

有关自动化 Excel 的更多示例代码，请参阅如何从 Visual Basic 自动执行 Microsoft Excel。

使用自动化将数据数组传输到工作表上的区域

一个数据数组可以一次传输到多个单元格区域：

Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object     

'Start a new workbook in Excel    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add     

'Create an array with 3 columns and 100 rows    
Dim DataArray(1 To 100, 1 To 3) As Variant    
Dim r As Integer    
For r = 1 To 100       
   DataArray(r, 1) = "ORD" & Format(r, "0000")       
   DataArray(r, 2) = Rnd() * 1000       
   DataArray(r, 3) = DataArray(r, 2) * 0.7    
Next     

'Add headers to the worksheet on row 1    
Set oSheet = oBook.Worksheets(1)    
oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")     

'Transfer the array to the worksheet starting at cell A2    
oSheet.Range("A2").Resize(100, 3).Value = DataArray        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

如果使用数组而不是逐个单元格传输数据，则可以使用大量数据实现巨大的性能提升。请考虑上述代码中的以下行，该行将数据传输到工作表中的 300 个单元格：

   oSheet.Range("A2").Resize(100, 3).Value = DataArray

此行表示两个接口请求， (一个用于 Range 方法返回的 Range 对象，另一个用于 Resize 方法返回的 Range 对象) 。另一方面，按单元格传输数据单元格需要向 Range 对象请求 300 个接口。只要有可能，就可以从批量传输数据并减少发出的接口请求数中受益。

使用自动化将 ADO 记录集传输到工作表区域

Excel 2000 引入了 CopyFromRecordset 方法，该方法允许将 ADO (或 DAO) 记录集传输到工作表上的区域。以下代码演示了如何使用 CopyFromRecordset 方法自动执行 Excel 2000、Excel 2002 或 Office Excel 2003 以及传输 Northwind 示例数据库中订单表的内容。

'Create a Recordset from all the records in the Orders table    
Dim sNWind As String    
Dim conn As New ADODB.Connection    
Dim rs As ADODB.Recordset    
sNWind = _       
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       sNWind & ";"    
conn.CursorLocation = adUseClient    
Set rs = conn.Execute("Orders", , adCmdTable)        

'Create a new workbook in Excel    
Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add    
Set oSheet = oBook.Worksheets(1)        

'Transfer the data to Excel    
oSheet.Range("A1").CopyFromRecordset rs        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit        
'Close the connection    
rs.Close    
conn.Close

注意如果使用 Office 2007 版本的 Northwind 数据库，则必须替换代码示例中的以下代码行：

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"

将此代码行替换为以下代码行：

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"

Excel 97 还提供 CopyFromRecordset 方法，但只能将其用于 DAO 记录集。使用 Excel 97 的 CopyFromRecordset 不支持 ADO。

有关使用 ADO 和 CopyFromRecordset 方法的详细信息，请参阅如何通过自动化将数据从 ADO 记录集传输到 Excel。

使用自动化在工作表上创建 QueryTable

QueryTable 对象表示从外部数据源返回的数据生成的表。自动执行 Microsoft Excel 时，只需向 OLEDB 或 ODBC 数据源提供连接字符串以及 SQL 字符串即可创建 QueryTable。 Excel 负责生成记录集并将其插入到指定位置的工作表中。使用 QueryTables 比 CopyFromRecordset 方法具有几个优点：

Excel 处理记录集的创建及其在工作表中的位置。
可以使用 QueryTable 保存查询，以便稍后可以刷新查询以获取更新的记录集。
将新的 QueryTable 添加到工作表后，可以指定移动工作表上单元格中已存在的数据以容纳新数据 (请参阅 RefreshStyle 属性了解详细信息) 。

以下代码演示了如何自动执行 Excel 2000、Excel 2002 或 Office Excel 2003，以使用 Northwind 示例数据库中的数据在 Excel 工作表中创建新的 QueryTable：

'Create a new workbook in Excel    
Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add    
Set oSheet = oBook.Worksheets(1)        

'Create the QueryTable    
Dim sNWind As String    
sNWind = _       
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"    
Dim oQryTable As Object    
Set oQryTable = oSheet.QueryTables.Add( _    
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       
sNWind & ";", oSheet.Range("A1"), "Select * from Orders")    oQryTable.RefreshStyle = xlInsertEntireRows    
oQryTable.Refresh False        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

使用剪贴板

Windows 剪贴板还可以用作将数据传输到工作表的机制。若要将数据粘贴到工作表上的多个单元格中，可以复制一个字符串，其中列由制表符分隔，行由回车符分隔。以下代码演示了 Visual Basic 如何使用其剪贴板对象将数据传输到 Excel：

'Copy a string to the clipboard    
Dim sData As String    
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _            & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _            
    & "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"    
Clipboard.Clear     
Clipboard.SetText sData        

'Create a new workbook in Excel    
Dim oExcel As Object    
Dim oBook As Object    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add         

'Paste the data    
oBook.Worksheets(1).Range("A1").Select    
oBook.Worksheets(1).Paste        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

创建 Excel 可以分析为行和列的带分隔符的文本文件

Excel 可以打开制表符或逗号分隔的文件，并将数据正确分析为单元格。如果要将大量数据传输到工作表，同时使用很少的自动化（如果有的话），可以利用此功能。对于客户端-服务器应用程序来说，这可能是一个很好的方法，因为可以在服务器端生成文本文件。然后，可以在客户端使用适当的自动化打开文本文件。

以下代码演示如何从 ADO 记录集创建逗号分隔的文本文件：

'Create a Recordset from all the records in the Orders table    
Dim sNWind As String    
Dim conn As New ADODB.Connection   
Dim rs As ADODB.Recordset    
Dim sData As String    
sNWind = _       
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       sNWind & ";"    
conn.CursorLocation = adUseClient    
Set rs = conn.Execute("Orders", , adCmdTable)        

'Save the recordset as a tab-delimited file    
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)    
Open "C:\Test.txt" For Output As #1    
Print #1, sData    
Close #1         

'Close the connection    
rs.Close    
conn.Close        

'Open the new text file in Excel    
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _       Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus

注意如果使用 Office 2007 版本的 Northwind 数据库，则必须替换代码示例中的以下代码行：

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       
   sNWind & ";"

将此代码行替换为以下代码行：

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _       
   sNWind & ";"

如果文本文件具有 .CSV 扩展名，Excel 将打开文件而不显示文本导入向导，并自动假定该文件是逗号分隔的。同样，如果文件具有 .TXT 扩展名，Excel 会自动使用制表符分隔符分析文件。

在前面的代码示例中，Excel 是使用 Shell 语句启动的，文件的名称用作命令行参数。上一个示例中未使用任何自动化。但是，如果需要，可以使用最少量的自动化打开文本文件并将其保存为 Excel 工作簿格式：

'Create a new instance of Excel    
Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object    
Set oExcel = CreateObject("Excel.Application")            

'Open the text file   
 Set oBook = oExcel.Workbooks.Open("C:\Test.txt")        

'Save as Excel workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal    
oExcel.Quit

使用 ADO 将数据传输到工作表

使用 Microsoft Jet OLE DB 提供程序，可以将记录添加到现有 Excel 工作簿中的表中。 Excel 中的“表”只是具有定义名称的区域。范围的第一行必须包含标题 (或字段名称) ，所有后续行都包含记录。以下步骤演示如何创建包含名为 MyTable 的空表的工作簿。

Excel 97、Excel 2000 和 Excel 2003

在 Excel 中启动新工作簿。
将以下标头添加到 Sheet1 的单元格 A1：B1：

A1：FirstName B1：LastName
将单元格 B1 的格式设置为右对齐。
选择 A1：B1。
在“插入”菜单上，选择“名称”，然后选择“定义”。输入名称“MyTable”，然后单击“确定”。
将新工作簿另存为 C:\Book1.xls 并退出 Excel。

若要使用 ADO 将记录添加到 MyTable，可以使用如下所示的代码：

'Create a new connection object for Book1.xls    
Dim conn As New ADODB.Connection    
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _       
    "Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Bill', 'Brown')"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Joe', 'Thomas')"    
conn.Close

Excel 2007

在 Excel 2007 中，启动新工作簿。
将以下标头添加到 Sheet1 的单元格 A1：B1：

A1：FirstName B1：LastName
将单元格 B1 的格式设置为右对齐。
选择 A1：B1。
在功能区上，单击“ 公式 ”选项卡，然后单击“ 定义名称”。键入名称“MyTable”，然后单击“ 确定”。
将新工作簿另存为 C:\Book1.xlsx，然后退出 Excel。

若要使用 ADO 将记录添加到 MyTable 表，请使用类似于以下代码示例的代码。

'Create a new connection object for Book1.xls    
Dim conn As New ADODB.Connection    
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _       
   "Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Scott', 'Brown')"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Jane', 'Dow')"    
conn.Close

以这种方式向表添加记录时，工作簿中的格式设置会保持。在前面的示例中，添加到 B 列的新字段采用右对齐格式。添加到行的每个记录都从其上方的行中借用格式。

应注意，将记录添加到工作表中的一个或多个单元格时，它会覆盖这些单元格中之前的任何数据;换句话说，添加新记录时，工作表中的行不会“向下推送”。在设计工作表上的数据布局时，应牢记这一点。

注意

安装 Office 2003 Service Pack 2 (SP2) 之后，或者安装 Microsoft 知识库文章904018中包含的 Access 2002 更新之后，使用 ADO 或 DAO 更新 Excel 工作表中的数据的方法在 Access 内的 Visual Basic for Application 环境中不起作用。此方法适用于其他 Office 应用程序的 Visual Basic for Application 环境，例如 Word、Excel 和 Outlook。

有关使用 ADO 访问 Excel 工作簿的详细信息，请参阅如何使用 ADO 从 ASP 查询和更新 Excel 数据。

使用 DDE 将数据传输到 Excel

DDE 是自动化的替代方法，作为与 Excel 通信和传输数据的方式;但是，随着自动化和 COM 的出现，DDE 不再是与其他应用程序通信的首选方法，并且仅应在没有其他解决方案可供你使用时使用。

若要使用 DDE 将数据传输到 Excel，可以使用 LinkPoke 方法将数据戳入特定单元格区域 () ，或者使用 LinkExecute 方法发送 Excel 将执行的命令。

下面的代码示例演示如何与 Excel 建立 DDE 对话，以便你可以将数据戳到工作表上的单元格并执行命令。使用此示例，在 LinkTopic Excel 中成功建立 DDE 对话 |MyBook.xls，名称为 MyBook.xls 的工作簿必须在正在运行的 Excel 实例中打开。

注意

使用 Excel 2007 时，可以使用新的 .xlsx 文件格式来保存工作簿。请确保更新以下代码示例中的文件名。在此示例中，Text1 表示 Visual Basic 窗体上的文本框控件：

'Initiate a DDE communication with Excel    
Text1.LinkMode = 0    
Text1.LinkTopic = "Excel|MyBook.xls"    
Text1.LinkItem = "R1C1:R2C3"    
Text1.LinkMode = 1        

'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls    
Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _                             
             "four" & vbTab & "five" & vbTab & "six"    
Text1.LinkPoke        

'Execute commands to select cell A1 (same as R1C1) and change the font  format 
Text1.LinkExecute "[SELECT(""R1C1"")]"    
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"        

'Terminate the DDE communication    
Text1.LinkMode = 0

将 LinkPoke 与 Excel 配合使用时，可以指定 LinkItem 的行列 (R1C1) 表示法的区域。如果要将数据指向多个单元格，则可以使用字符串，其中列由制表符分隔，行由回车符分隔。

使用 LinkExecute 要求 Excel 执行命令时，必须按照 Excel 宏语言 (XLM) 语法为 Excel 提供命令。 XLM 文档未包含在 Excel 版本 97 及更高版本中。
建议不要使用 DDE 与 Excel 进行通信。自动化提供了最大的灵活性，让你能够更好地访问 Excel 必须提供的新功能。

通过