您目前已離線,請等候您的網際網路重新連線

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

Office 2003 支援已結束

Microsoft 於 2014 年 4 月 8 日結束對 Office 2003 的支援。此變更已影響您的軟體更新和安全性選項。 瞭解這對您的意義為何且如何持續受保護。

本文曾發行於 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 網站:

建立範例的步驟

  1. 啟動 Visual Basic 並建立新的標準 EXE 專案。根據預設會建立 Form1。
  2. 新增 CommandButton 至 Form1。
  3. 按一下 [專案] 功能表中的 [參考]。新增參考至 [Microsoft ActiveX Data Objects 2.1 程式庫]
  4. 將下列程式碼貼至 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 = NothingEnd SubFunction 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 = tempArrayEnd Function
  5. 按下 F5 鍵以執行專案。Form1 隨即會出現。
  6. 按一下 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 知識庫」中的文件:
177991XL:Limitations of Passing Arrays to Excel Using Automation
如需詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
146406 How to Retrieve a Table from Access into Excel Using DAO
215965 XL2000:12:00:00 AM Displayed for Dates Earlier Than 1900
243394 HOWTO: Use MFC to Copy a DAO Recordset to Excel with Automation
247412 INFO:將資料從 Visual Basic 傳送至 Excel 的方法
Transpose Mismatch XL2007
內容

文章識別碼:246335 - 最後檢閱時間:05/18/2007 11:16:03 - 修訂: 5.0

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
意見反應
s://c.microsoft.com/ms.js">