將資料從 Visual Basic 傳輸到 Excel

將資料從 Visual Basic 傳輸到 Excel 的方法適用於
概要
本文介紹將資料從 Microsoft Visual Basic 應用程式傳輸到 Microsoft Excel 的多種方法。本文還介紹了每種

方法的優缺點，這樣您可以選擇最適合您的解決方案。
更多資訊
將資料傳輸到 Excel 工作簿最常用的方法是“自動化”功能。“自動化”功能為您提供了指定資料在工作簿中所

處位置的最大的靈活性，以及對工作簿進行格式設定和在執行時進行各種設定的功能。利用“自動化”功能，您可

以使用多種方法傳輸資料：
a.. 逐單元格傳輸資料
b.. 將陣列中的資料傳輸到單元格區域
c.. 使用 CopyFromRecordset 方法向單元格區域傳輸 ADO 記錄集中的資料
d.. 在 Excel 工作表上建立一個 QueryTable，它包含對 ODBC 或 OLEDB 資料來源進行查詢的結果。
e.. 將資料傳輸到剪貼簿，然後將剪貼簿內容貼上到 Excel 工作表中。
您還可以使用一些其他方法將資料傳輸到 Excel，而不必使用“自動化”功能。如果您正在執行伺服器端應用程式

，這是一種將批次資料處理從客戶端移走的好方法。在沒有“自動化”功能的情況下，可以使用下列方法來傳輸數

據：
a.. 將資料傳輸到製表符分隔或逗號分隔的文字檔案，然後 Excel 可以將該文字檔案分析為工作表上的單元格
b.. 使用 ADO 將資料傳輸到工作表
c.. 使用動態資料交換 (DDE) 將資料傳輸到 Excel
下面的部分提供了每種解決方案的詳細資訊。
使用“自動化”功能逐單元格傳輸資料
利用“自動化”功能，您可以逐單元格地向工作表傳輸資料：
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

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

   'Add data to cells of the first worksheet in the new workbook
   Set Sheet = 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 PRB:Cross-Process COM Automation Can Hang Client Application on Win95/98

需要再次強調的是，逐單元格傳輸資料僅適用於傳輸少量資料。如果需要將大量的資料集傳輸到 Excel，應考慮下

文提供的解決方案之一。

有關自動化 Excel 的更多示例程式碼，請參見以下 Microsoft 知識庫文章：
219151 HOWTO：在 Visual Basic 中自動執行 Microsoft Excel

使用“自動化”功能將資料陣列傳輸到工作表上的區域
一次可以將一個資料陣列傳輸到多個單元格區域：
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Start a new workbook in Excel
   Set Excel = CreateObject("Excel.Application")
   Set Book = 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 Sheet = 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）記錄集傳輸到工作表上的某個區域。下

面的程式碼說明了如何自動化 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 Excel = CreateObject("Excel.Application")
   Set Book = oExcel.Workbooks.Add
   Set Sheet = 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.CloseExcel
97 還提供了一種 CopyFromRecordset 方法，但它只能用於 DAO 記錄集。Excel 97 中的 CopyFromRecordset 不

支援 ADO。

有關使用 ADO 和 CopyFromRecordset 方法的更多資訊，請參見以下 Microsoft 知識庫文章：
246335 HOWTO：使用“自動化”功能將資料從 ADO 記錄集傳輸到 Excel

使用“自動化”功能在工作表上建立 QueryTable
QueryTable 物件代表由外部資料來源返回的資料構建的表。當您自動執行 Microsoft Excel 時，只須提供指向

OLEDB 或 ODBC 資料來源的連線字串和 SQL 字串就可以建立 QueryTable。Excel 假定能夠生成記錄集，並負責

