Gegevens overdragen van een ADO Recordset naar Excel met automatisering

  • Artikel
  • Van toepassing op:
    Excel 2007, Excel 2003, Excel 2002, Excel 2000, Excel 97

Samenvatting

U kunt de inhoud van een ADO-recordset overbrengen naar een Microsoft Excel-werkblad door Excel te automatiseren. Welke methode u kunt gebruiken, is afhankelijk van de versie van Excel die u automatiseert. Excel 97, Excel 2000 en Excel 2002 hebben een CopyFromRecordset-methode die u kunt gebruiken om een recordset over te dragen naar een bereik. CopyFromRecordset in Excel 2000 en 2002 kan worden gebruikt om een DAO- of ADO-recordset te kopiëren. CopyFromRecordset in Excel 97 ondersteunt echter alleen DAO-recordsets. Als u een ADO-recordset wilt overdragen naar Excel 97, kunt u een matrix maken op basis van de recordset en vervolgens een bereik vullen met de inhoud van die matrix.

In dit artikel worden beide benaderingen besproken. De weergegeven voorbeeldcode laat zien hoe u een ADO-recordset kunt overdragen naar Excel 97, Excel 2000, Excel 2002, Excel 2003 of Excel 2007.

Meer informatie

In het onderstaande codevoorbeeld ziet u hoe u een ADO-recordset kopieert naar een Microsoft Excel-werkblad met behulp van automatisering van Microsoft Visual Basic. De code controleert eerst de versie van Excel. Als Excel 2000 of 2002 wordt gedetecteerd, wordt de methode CopyFromRecordset gebruikt omdat deze efficiënt is en minder code vereist. Als Excel 97 of eerder echter wordt gedetecteerd, wordt de recordset eerst gekopieerd naar een matrix met behulp van de Methode GetRows van het ADO-recordsetobject. De matrix wordt vervolgens getransponeerd zodat records zich in de eerste dimensie (in rijen) bevinden en velden zich in de tweede dimensie (in kolommen) bevinden. Vervolgens wordt de matrix gekopieerd naar een Excel-werkblad door de matrix toe te wijzen aan een celbereik. (De matrix wordt in één stap gekopieerd in plaats van door elke cel in het werkblad te bladeren.)

Het codevoorbeeld maakt gebruik van de Northwind-voorbeelddatabase die is opgenomen in Microsoft Office. Als u de standaardmap hebt geselecteerd tijdens de installatie van Microsoft Office, bevindt de database zich in:

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

Als de Northwind-database zich in een andere map op uw computer bevindt, moet u het pad van de database bewerken in de onderstaande code.

Als u de Northwind-database niet op uw systeem hebt geïnstalleerd, kunt u de optie Toevoegen/verwijderen voor de installatie van Microsoft Office gebruiken om de voorbeelddatabases te installeren.

Opmerking De Northwind-database is niet geïnstalleerd wanneer u Microsoft Office 2007 installeert. Ga naar de volgende Microsoft-website om Northwind 2007 te verkrijgen:

Thema's voor Office-sjablonen &

Stappen voor het maken van een voorbeeld

  1. Start Visual Basic en maak een nieuw Standard EXE-project. Form1 wordt standaard gemaakt.

  2. Voeg een CommandButton toe aan Form1.

  3. Klik op Verwijzingen in het menu Project. Voeg een verwijzing toe naar de Microsoft ActiveX-gegevensobjectenbibliotheek 2.1.

  4. Plak de volgende code in de codesectie van 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. Druk op F5 om het project uit te voeren. Formulier1 wordt weergegeven.

  6. Klik op CommandButton op Form1 en houd er rekening mee dat de inhoud van de tabel Orders wordt weergegeven in een nieuwe werkmap in Excel.

CopyFromRecordset gebruiken

Voor efficiëntie en prestaties is CopyFromRecordset de voorkeursmethode. Omdat Excel 97 alleen DAO-recordsets met CopyFromRecordset ondersteunt, ontvangt u de volgende fout als u een ADO-recordset probeert door te geven aan CopyFromRecordset met Excel 97:

