INFO:將資料從 Visual Basic 傳送至 Excel 的方法

文章翻譯 文章翻譯
文章編號: 247412 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

結論

本文會說明將資料從您的 Microsoft Visual Basic 應用程式傳送至 Microsoft Excel 的多種方法。此外,本文也說明每個方法的優缺點,讓您能夠選擇最適用的解決方案。

其他相關資訊

最常用來將資料傳送至 Excel 活頁簿的方法,就是「自動化」功能。「自動化」具有最佳的彈性,可以在活頁簿中指定資料的位置,並且具備將活頁簿格式化,以及在執行階段進行各種設定的功能。有了「自動化」,您就可以使用數種方法來傳送資料:
  • 依儲存格傳送資料
  • 將陣列中的資料傳送至儲存格範圍
  • 使用 CopyFromRecordset 方法將 ADO 資料錄集中的資料傳送至儲存格範圍
  • 在包含 ODBC 或 OLEDB 資料來源的查詢結果的 Excel 工作表上建立 QueryTable
  • 將資料傳送至剪貼簿,再將剪貼簿的內容貼上 Excel 工作表
還有其他不需要「自動化」的方法,可以用來將資料傳送至 Excel。如果您是在伺服器端上執行應用程式,則使用這個方法以避免在用戶端上處理大量的資料,會是個不錯的選擇。下列方法可以讓您用來傳送資料,而不需要「自動化」:
  • 將您的資料傳送至以 Tab 或逗號分隔的文字檔,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 具有 64 K 的介面要求限制。如果您的介面要求達到或超過這個 64 k 的限制,「自動化」伺服器 (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 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 物件)。另一方面,依儲存格傳送資料會需要 300 個 Range 物件的介面要求。可能的話,您可以從大量傳送資料,並且減少產生介面要求的數量中,獲得好處。

使用自動化將 ADO 資料錄集傳送至工作表範圍

Excel 2000 引進的 CopyFromRecordset 方法允許您將 ADO (或 DAO) 資料錄集傳送至工作表上的範圍。下列程式碼示範如何自動化 Excel 2000、Excel 2002 或 Office Excel 2003,並使用 CopyFromRecordset 方法,傳送「北風範例資料庫」中的 Orders 資料表內容:
'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 HOWTO:將 ADO 資料錄集的資料自動傳送到 Excel

使用自動化在工作表上建立 QueryTable

QueryTable 物件代表從外部資料來源傳回的資料所建置的表格。自動化 Microsoft Excel 時,只要將連接字串與 SQL 字串一同提供給 OLEDB 或 ODBC 資料來源,就可以建立 QueryTable。Excel 會負責產生資料錄集,並且將資料錄集插入您在工作表中所指定的位置。透過 CopyFromRecordset 方法使用 QueryTables 具有數項優點:
  • Excel 會負責建立資料錄集,並且安排資料錄集在工作表中的位置。
  • 查詢可以和 QueryTable 一起儲存,如此,以後只要重新整理查詢,就可以取得更新的資料錄集。
  • 當工作表中加入新的 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 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 剪貼簿也可以做為將資料傳送至工作表的機制。如果要將資料貼至工作表上的多個儲存格中,您可以複製以 Tab 字元分隔的欄及由換行字元分隔的列中的字串。下列程式碼示範 Visual Basic 如何使用 Clipboard 物件,將資料傳送至 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

建立 Excel 可以剖析至列及欄的分隔符號文字檔

Excel 能夠開啟以 Tab 或逗號分隔的檔案,並且正確地將其中的資料剖析至儲存格中。當您要傳送大量的資料至工作表 (可能不常使用「自動化」,或是完全不使用) 時,可以好好地利用這個功能。這對主從模式應用程式而言,可能會是個很好的方法,因為文字檔可以在伺服器端建立。然後,您可以在適當的時候使用「自動化」,以便在用戶端開啟文字檔。

下列程式碼示範如何從 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 會自動使用 Tab 分隔符號剖析檔案。

在先前的程式碼範例中,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 SAMPLE: RECEDIT.VBP Demonstrates File I/O in Visual Basic

使用 ADO 將資料傳送至工作表

使用 Microsoft Jet OLE DB 提供者,即可將資料錄集加入現存 Excel 活頁簿中的表格。Excel 中的「表格」是指含有定義名稱的範圍。範圍中的第一列必須包含標題 (或是欄位名稱),並且所有後續的列都會包含記錄。下列步驟示範如何建立含有空白表格且名為我的表格的活頁簿:
  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 How To Query and Update Excel Data Using ADO From ASP

使用 DDE 將資料傳送至 Excel

DDE 是「自動化」的替代方式,可以與 Excel 通訊並傳送資料。不過,隨著「自動化」和 COM 的出現,DDE 不再是與其他應用程式通訊的偏好方法,而且應該只在沒有其他解決方案時使用。

如果要使用 DDE 將資料傳送至 Excel,您可以:
  • 使用 LinkPoke 方法,將資料移至特定的儲存格範圍中,

    - 或 -
  • 使用 LinkExecute 方法傳送 Excel 將會執行的命令。
下列程式碼範例示範如何使用 Excel 建立 DDE 對話,如此您就能夠將資料移至工作表上的儲存格中,並且執行命令。使用這個範例時,為了能在 LinkTopic Excel|MyBook.xls 中順利地建立 DDE 對話,必須先在 Excel 正在執行的執行個體中開啟名為 MyBook.xls 的活頁簿。

注意 在這個範例中,Text1 代表 Visual Basic 表單上的 Text Box 控制項:
'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 時,您可以在 LinkItem 的列 - 欄 (R1C1) 標記中指定範圍。如果您要將資料移至多個儲存格中,可以利用以 Tab 字元分隔的欄及以換行字元分隔的列中的字串。

當您使用 LinkExecute 要求 Excel 執行命令時,必須使用 Excel Macro Language (XLM) 的語法,將命令傳送給 Excel。XLM 文件並未隨附於 Excel 97 及更新的版本中。如需有關如何取得 XLM 文件的詳細資訊,請參閱下列「Microsoft 知識庫」中的文件:
143466 XL97: Macro97.exe File Available on Online Services
DDE 不是與 Excel 通訊的建議解決方案。「自動化」提供最大的彈性,並且可以讓您存取更多 Excel 所提供的新功能。

?考

如需詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
306022 How to transfer data to an Excel workbook by using Visual Basic .NET

屬性

文章編號: 247412 - 上次校閱: 2005年11月17日 - 版次: 6.1
這篇文章中的資訊適用於:
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic for Applications 5.0
  • Microsoft Visual Basic for Applications 6.0
關鍵字:?
kbinfo kbautomation kbdde KB247412
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com