将数据从 Visual Basic 传输到 Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

如果文本文件有.CSV扩展名，Excel 将打开该文件，而不显示“文本导入向导”，并自动假定该文件以逗号分隔。 同样，如果文件有.TXT扩展名，Excel 会使用选项卡分隔符自动分析文件。

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

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

在 Excel 中启动新工作簿。

将以下标头添加到 Sheet1 的单元格 A1：B1：

A1：FirstName B1：LastName

将单元格 B1 格式设置为右对齐。

选择 A1：B1。

在“插入”菜单上，选择“名称”，然后选择“定义”。 输入名称 MyTable，然后单击“确定”。

将新工作簿另存为C:\Book1.xls并退出 Excel。

若要使用 ADO 将记录添加到 MyTable，可以使用类似于以下内容的代码：

在 Excel 2007 中，启动新工作簿。

将以下标头添加到 Sheet1 的单元格 A1：B1：

A1：FirstName B1：LastName

将单元格 B1 格式设置为右对齐。

选择 A1：B1。

在功能区上，单击“ 公式” 选项卡，然后单击 “定义名称”。 键入名称 MyTable，然后单击 “确定”。

将新工作簿另存为C:\Book1.xlsx，然后退出 Excel。

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

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

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

注意

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

有关更多信息，请参阅下面的文章：

无法在 Office Access 2003 或 Access 2002 中更改、添加或删除链接到 Excel 工作簿的表中的数据

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

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

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

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

注意

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

将 LinkPoke 与 Excel 配合使用时，请为 LinkItem 指定行列 (R1C1) 表示法中的范围。 如果要将数据戳入多个单元格，则可以使用字符串，其中列由选项卡分隔，行按回车符分隔。

使用 LinkExecute 要求 Excel 执行命令时，必须在 Excel 宏语言 (XLM) 的语法中向 Excel 提供该命令。 Excel 版本 97 及更高版本不包含 XLM 文档。 DDE 不是建议与 Excel 通信的解决方案。 自动化提供了最大的灵活性，并使你能够更多地访问 Excel 必须提供的新功能。