INFO：将数据从 Visual Basic 传输到 Excel 的方法_info文件怎么可以转excel-CSDN博客

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

概要

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

更多信息

将数据传输到 Excel 工作簿最常用的方法是“自动化”功能。“自动化”功能为您提供了指定数据在工作簿中所处位置的最大灵活性，以及对工作簿进行格式设置和在运行时进行各种设置的功能。利用“自动化”功能，您可以使用多种方法传输数据：

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

您还可以使用一些其他方法将数据传输到 Excel，而不必使用“自动化”功能。如果您正在运行服务器端应用程序，这是一种将批量数据处理从客户端移走的好方法。在没有“自动化”功能的情况下，可以使用下列方法来传输数据：

•	将数据传输到制表符分隔或逗号分隔的文本文件，然后 Excel 可以将该文本文件分析为工作表上的单元格
•	使用 ADO 将数据传输到工作表
•	使用动态数据交换 (DDE) 将数据传输到 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


   '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) 可能停止响应，或者您可能收到表明内存不足的错误。Windows 95 和 Windows 98 中的这一限制在下面的知识库文章中进行了讨论：

216400 (http://support.microsoft.com/kb/216400/) PRB：在 Win 95/98 上，进程间的 COM 自动化可能会挂起客户端应用程序

需要再次强调的是，逐单元格传输数据仅适用于传输少量数据。如果需要将大量的数据集传输到 Excel，应考虑下文提供的解决方案之一。

有关自动化 Excel 的更多示例代码，请查看以下 Microsoft 知识库文章：

219151 (http://support.microsoft.com/kb/219151/) 如何在 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 记录集传输到工作表区域

    '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

Excel 97 还提供了一种 CopyFromRecordset 方法，但它只能用于 DAO 记录集。Excel 97 中的 CopyFromRecordset 不支持 ADO。

有关使用 ADO 和 CopyFromRecordset 方法的更多信息，请查看以下 Microsoft 知识库文章：

246335 (http://support.microsoft.com/kb/246335/) 如何使用“自动化”功能将数据从 ADO 记录集传输到 Excel

使用“自动化”功能在工作表上创建 QueryTable

•	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

使用剪贴板

    '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 分析为行和列的带分隔符的文本文件

    '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

如果文本文件具有 .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

有关使用 Visual Basic 应用程序中“文件 I/O”的更多信息，请查看以下 Microsoft 知识库文章：

172267 (http://support.microsoft.com/kb/172267/) 示例：RECEDIT.VBP 使用 Visual Basic 演示文件 I/O

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

1.	在 Excel 中启动一个新工作簿。
2.	将下面的标题添加到 Sheet1 的 A1:B1 单元格： A1:名 B1:姓
3.	将单元格 B1 的格式设置为右对齐。
4.	选择 A1:B1。
5.	在插入菜单上，选择名称，然后选择定义。输入名称 MyTable，并单击确定。
6.	将新工作簿另存为 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

在以此方式将记录添加到该表中后，工作簿中的格式将会保留。在前面的示例中，添加到 B 列中的新字段的格式设置为右对齐。添加到行中的每个记录都将继承它上面的行的格式。

请注意，在将一个记录添加到工作表中的一个或多个单元格时，该记录将会覆盖这些单元格中以前存在的任何数据；也就是说，在添加新记录时，工作表中的行不会“向下推移”。在工作表中设计数据的布局时应考虑到这一点。

有关使用 ADO 访问 Excel 工作簿的其他信息，请查看以下 Microsoft 知识库中文章：

195951 (http://support.microsoft.com/kb/195951/) 如何在 ASP 中使用 ADO 查询和更新 Excel 数据

使用 DDE 将数据传输到 Excel

•	使用 LinkPoke 方法将数据发送到特定的单元格区域， - 或者 -
•	使用 LinkExecute 方法发送 Excel 将执行的命令。

下面的代码示例说明了如何建立 DDE 与 Excel 的会话，以便能够将数据发送到工作表中的单元格，并执行命令。要使用此示例成功建立 DDE 与 LinkTopic Excel|MyBook.xls 的会话，名为 MyBook.xls 的工作簿必须在正运行的 Excel 实例中已打开。

注意：在本示例中， 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

在 Excel 中使用 LinkPoke 时，您需要在行-列 (R1C1) 批注中为 LinkItem 指定区域。如果您要将数据发送到多个单元格，则可以使用列由制表符分隔、行由回车符分隔的字符串。

在使用 LinkExecute 请求 Excel 执行命令时，必须为 Excel 提供 Excel 宏语言 (XLM) 语法的命令。XLM 文档未包括在 Excel 97 版和更高版本中。有关如何获取 XLM 文档的更多信息，请查看以下 Microsoft 知识库文章：

143466 (http://support.microsoft.com/kb/143466/) XL97：Macro97.exe 在联机服务上可用

不建议使用 DDE 解决方案与 Excel 通信。“自动化”功能提供了最大的灵活性，让您能够访问 Excel 提供的更多新功能。