文章編號: 246335 - 上次校閱: 2007年5月18日 - 版次: 5.0

如何利用自動化將 ADO 資料錄集中的資料傳送至 Excel

系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。

本文曾發行於 CHT246335

您可以藉由自動化 Excel，將 ADO 資料錄集的內容傳送至 Microsoft Excel 工作表。請根據要自動化的 Excel 版本決定要採用的方法。Excel 97、Excel 2000 和 Excel 2002 都具有 CopyFromRecordset 方法，您可以利用這個方法傳送資料錄集至某個範圍。Excel 2000 和 2002 中的 CopyFromRecordset 可以用來複製 DAO 或 ADO 資料錄集。不過，Excel 97 中的 CopyFromRecordset 只支援 DAO 資料錄集。如果要將 ADO 資料錄集傳送至 Excel 97，您可以從資料錄集建立陣列，然後再以該陣列的內容填入範圍。

本文會說明這兩種方法，其中提供的範例程式碼將會告訴您，如何將 ADO 資料錄集傳送至 Excel 97、Excel 2000、Excel 2002、Excel 2003 或 Excel 2007。

回此頁最上方

其他相關資訊

下列提供的程式碼範例說明如何使用 Microsoft Visual Basic 中的自動化，將 ADO 資料錄集複製到 Microsoft Excel 工作表。這個程式碼會先檢查 Excel 的版本。如果偵測到 Excel 2000 或 2002，就會使用 CopyFromRecordset 方法，因為這個方法很有效並需要較少程式碼。然而，如果偵測到 Excel 97 或更舊版本，則會使用 ADO 資料錄集物件的 GetRows 方法，先將資料錄集複製到陣列。然後，將陣列轉置，使資料錄位於第一個維度 (列) 中，而讓欄位位於第二個維度 (欄) 中。接著，透過將陣列指定至儲存格範圍，即可將陣列複製到 Excel 工作表(陣列是以一個步驟的方式複製的，而非在工作表的每個儲存格中以迴圈方式複製)。

此程式碼範例會使用隨附於 Microsoft Office 的 Northwind 範例資料庫。安裝 Microsoft Office 時，如果您選取了預設資料夾，此資料庫就會位於：

\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

如果 Northwind 資料庫位於電腦的不同資料夾中，您必須在下列提供的程式碼中編輯資料庫的路徑。

如果系統上沒有安裝 Northwind 資料庫，您可以使用 Microsoft Office 安裝程式的 [新增/移除] 選項來安裝範例資料庫。

注意當您安裝 2007 Microsoft Office 時，不會安裝 Northwind 資料庫。如果要取得 Northwind 2007，請造訪下列 Microsoft 網站：

http://office.microsoft.com/zh-tw/templates/TC012289971028.aspx (http://office.microsoft.com/zh-tw/templates/TC012289971028.aspx)

回此頁最上方

建立範例的步驟

啟動 Visual Basic 並建立新的標準 EXE 專案。根據預設會建立 Form1。
新增 CommandButton 至 Form1。
按一下 [專案] 功能表中的 [參考]。新增參考至 [Microsoft ActiveX Data Objects 2.1 程式庫]。

將下列程式碼貼至 Form1 的程式碼區段：

Private Sub Command1_Click()
    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object

    
    Dim recArray As Variant
    
    Dim strDB As String
    Dim fldCount As Integer
    Dim recCount As Long
    Dim iCol As Integer
    Dim iRow As Integer
    
    ' Set the string to the path of your Northwind database
    strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb"
  
    ' Open connection to the database
    cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDB & ";"
    
    ''When using the Access 2007 Northwind database
    ''comment the previous code and uncomment the following code.
    'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    '    "Data Source=" & strDB & ";"
        
    ' Open recordset based on Orders table
    rst.Open "Select * From Orders", cnt
    
    ' Create an instance of Excel and add a workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")
  
    ' Display Excel and give user control of Excel's lifetime
    xlApp.Visible = True
    xlApp.UserControl = True
    
    ' Copy field names to the first row of the worksheet
    fldCount = rst.Fields.Count
    For iCol = 1 To fldCount
        xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
    Next
        
    ' Check version of Excel
    If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
        'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset
         
        ' Copy the recordset to the worksheet, starting in cell A2
        xlWs.Cells(2, 1).CopyFromRecordset rst
        'Note: CopyFromRecordset will fail if the recordset
        'contains an OLE object field or array data such
        'as hierarchical recordsets
        
    Else
        'EXCEL 97 or earlier: Use GetRows then copy array to Excel
    
        ' Copy recordset to an array
        recArray = rst.GetRows
        'Note: GetRows returns a 0-based array where the first
        'dimension contains fields and the second dimension
        'contains records. We will transpose this array so that
        'the first dimension contains records, allowing the
        'data to appears properly when copied to Excel
        
        ' Determine number of records

        recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
        

        ' Check the array for contents that are not valid when
        ' copying the array to an Excel worksheet
        For iCol = 0 To fldCount - 1
            For iRow = 0 To recCount - 1
                ' Take care of Date fields
                If IsDate(recArray(iCol, iRow)) Then
                    recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                ' Take care of OLE object fields or array fields
                ElseIf IsArray(recArray(iCol, iRow)) Then
                    recArray(iCol, iRow) = "Array Field"
                End If
            Next iRow 'next record
        Next iCol 'next field
            
        ' Transpose and Copy the array to the worksheet,
        ' starting in cell A2
        xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
            TransposeDim(recArray)
    End If

    ' Auto-fit the column widths and row heights
    xlApp.Selection.CurrentRegion.Columns.AutoFit
    xlApp.Selection.CurrentRegion.Rows.AutoFit

    ' Close ADO objects
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
    
    ' Release Excel references
    Set xlWs = Nothing
    Set xlWb = Nothing

    Set xlApp = Nothing

