INFO：將數據從 Visual Basic 傳輸到 Excel 的方法
適用于
概要
本文介紹將數據從 Microsoft Visual Basic 應用程序傳輸到 Microsoft Excel 的多種方法。本文還介紹了每種方法的優(yōu)缺點(diǎn)，這樣您可以選擇最適合您的解決方案。
更多信息
將數據傳輸到 Excel 工作簿最常用的方法是“自動(dòng)化”功能。“自動(dòng)化”功能為您提供了指定數據在工作簿中所處位置的最大的靈活性，以及對工作簿進(jìn)行格式設置和在運行時(shí)進(jìn)行各種設置的功能。利用“自動(dòng)化”功能，您可以使用多種方法傳輸數據：
逐單元格傳輸數據
將數組中的數據傳輸到單元格區域
使用 CopyFromRecordset 方法向單元格區域傳輸 ADO 記錄集中的數據
在 Excel 工作表上創(chuàng )建一個(gè) QueryTable，它包含對 ODBC 或 OLEDB 數據源進(jìn)行查詢(xún)的結果。
將數據傳輸到剪貼板，然后將剪貼板內容粘貼到 Excel 工作表中。
您還可以使用一些其他方法將數據傳輸到 Excel，而不必使用“自動(dòng)化”功能。如果您正在運行服務(wù)器端應用程序，這是一種將批量數據處理從客戶(hù)端移走的好方法。在沒(méi)有“自動(dòng)化”功能的情況下，可以使用下列方法來(lái)傳輸數據：
將數據傳輸到制表符分隔或逗號分隔的文本文件，然后 Excel 可以將該文本文件分析為工作表上的單元格
使用 ADO 將數據傳輸到工作表
使用動(dòng)態(tài)數據交換 (DDE) 將數據傳輸到 Excel
下面的部分提供了每種解決方案的詳細信息。
使用“自動(dòng)化”功能逐單元格傳輸數據
利用“自動(dòng)化”功能，您可以逐單元格地向工作表傳輸數據：
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
如果數據量較少，逐單元格傳輸數據是一種完全可以接受的方法。您可以靈活地將數據放到工作簿中的任何位置，并可以在運行時(shí)根據條件對單元格進(jìn)行格式設置。不過(guò)，如果需要向 Excel 工作簿傳輸大量數據，則不建議您使用此方法。您在運行時(shí)獲取的每一個(gè) Range 對象都會(huì )產(chǎn)生一個(gè)接口請求；因此，以這種方式傳輸數據速度較慢。另外，Microsoft Windows 95 和 Windows 98 在接口請求上有 64K 限制。如果在接口請求上達到或超過(guò)這一 64K 限制，自動(dòng)化服務(wù)器 (Excel) 可能停止響應，或者您可能收到表明內存不足的錯誤。Windows 95 和 Windows 98 中的這一限制在下面的知識庫文章中進(jìn)行了討論：
216400 PRB:Cross-Process COM Automation Can Hang Client Application on Win95/98
需要再次強調的是，逐單元格傳輸數據僅適用于傳輸少量數據。如果需要將大量的數據集傳輸到 Excel，應考慮下文提供的解決方案之一。
有關(guān)自動(dòng)化 Excel 的更多示例代碼，請參見(jiàn)以下 Microsoft 知識庫文章：
219151 HOWTO：在 Visual Basic 中自動(dòng)運行 Microsoft Excel
使用“自動(dòng)化”功能將數據數組傳輸到工作表上的區域
一次可以將一個(gè)數據數組傳輸到多個(gè)單元格區域：
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
如果您使用數組傳輸數據而不是逐單元格傳輸數據，則在傳輸大量數據時(shí)，傳輸性能會(huì )大大增強。請注意上述代碼中的以下行，該行將數據傳輸到工作表中的 300 個(gè)單元格：
oSheet.Range("A2").Resize(100, 3).Value = DataArray
此行表示兩個(gè)接口請求（一個(gè)用于 Range 方法返回的 Range 對象，另一個(gè)用于 Resize 方法返回的 Range 對象）。另一方面，逐單元格傳輸數據需要請求指向 Range 對象的 300 個(gè)接口。只要有可能，您就可以從批量傳輸數據以及減少所發(fā)出的接口請求的數量中受益。
使用“自動(dòng)化”功能將 ADO 記錄集傳輸到工作表區域
Excel 2000 引入了 CopyFromRecordset 方法，使您能夠將 ADO（或 DAO）記錄集傳輸到工作表上的某個(gè)區域。下面的代碼說(shuō)明了如何自動(dòng)化 Excel 2000、Excel 2002 或 Office Excel 2003，以及使用 CopyFromRecordset 方法傳輸羅斯文示例數據庫中訂單表的內容。
‘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。
有關(guān)使用 ADO 和 CopyFromRecordset 方法的更多信息，請參見(jiàn)以下 Microsoft 知識庫文章：
246335 HOWTO：使用“自動(dòng)化”功能將數據從 ADO 記錄集傳輸到 Excel
使用“自動(dòng)化”功能在工作表上創(chuàng )建 QueryTable
QueryTable 對象代表由外部數據源返回的數據構建的表。當您自動(dòng)運行 Microsoft Excel 時(shí)，只須提供指向 OLEDB 或 ODBC 數據源的連接字符串和 SQL 字符串就可以創(chuàng )建 QueryTable。Excel 假定能夠生成記錄集，并負責將其插入工作表中您指定的位置。使用 QueryTables 可提供優(yōu)于 CopyFromRecordset 方法的多種優(yōu)點(diǎn)：
Excel 處理記錄集的創(chuàng )建并將其放置到工作表中。
查詢(xún)可以保存在 QueryTable 中，以便在以后能夠刷新，以獲取更新的記錄集。
當向工作表中添加新的 QueryTable 時(shí)，可以指定將工作表上的單元格中已經(jīng)存在的數據移位，以便放置新數據（有關(guān)詳細信息，請查看 RefreshStyle 屬性）。
下面的代碼演示了如何自動(dòng)運行 Excel 2000、Excel 2002 或 Office Excel 2003，以便使用羅斯文示例數據庫中的數據在 Excel 工作表中創(chuàng )建新的 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 剪貼板還可以用作將數據傳輸到工作表的一種機制。要將數據粘貼到工作表上的多個(gè)單元格中，可以復制列由制表符分隔、行由回車(chē)符分隔的字符串。下面的代碼說(shuō)明了 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
創(chuàng )建可由 Excel 分析為行和列的帶分隔符的文本文件
Excel 可以打開(kāi)由制表符或逗號分隔的文件并正確地將數據分析為單元格。當您希望向工作表傳輸大量數據而只使用少量“自動(dòng)化”功能（如果有）時(shí)，可以使用此功能。這對于客戶(hù)端-服務(wù)器應用程序而言可能是一種好方法，因為文本文件可以在服務(wù)器端生成。然后，可以在客戶(hù)端根據需要使用“自動(dòng)化”功能打開(kāi)文本文件。
下面的代碼說(shuō)明了如何從 ADO 記錄集創(chuàng )建逗號分隔的文本文件：
‘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 將打開(kāi)該文件，而不顯示“文本導入向導”，并自動(dòng)假定該文件是逗號分隔文件。類(lèi)似地，如果文件具有 .TXT 擴展名，Excel 將自動(dòng)使用制表符分析此文件。
在前面的代碼示例中，Excel 使用 Shell 語(yǔ)句啟動(dòng)，文件名用作命令行參數。前面的示例中沒(méi)有使用“自動(dòng)化”功能。不過(guò)，如果希望，您可以使用最少量的“自動(dòng)化”功能打開(kāi)文本文件，并以 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
有關(guān)使用 Visual Basic 應用程序中“文件 I/O”的更多信息，請參見(jiàn)以下 Microsoft 知識庫中文章：
172267 RECEDIT.VBP Demonstrates File I/O in Visual Basic
下面的文章還討論并提供了控制 Visual Basic for Applications 中的“文件 I/O”的示例代碼：
File Access with Visual Basic for Applications（Visual Basic for Applications 中的文件訪(fǎng)問(wèn)）
使用 ADO 將數據傳輸到工作表
使用 Microsoft Jet OLE DB 提供程序，您可以將記錄添加到現有 Excel 工作簿的一個(gè)表中。Excel 中的“表”僅是帶有定義名稱(chēng)的一個(gè)區域。區域中的第一行必須包含標題（或字段名），而且所有后續行都包含記錄。下列步驟說(shuō)明了如何使用名為 MyTable 的空表創(chuàng )建工作簿：
在 Excel 中啟動(dòng)一個(gè)新工作簿。
將下面的標題添加到 Sheet1 中的 A1:B1 單元格：
A1:FirstName B1:LastName
將單元格 B1 的格式設置為右對齊。
選擇 A1:B1。
在插入菜單上，選擇名稱(chēng)，然后選擇定義。輸入名稱(chēng) MyTable，并單擊確定。
將新工作簿另存為 C:\Book1.xls 并退出 Excel。
要使用 ADO 將記錄添加到 MyTable 中，您可以使用與以下內容類(lèi)似的代碼：
‘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
在以此方式將記錄添加到該表中后，工作簿中的格式將會(huì )保留。在前面的示例中，添加到 B 列中的新字段的格式設置為右對齊。添加到行中的每個(gè)記錄都將繼承它前面的行的格式。
請注意，在將一個(gè)記錄添加到工作表中的一個(gè)或多個(gè)單元格時(shí)，該記錄將會(huì )覆蓋這些單元格中以前存在的任何數據；也就是說(shuō)，在添加新記錄時(shí)，工作表中的行不會(huì )“向下推移”。在工作表中設計數據的布局時(shí)應考慮到這一點(diǎn)。
有關(guān)使用 ADO 訪(fǎng)問(wèn) Excel 工作簿的其他信息，請參見(jiàn)以下 Microsoft 知識庫中文章：
195951 HOWTO:Query and Update Excel Data Using ADO From ASP
使用 DDE 將數據傳輸到 Excel
在與 Excel 通信和傳輸數據方面，DDE 是“自動(dòng)化”的替代方法；不過(guò)，由于“自動(dòng)化”和 COM 的出現，DDE 不再是與其他應用程序通信的首選方法，而且應僅在沒(méi)有其他可用的解決方案時(shí)才使用該方法。
要使用 DDE 將數據傳輸到 Excel，您可以：
使用 LinkPoke 方法將數據發(fā)送到特定的單元格區域，
- 或 -
使用 LinkExecute 方法發(fā)送 Excel 將執行的命令。
下面的代碼示例說(shuō)明了如何建立 DDE 與 Excel 的會(huì )話(huà)，以便能夠將數據發(fā)送到工作表中的單元格，并執行命令。要使用此示例成功建立 DDE 與 LinkTopic Excel|MyBook.xls 的會(huì )話(huà)，名為 MyBook.xls 的工作簿必須在正運行的 Excel 實(shí)例中已打開(kāi)。
注意：在此示例中，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 時(shí)，您需要在行-列 (R1C1) 批注中為 LinkItem 指定區域。如果您要將數據發(fā)送到多個(gè)單元格，則可以使用列由制表符分隔、行由回車(chē)符分隔的字符串。
在使用 LinkExecute 請求 Excel 執行命令時(shí)，必須為 Excel 提供 Excel 宏語(yǔ)言 (XLM) 語(yǔ)法的命令。XLM 文檔未包括在 Excel 97 版和更高版本中。有關(guān)如何獲取 XLM 文檔的更多信息，請參見(jiàn)以下 Microsoft 知識庫文章：
143466 Download the Excel 97 Macro Functions Help File for XLM Documentation
不建議使用 DDE 解決方案與 Excel 通信。“自動(dòng)化”功能提供了最大的靈活性，讓您能夠訪(fǎng)問(wèn) Excel 提供的更多新功能。
參考
有關(guān)其他信息，請單擊下面的文章編號，以查看 Microsoft 知識庫中相應的文章：
306022 HOW TO：使用 Visual Basic .NET 向 Excel 工作簿傳輸數據
