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