Gegevens uit een ADO-Recordset naar Excel overbrengen met automatisering

BELANGRIJK: Dit artikel is vertaald door de vertaalmachine software van Microsoft in plaats van door een professionele vertaler. Microsoft biedt u professioneel vertaalde artikelen en artikelen vertaald door de vertaalmachine, zodat u toegang heeft tot al onze knowledge base artikelen in uw eigen taal. Artikelen vertaald door de vertaalmachine zijn niet altijd perfect vertaald. Deze artikelen kunnen fouten bevatten in de vocabulaire, zinsopbouw en grammatica en kunnen lijken op hoe een anderstalige de taal spreekt en schrijft. Microsoft is niet verantwoordelijk voor onnauwkeurigheden, fouten en schade ontstaan door een incorrecte vertaling van de content of het gebruik ervan door onze klanten. Microsoft past continue de kwaliteit van de vertaalmachine software aan door deze te updaten.

De Engelstalige versie van dit artikel is de volgende:246335
Samenvatting
U kunt de inhoud van een ADO-recordset om transfer een Microsoft Excel-werkblad door Excel automatiseren. De aanpak die u kunt gebruiken afhankelijk van de versie van Excel die u wilt automatiseren. Excel 97, Excel 2000 en Excel 2002 heeft een methode CopyFromRecordset overbrengen kunt u een record set aan een bereik. CopyFromRecordset in Excel 2000 en 2002 kan worden gebruikt om Kopieer een DAO of ADO-recordset. Echter CopyFromRecordset in Excel 97 ondersteunt alleen DAO records ets. Een ADO-recordset overbrengen naar Excel 97, kunt u een matrix van de record set en vul een bereik met de inhoud van de matrix.

Dit artikel bespreekt beide benaderingen. Het monster codes wordt geïllustreerd hoe u een ADO-recordset kunt overbrengen naar Excel 97 Excel 2000, Excel 2002, Excel 2003 of Excel 2007.
Meer informatie
Het onderstaande code voorbeeld ziet u hoe een ADO kopiëren record set naar Microsoft Excel-werkblad met behulp van automatisering van Microsoft Visual Basis. De code controleert eerst de versie van Excel. Als Excel 2000 of 2002 gedetecteerd, de methode CopyFromRecordset wordt gebruikt omdat het efficiënter en minder code vereist. Echter, als u Excel 97 of eerder wordt gedetecteerd, de record set eerst gekopieerd naar een matrix met behulp van de methode GetRows van de ADO-recordset object. De matrix wordt vervolgens omgezet zodat records in de eerste dimensie (in rijen) en velden worden in de tweede dimensie (kolommen). Vervolgens de array wordt gekopieerd naar een Excel-werkblad via de matrix ber eik toewijzen cellen. (De matrix wordt gekopieerd in één stap plaats via elke cel lus in het voorstel.)

Het code voorbeeld wordt de voorbeeld data base Noorden wind dat is meegeleverd met Microsoft Office. Als u de standaardmap hebt geselecteerd als Microsoft Office is geïnstalleerd, de data base zich bevindt in:

\Program Office\Office\Voorbeelden\Noordenwind.mdb

Als North wind data base bevindt zich in een andere map op uw computer, moet u bewerken het pad naar de data base in de onderstaande code.

Als u geen de data base Noorden wind op uw systeem geïnstalleerd hebt, kunt u de Optie voor de installatie van Microsoft Office te installeren van de voorbeeld data bases toevoegen/verwijderen.

OpmerkingDe data base Noorden wind wordt niet geïnstalleerd tijdens de installatie van 2007 Microsoft Office. Noorden wind 2007 downloaden, gaat u naar de volgende Microsoft-website:

Stappen voor het maken van monster

  1. Start Visual Basic en maak een nieuw standaard-EXE-project. Form1 wordt standaard gemaakt.
  2. Toevoegen eenCommandButtontoe aan Form1.
  3. Klik opVerwijzingenuit deProjectmenu. Voeg een verwijzing toe naar deMicrosoft ActiveX Data Objects 2. 1 Library.
  4. Plak de volgende code in de sectie code 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 = 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. Druk op F5 om het uitvoeren van het project. Formulier1 wordt weer gegeven.
  6. Klik op deCommandButtonop Form1 en wordt de inhoud van de tabel Orders in een nieuwe werkmap in Excel weer gegeven.
Met behulp van CopyFromRecordset

