從 Visual Basic 將資料傳輸至 Excel - Microsoft 365 Apps

其他相關資訊

最常用來將資料傳輸至 Excel 活頁簿的方法是自動化。自動化可讓您有最大的彈性來指定活頁簿中的資料位置，以及在執行時間格式化活頁簿並進行各種設定的能力。透過自動化，您可以使用數種方法來傳輸資料：
依儲存格傳輸資料格
將陣列中的資料傳輸至儲存格範圍
使用 CopyFromRecordset 方法將 ADO 記錄集中的資料傳輸至儲存格範圍
在包含 ODBC 或 OLEDB 資料來源上查詢結果的 Excel 工作表上建立 QueryTable
將資料傳輸到剪貼簿，然後將剪貼簿內容貼到 Excel 工作表中
您也可以使用一些方法，將不一定需要自動化的資料傳輸至 Excel。如果您執行的是應用程式伺服器端，這可以是將大量資料處理離開用戶端的好方法。下列方法可用來傳輸資料，而不需要自動化：
將資料傳輸至索引標籤或以逗號分隔的文字檔，Excel 稍後可以剖析成工作表上的儲存格
使用 ADO 將資料傳輸至工作表
使用動態資料交換 (DDE) 將資料傳輸至 Excel
下列各節提供每個解決方案的詳細資料。
注意當您使用 Microsoft Office Excel 2007 時，您可以在儲存活頁簿時使用新的 Excel 2007 活頁簿 (*.xlsx) 檔案格式。若要這樣做，請在下列程式碼範例中找出下列程式程式碼：
oBook.SaveAs "C:\Book1.xls"
以下列程式程式碼取代此程式碼：
oBook.SaveAs "C:\Book1.xlsx"
此外，根據預設，Northwind 資料庫不會包含在 Office 2007 中。 不過，您可以從 Microsoft Office Online 下載 Northwind 資料庫。
使用自動化依儲存格傳輸資料格
透過自動化，您可以一次將資料傳輸到工作表一個儲存格：
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 在介面要求上具有 64K 的限制。 如果您達到或超過介面要求的 64k 限制，自動化伺服器 (Excel) 可能會停止回應，或您可能會收到指出記憶體不足的錯誤。
再一次，僅針對少量資料，依資料格傳輸資料格是可接受的。 如果您需要將大型資料集傳送至 Excel，您應該考慮稍後呈現的其中一個解決方案。
如需自動化 Excel 的更多範例程式碼，請參閱 如何從 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    
'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) 記錄集傳送到工作表上的範圍。 下列程式碼說明如何使用 CopyFromRecordset 方法，將 Excel 2000、Excel 2002 或 Office Excel 2003 自動化，並在 Northwind 範例資料庫中傳輸 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
注意 如果您使用 Northwind 資料庫的 Office 2007 版本，您必須取代程式碼範例中的下列程式程式碼：
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
以下列程式程式碼取代這一行程式碼：
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
Excel 97 也提供 CopyFromRecordset 方法，但您只能搭配 DAO 記錄集使用。 具有 Excel 97 的 CopyFromRecordset 不支援 ADO。
如需使用 ADO 和 CopyFromRecordset 方法的詳細資訊，請參閱 如何使用自動化將資料從 ADO 記錄集傳輸至 Excel。
使用自動化在工作表上建立 QueryTable
QueryTable 物件代表從外部資料源傳回的資料所建置的資料表。 將 Microsoft Excel 自動化時，只要提供連接字串給 OLEDB 或 ODBC 資料來源以及 SQL 字串，即可建立 QueryTable。 Excel 會負責產生記錄集，並將它插入您指定位置的工作表中。 使用 QueryTables 提供比 CopyFromRecordset 方法多幾項優點：
Excel 會處理建立記錄集及其在工作表中的位置。
查詢可以與 QueryTable 一起儲存，以便稍後重新整理以取得更新的記錄集。
當新的 QueryTable 新增至您的工作表時，您可以指定工作表儲存格中已經存在的資料會移轉以容納新的資料 (如需詳細資料，請參閱 RefreshStyle 屬性以取得詳細資料) 。
下列程式碼示範如何使用 Northwind 範例資料庫中的資料，將 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 剪貼簿也可用來作為將資料傳輸至工作表的機制。 若要將資料貼到工作表上的多個儲存格，您可以複製字串，其中的資料行是以定位字元分隔，而資料列是以歸位字元分隔。 下列程式碼說明 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
建立 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
注意 如果您使用 Northwind 資料庫的 Office 2007 版本，您必須取代程式碼範例中的下列程式程式碼：
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       
   sNWind & ";"