End Sub


Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)
    
    Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant
    
    Xupper = UBound(v, 2)
    Yupper = UBound(v, 1)
    
    ReDim tempArray(Xupper, Yupper)
    For X = 0 To Xupper
        For Y = 0 To Yupper
            tempArray(X, Y) = v(Y, X)
        Next Y
    Next X
    
    TransposeDim = tempArray


End Function

按下 F5 鍵以執行專案。Form1 隨即會出現。
按一下 Form1 上的 CommandButton，並請注意，Orders 資料表的內容會出現在 Excel 的新活頁簿中。

使用 CopyFromRecordset

基於效率與效能的考量，CopyFromRecordset 會是較佳的方法。由於 Excel 97 的 CopyFromRecordset 只支援 DAO 資料錄集，如果您嘗試在 Excel 97 用 CopyFromRecordset 傳遞 ADO 資料錄集，就會收到下列錯誤：

執行階段錯誤 430：
類別不支援 Automation 或不支援預期的介面。

在程式碼範例中，您可以檢查 Excel 版本，以便不使用 97 版的 CopyFromRecordset，藉此避免發生這個錯誤。

注意使用 CopyFromRecordset 時，請注意，您所使用的 ADO 或 DAO 資料錄集不可包含 OLE 物件欄位或陣列資料 (例如階層式資料錄集)。如果資料錄集包含任何這類欄位，CopyFromRecordset 方法就會失敗並出現下列錯誤：

Run-time error -2147467259: (執行階段錯誤 -2147467259：)
Method CopyFromRecordset of object Range failed. (物件 Range 的 CopyFromRecordset 方法失敗)

使用 GetRows

如果偵測到 Excel 97，請使用 ADO 資料錄集的 GetRows 方法，將資料錄集複製到陣列。如果將 GetRows 傳回的陣列指定到工作表中的儲存格範圍，資料就會跨越各欄，而非隨著每列下移。例如，如果資料錄集具有 2 個欄位和 10 個列，陣列就會顯示為 2 列和 10 欄。因此，將陣列指定到儲存格範圍之前，您需要使用 TransposeDim() 函數來轉置陣列。當您將陣列指定到儲存格範圍時，請注意一些限制：

將陣列指定至 Excel Range 物件時，會有下列限制：

陣列不可包含 OLE 物件欄位或陣列資料 (例如階層式資料錄集)。請注意，程式碼範例會檢查這個條件並顯示「陣列欄位」，讓使用者知道無法在 Excel 中顯示該欄位。
陣列不可包含 1900 年之前的 Date 欄位 (請參閱＜參考＞一節，以取得「Microsoft 知識庫」文件連結)。請注意，程式碼範例會將 Date 欄位格式化為變數字串，以避免發生這個潛在問題。

請注意，將陣列複製到 Excel 工作表之前，可以使用 TransposeDim() 函數來轉置陣列。與其建立自己的函數來轉置陣列，您可以修改範例程式碼，藉以使用 Excel 的 Transpose 函數將陣列指定至儲存格，如下所示：

   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)

如果您決定使用 Excel 的 Transpose 方法 (而非 TransposeDim() 函數) 轉置陣列，請注意 Transpose 方法的下列限制：

陣列不可包含大於 255 個字元的元素。
陣列不可包含 Null 值。
陣列中的元素數目不可超過 5461 個。

將陣列複製到 Excel 工作表時，如果沒有考慮到上述限制，可能就會發生下列其中一個執行階段錯誤：

執行階段錯誤 13：型別不符

執行階段錯誤 5：無效的程序呼叫或引數

執行階段錯誤 1004：Application defined or object defined error (應用程式定義或物件定義錯誤)

回此頁最上方

?考

如需有關將陣列傳遞至各種 Excel 版本的限制的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：

177991? (http://support.microsoft.com/kb/177991/ ) XL:Limitations of Passing Arrays to Excel Using Automation

如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：

146406? (http://support.microsoft.com/kb/146406/ ) How to Retrieve a Table from Access into Excel Using DAO

215965? (http://support.microsoft.com/kb/215965/ ) XL2000:12:00:00 AM Displayed for Dates Earlier Than 1900

243394? (http://support.microsoft.com/kb/243394/ ) HOWTO: Use MFC to Copy a DAO Recordset to Excel with Automation

247412? (http://support.microsoft.com/kb/247412/ ) INFO：將資料從 Visual Basic 傳送至 Excel 的方法

回此頁最上方

這篇文章中的資訊適用於:

Microsoft Office Excel 2007
Microsoft Office Excel 2003
Microsoft Excel 2002 Standard Edition
Microsoft Excel 2000 Standard Edition
Microsoft Excel 97 Standard Edition
Microsoft Visual Basic 5.0 Professional Edition
Microsoft Visual Basic 6.0 Professional Edition
Microsoft Visual Basic 5.0 Enterprise Edition
Microsoft Visual Basic 6.0 Enterprise Edition
Microsoft ActiveX Data Objects 2.0
Microsoft ActiveX Data Objects 2.1
Microsoft ActiveX Data Objects 2.5

回此頁最上方

kbexpertiseinter kbautomation kbhowto KB246335

回此頁最上方

Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。