CopyFromRecordset is voor efficiëntie en prestaties de aanbevolen methode. Omdat Excel 97 alleen DAO records ets met ondersteunt CopyFromRecordset, als u probeert een ADO-recordset doorgeven aan CopyFromRecordset met Excel 97, wordt de volgende fout weer gegeven:
Run time 430 fout:
Klasse ondersteunt automatisering of niet geen ondersteuning verwacht interface.
In het code voorbeeld, kunt u deze fout voorkomen door te controleren De versie van Excel zodat u CopyFromRecordset niet voor de 97 gebruikt versie.

OpmerkingWanneer u CopyFromRecordset, moet u er rekening mee houden dat de ADO of u gebruikt DAO-recordset geen OLE-objectvelden of matrixgegevens zoals hiërarchische records ets. Als u velden van elk type in een record set opnemen de methode CopyFromRecordset mislukt vanwege de volgende fout:
Run time fout-2147467259:
Methode CopyFromRecordset buiten het bereik van het object is mislukt.
GetRows gebruikt

Gebruik de methode GetRows als Excel 97 wordt gedetecteerd, de ADO Record set de record set naar een matrix kopiëren. Als u de matrix toewijzen door GetRows geretourneerd naar een bereik van cellen in het werk blad, gaat de gegevens over de kolommen in plaats van de rijen. Bijvoorbeeld, als de record set heeft twee velden en 10 rijen, wordt de array als twee rijen en tien kolommen weer gegeven. Daarom u wilt transponeren de matrix met de functie TransposeDim() voordat de matrix toewijzen aan het cellen ber eik. Bij het toewijzen van een matrix naar een bereik van cellen zijn er enkele beperkingen rekening houden met:

De volgende Er gelden beperkingen bij het toewijzen van een matrix naar een object Excel-bereik:
  • De matrix geen OLE-objectvelden of matrixgegevens zoals hiërarchische records ets. U ziet dat de voorbeeld code voor deze controles voorwaarde en "Matrix veld" wordt weer gegeven zodat de gebruiker wordt gewezen dat de veld kan niet worden weer gegeven in Excel.

  • De matrix geen datum velden met een datum vóór het jaar 1900. (Zie het gedeelte "Verwijzingen" voor een Microsoft Knowledge Base artikel link.) Houd er rekening mee dat de voorbeeld code datum velden als variant teken reeksen opmaken om te voorkomen dat dit potentiële probleem.
Let op het gebruik van de functie TransposeDim() om te zetten in de matrix voordat de matrix wordt gekopieerd naar het Excel-werkblad. In plaats van uw eigen functie Transponeren de matrix, kunt u Excel transponeren functie doordat de matrix toewijzen aan de cellen, zoals de voorbeeldcode hieronder:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _      xlApp.WorksheetFunction.Transpose(recArray)				
Als u met behulp van Excel transponeren methode in plaats van de TransposeDim()-functie om te zetten van de matrix, moet u rekening houden met de met de methode omwisselen de volgende beperkingen:
  • De matrix kan een element dat groter is dan bevatten. 255 tekens.
  • De matrix mag geen Null-waarden bevatten.
  • Het aantal elementen in de matrix mag niet meer dan 5461.
Als de bovengenoemde beperkingen niet in aanmerking worden genomen bij het u kopiëren een matrix naar een Excel-werkblad, een van de volgende run time fouten kan optreden:
Run time fout 13: Type komt niet overeen
Run time fout 5: Ongeldige procedure-aanroep of argument
Runtime-fout 1004: toepassing of door object gedefinieerde fout
Referenties
Voor meer informatie over beperkingen voor het doorgeven van matrices naar verschillende versies van Excel, klikt u op het volgende artikelnummer in de Microsoft Knowledge Base:
177991XL: Beperkingen van het automatisch doorgeven van matrices naar Excel
Voor meer informatie klikt u op de artikel nummers hieronder om de artikelen worden weer gegeven in de Microsoft Knowledge Base:
146406XL: Hoe u een tabel vanuit Access ophaalt in Excel met behulp van DAO
215965EXCEL 2000: 00: 00: 00 Uur weer gegeven voor datums vóór 1900
243394Het gebruik van MFC een DAO-Recordset kopiëren naar Excel automatisering
247412INFO: Methoden voor het overdragen van gegevens naar Excel vanuit Visual Basic
Verschil XL2007 transponeren

Waarschuwing: dit artikel is automatisch vertaald

Eigenschappen

Artikel-id: 246335 - Laatst bijgewerkt: 03/01/2011 07:32:00 - Revisie: 2.0

Microsoft Office Excel 2007, 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

  • kbexpertiseinter kbautomation kbhowto kbmt KB246335 KbMtnl
Feedback