以下列程式程式碼取代這一行程式碼：
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _       
   sNWind & ";"
如果您的文字檔有.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 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
使用 ADO 將資料傳輸至工作表
您可以使用 Microsoft Jet OLE DB 提供者，將記錄新增至現有 Excel 活頁簿中的資料表。 Excel 中的「資料表」只是具有定義的名稱的範圍。 範圍的第一個資料列必須包含標頭 (或功能變數名稱) 而且所有後續的資料列都包含記錄。 下列步驟說明如何使用名為 MyTable 的空白資料表來建立活頁簿。
Excel 97、Excel 2000 和 Excel 2003
在 Excel 中啟動新的活頁簿。
將下列標頭新增至 Sheet1 的儲存格 A1：B1：
A1： FirstName B1： LastName
將儲存格 B1 格式化為靠右對齊。
選取 [A1：B1]。
在 [插入] 功能表上，選擇 [名稱]，然後選取 [定義]。 輸入名稱 MyTable，然後按一下 [確定]。
將新的活頁簿儲存為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
Excel 2007
在 Excel 2007 中，啟動新的活頁簿。
將下列標頭新增至 Sheet1 的儲存格 A1：B1：
A1： FirstName B1： LastName
將儲存格 B1 格式化為靠右對齊。
選取 [A1：B1]。
在功能區上，按一下 [ 公式] 索引卷 標，然後按一下 [ 定義名稱]。 輸入名稱 MyTable，然後按一下 [ 確定]。
將新的活頁簿儲存為C:\Book1.xlsx，然後結束 Excel。
若要使用 ADO 將記錄新增至 MyTable 資料表，請使用類似下列程式碼範例的程式碼。
'Create a new connection object for Book1.xls    
Dim conn As New ADODB.Connection    
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _       
   "Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Scott', 'Brown')"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Jane', 'Dow')"    
conn.Close
當您以這種方式將記錄新增至資料表時，會維護活頁簿中的格式設定。 在上一個範例中，新增至資料行 B 的新欄位會以右對齊格式化。 新增至資料列的每一筆記錄都會從其上方的資料列借用格式。
您應該注意，當記錄新增至工作表中的儲存格或儲存格時，它會覆寫這些儲存格中先前的任何資料;換句話說，新增記錄時，工作表中的資料列不會「向下推送」。 當您在工作表上設計資料的配置時，應該記住這一點。
在安裝 Office 2003 Service Pack 2 (SP2) 或安裝 Microsoft 知識庫文章904018所包含的 Access 2002 更新之後，使用 ADO 或 DAO 更新 Excel 工作表中資料的方法，在 Access 內的 Visual Basic for Application 環境中無法運作。 方法可在 Visual Basic for Application 環境中從其他 Office 應用程式運作良好，例如 Word、Excel 和 Outlook。
如需詳細資訊，請參閱下列文章：
您無法在 Office Access 2003 或 Access 2002 中連結至 Excel 活頁簿的資料表中變更、新增或刪除資料
如需使用 ADO 存取 Excel 活頁簿的詳細資訊，請參閱 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 的 DDE 交談|MyBook.xls，名稱MyBook.xls的活頁簿必須在執行中的 Excel 實例中開啟。
當您使用 Excel 2007 時，可以使用新的.xlsx檔案格式來儲存活頁簿。 請確定您已在下列程式碼範例中更新檔案名。 在此範例中，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 宏語言 (XLM) 的語法為 Excel 提供命令。 XLM 檔不包含在 Excel 97 版和更新版本中。

DDE 不是與 Excel 通訊的建議解決方案。 自動化提供最大的彈性，並可讓您更深入地存取 Excel 必須提供的新功能。