將其插入工作表中您指定的位置。使用 QueryTables 可提供優於 CopyFromRecordset 方法的多種優點：
a.. Excel 處理記錄集的建立並將其放置到工作表中。
b.. 查詢可以儲存在 QueryTable 中，以便在以後能夠重新整理，以獲取更新的記錄集。
c.. 當向工作表中新增新的 QueryTable 時，可以指定將工作表上的單元格中已經存在的資料移位，以便放置新

資料（有關詳細資訊，請檢視 RefreshStyle. 屬性）。
下面的程式碼演示瞭如何自動執行 Excel 2000、Excel 2002 或 Office Excel 2003，以便使用羅斯文示例資料庫中

的資料在 Excel 工作表中建立新的 QueryTable：
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set Excel = CreateObject("Excel.Application")
   Set Book = oExcel.Workbooks.Add
   Set Sheet = 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 QryTable = 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 Excel = CreateObject("Excel.Application")
   Set Book = 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
如果文字檔案具有 .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 Excel = CreateObject("Excel.Application")
      
   'Open the text file
   Set Book = 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 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 將資料傳輸到工作表
使用 Microsoft Jet OLE DB 提供程式，您可以將記錄新增到現有 Excel 工作簿的一個表中。Excel 中的“表”

僅是帶有定義名稱的一個區域。區域中的第一行必須包含標題（或欄位名），而且所有後續行都包含記錄。下列步

驟說明了如何使用名為 MyTable 的空表建立工作簿：
1.. 在 Excel 中啟動一個新工作簿。
2.. 將下面的標題新增到 Sheet1 中的 A1:B1 單元格：

A1:FirstName B1:LastName
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 HOWTO:Query and Update Excel Data Using ADO From ASP

使用 DDE 將資料傳輸到 Excel
在與 Excel 通訊和傳輸資料方面，DDE 是“自動化”的替代方法；不過，由於“自動化”和 COM 的出現，DDE 不

再是與其他應用程式通訊的首選方法，而且應僅在沒有其他可用的解決方案時才使用該方法。

要使用 DDE 將資料傳輸到 Excel，您可以：

a.. 使用 LinkPoke 方法將資料傳送到特定的單元格區域，

- 或 -
b.. 使用 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 Download the Excel 97 Macro Functions Help File for XLM Documentation

不建議使用 DDE 解決方案與 Excel 通訊。“自動化”功能提供了最大的靈活性，讓您能夠訪問 Excel 提供的更

多新功能。
參考
有關其他資訊，請單擊下面的文章編號，以檢視 Microsoft 知識庫中相應的文章：
306022 HOW TO：使用 Visual Basic .NET 向 Excel 工作簿傳輸資料

這篇文章中的資訊適用於:
a.. Microsoft Office Excel 2003
b.. Microsoft Excel 2002
c.. Microsoft Excel 2000
d.. Microsoft Excel 97 for Windows
e.. Microsoft Visual Basic for Applications 5.0
f.. Microsoft Visual Basic for Applications 6.0
      最近更新: 2004-4-26 (5.0)
      關鍵字: kbAutomation kbDDE kbinfo KB247412 kbAudDeveloper

Microsoft和/或其各供應商對於為任何目的而在本伺服器上釋出的檔案及有關圖形所含資訊的適用性，不作任何聲

明。 所有該等檔案及有關圖形均"依樣"提供，而不帶任何性質的保證。Microsoft和/或其各供應商特此宣告，對

所有與該等資訊有關的保證和條件不負任何責任，該等保證和條件包括關於適銷性、符合特定用途、所有權和非侵

權的所有默示保證和條件。在任何情況下，在由於使用或執行本伺服器上的資訊所引起的或與該等使用或執行有關

的訴訟中，Microsoft和/或其各供應商就因喪失使用、資料或利潤所導致的任何特別的、間接的或衍生性的損失或

任何種類的損失，均不負任何責任，無論該等訴訟是合同之訴、疏忽或其它侵權行為之訴。
---------------------
Best Regards
HI1/Apple Jia
Tel:68251233 ext:6536
Zero Defected