如何使用自動化將資料從 ADO 記錄集傳輸至 Excel

發行項
06/20/2023
適用於:

Excel 2007, Excel 2003, Excel 2002, Excel 2000, Excel 97

摘要

您可以自動化 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 工作表。 (陣列會以一個步驟複製，而不是在 worksheet.) 中的每個儲存格中執行迴圈

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

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

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

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

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

Office 模 & 板主題

建立範例的步驟

啟動 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 記錄集，因此如果您嘗試將 ADO 記錄集傳遞至具有 Excel 97 的 CopyFromRecordset，您會收到下列錯誤：

執行階段錯誤 430：類別不支援自動化，或不支援預期的介面。在程式碼範例中，您可以藉由檢查 Excel 的版本來避免此錯誤，這樣您就不會針對 97 版使用 CopyFromRecordset。

注意使用 CopyFromRecordset 時，您應該注意您使用的 ADO 或 DAO 記錄集不能包含 OLE 物件欄位或陣列資料，例如階層式記錄集。如果您在記錄集中包含任一類型的欄位，CopyFromRecordset 方法會失敗，並出現下列錯誤：

執行階段錯誤 -2147467259：物件範圍的方法 CopyFromRecordset 失敗。

使用 GetRows

如果偵測到 Excel 97，請使用 ADO 記錄集的 GetRows 方法，將記錄集複製到陣列中。如果您將 GetRows 傳回的陣列指派給工作表中的儲存格範圍，則資料會跨越資料行，而不是沿著資料列往下移動。例如，如果記錄集有兩個欄位和 10 個數據列，則陣列會顯示為兩個數據列和 10 個數據行。因此，您必須先使用 TransposeDim () 函式轉置陣列，再將陣列指派給儲存格範圍。將陣列指派給儲存格範圍時，有一些限制需要注意：

將陣列指派給 Excel Range 物件時，適用下列限制：

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

請注意，將陣列複製到 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：應用程式定義或物件定義錯誤

參考

如需有關將陣列傳遞至各種 Excel 版本限制的其他資訊，請按一下下列文章編號以檢視 Microsoft 知識庫中的文章：

177991 XL：使用自動化將陣列傳遞至 Excel 的限制

247412 資訊：從 Visual Basic 將資料傳輸至 Excel 的方法

如何使用自動化將資料從 ADO 記錄集傳輸至 Excel

摘要

其他相關資訊

建立範例的步驟

使用 CopyFromRecordset

使用 GetRows

參考

其他資源