Runtimefout 430: De klasse biedt geen ondersteuning voor Automation of biedt geen ondersteuning voor de verwachte interface. In het codevoorbeeld kunt u deze fout voorkomen door de versie van Excel te controleren, zodat u CopyFromRecordset niet gebruikt voor de 97-versie.

Opmerking Wanneer u CopyFromRecordset gebruikt, moet u er rekening mee houden dat de ADO- of DAO-recordset die u gebruikt, geen OLE-objectvelden of matrixgegevens, zoals hiërarchische recordsets, mag bevatten. Als u velden van een van beide typen in een recordset opneemt, mislukt de methode CopyFromRecordset met de volgende fout:

Runtimefout -2147467259: Methode CopyFromRecordset van objectbereik is mislukt.

GetRows gebruiken

Als Excel 97 wordt gedetecteerd, gebruikt u de Methode GetRows van de ADO-recordset om de recordset naar een matrix te kopiëren. Als u de matrix die door GetRows wordt geretourneerd, toewijst aan een celbereik in het werkblad, worden de gegevens over de kolommen verdeeld in plaats van in de rijen. Als de recordset bijvoorbeeld twee velden en tien rijen heeft, wordt de matrix weergegeven als twee rijen en tien kolommen. Daarom moet u de matrix transponeren met behulp van de functie TransposeDim() voordat u de matrix toewijst aan het celbereik. Wanneer u een matrix toewijst aan een celbereik, zijn er enkele beperkingen waarmee u rekening moet houden:

De volgende beperkingen gelden wanneer u een matrix toewijst aan een Excel Range-object:

  • De matrix kan geen OLE-objectvelden of matrixgegevens bevatten, zoals hiërarchische recordsets. U ziet dat in het codevoorbeeld wordt gecontroleerd op deze voorwaarde en 'Matrixveld' wordt weergegeven, zodat de gebruiker weet dat het veld niet kan worden weergegeven in Excel.

  • De matrix mag geen datumvelden bevatten die een datum vóór het jaar 1900 hebben. (Zie de sectie Verwijzingen voor een koppeling naar een Microsoft Knowledge Base-artikel.) Houd er rekening mee dat in het codevoorbeeld datumvelden worden opgemaakt als varianttekenreeksen om dit potentiële probleem te voorkomen.

Let op het gebruik van de functie TransposeDim() om de matrix te transponeren voordat de matrix naar het Excel-werkblad wordt gekopieerd. In plaats van uw eigen functie te maken om de matrix te transponeren, kunt u de functie Transponeren van Excel gebruiken door de voorbeeldcode te wijzigen om de matrix toe te wijzen aan de cellen, zoals hieronder wordt weergegeven:

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

Als u besluit de methode Transponeren van Excel te gebruiken in plaats van de functie TransposeDim() om de matrix te transponeren, moet u rekening houden met de volgende beperkingen met de transponeringsmethode:

  • De matrix mag geen element van meer dan 255 tekens bevatten.
  • De matrix mag geen Null-waarden bevatten.
  • Het aantal elementen in de matrix mag niet groter zijn dan 5461.

Als de bovenstaande beperkingen niet in aanmerking worden genomen wanneer u een matrix naar een Excel-werkblad kopieert, kan een van de volgende runtimefouten optreden:

Runtimefout 13: Type komt niet overeen

Runtimefout 5: Ongeldige procedure

aanroep of argument Runtimefout 1004: Door de toepassing gedefinieerde of door het object gedefinieerde fout

Verwijzingen

Voor meer informatie over beperkingen voor het doorgeven van matrices aan verschillende versies van Excel, klikt u op het volgende artikelnummer om het artikel in de Microsoft Knowledge Base weer te geven:

177991 XL: Beperkingen van het doorgeven van matrices aan Excel met behulp van Automatisering

247412 INFO: methoden voor het overdragen van gegevens naar Excel vanuit Visual Basic