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

文章翻譯 文章翻譯
文章編號: 246335 - 檢視此文章適用的產品。
本文曾發行於 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

建立範例的步驟

  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 = 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
    
  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 知識庫」中的文件:
177991 XL: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 的方法

屬性

文章編號: 246335 - 上次校閱: 2007年5月18日 - 版次: 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
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