Overføre data fra et ADO-postsett til Excel ved hjelp av automatisering

Kundestøtte for Office 2003 er avsluttet

Microsoft avsluttet kundestøtte for Office 2003 den 8. april 2014. Denne endringen har påvirket programvareoppdateringene og sikkerhetsalternativene dine. Finn ut hvordan dette påvirker deg og hvordan du forblir beskyttet.

Sammendrag
Det er mulig å automatisere Microsoft Excel til å overføre innholdet i et ADO-postsett til et regneark i Excel. Fremgangsmåten avhenger av hvilken Excel-versjon som skal automatiseres. Excel 97, Excel 2000 og Excel 2002 har en CopyFromRecordset-metode som kan brukes til å overføre et postsett til et område. CopyFromRecordset i Excel 2000 og 2002 kan brukes til å kopiere DAO- og ADO-postsett. CopyFromRecordset-funksjonen i Excel 97 støtter imidlertid bare DAO-postsett. Hvis du vil overføre et ADO-postsett til Excel 97, kan du opprette en matrise fra postsettet og deretter sette innholdet i matrisen inn i et område.

I denne artikkelen drøftes begge metodene. Eksempelkoden illustrerer hvordan du kan overføre ADO-postsett til Excel 97, Excel 2000, Excel 2002, Excel 2003 og Excel 2007.
Mer informasjon
Eksempelkoden nedenfor illustrerer hvordan du kopierer et ADO-postsett til et Microsoft Excel-regneark ved hjelp av automatisering fra Microsoft Visual Basic. Først kontrollerer koden hvilken Excel-versjon som finnes på maskinen. Hvis den finner Excel 2000 eller 2002, brukes CopyFrom Recordset-metoden, siden denne både er effektiv og krever lite kode. Hvis derimot Excel 97 eller tidligere versjoner er installert på maskinen, kopieres først postsettet til en matrise ved hjelp av ADO-postsettobjektets GetRows-metode. Matrisen transponeres deretter slik at postene er i den første dimensjonen (rader) og feltene er i den andre dimensjonen (kolonner). Deretter kopieres matrisen til et Excel-ark ved at den tilordnes til et celleområde. (Matrisen kopieres under ett i stedet for at prosessen må gjentas for hver celle i regnearket.)

Kodeeksemplet er hentet fra eksempeldatabasen Northwind som inngår i Microsoft Office. Hvis du installerte Microsoft Office i standardmappen, finner du denne på følgende plassering:

\Programfiler\Microsoft Office\Office\Samples\Northwind.mdb

Hvis Northwind-databasen ligger i en annen mappe på datamaskinen, må du redigere banen til databasen i koden nedenfor.

Hvis Northwind-databasen ikke er installert på systemet, kan du installere eksempeldatabasen ved hjelp av alternativet Legg til / fjern for installasjonsprogrammet for Microsoft Office.

Obs!  Northwind-databasen blir ikke installert når du installerer 2007 Microsoft Office. Du kan få tak i Northwind 2007 fra følgende Microsoft-webområde:

Lage eksempler

  1. Start Visual Basic, og opprett et nytt standard EXE-prosjekt. Som standard opprettes Form1.
  2. Legg til en CommandButton i Form1.
  3. Klikk ReferencesProject-menyen. Legg til en referanse i Microsoft ActiveX Data Objects 2.1 Library.
  4. Lim inn følgende kode i kodedelen av 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. Trykk F5 for å kjøre prosjektet. Form1 vises.
  6. Klikk CommandButton i Form 1, og legg merke til at innholdet i ordretabellen vises i en ny arbeidsbok i Excel.
Bruke CopyFromRecordset

CopyFromRecordset er den beste metoden med tanke på effektivitet og ytelse. Fordi Excel 97 bare støtter DAO-postsett med CopyFromRecordset, får du følgende feilmelding hvis du forsøker å sende et ADO-postsett til CopyFromRecordset med Excel 97:
Run-time error 430:
Class does not support Automation or does not support expected interface.
Du kan unngå denne feilen i kodeeksemplet hvis du kontrollerer hvilken Excel-versjon du har, og lar være å bruke CopyFromRecordset hvis du har 97-versjonen.

Obs!  Når du bruker CopyFromRecordset bør du være oppmerksom på at ADO- eller DAO-postsettet du bruker, ikke må inneholde OLE-objektfelt eller matrisedata som hierarkiske postsett. Bruker du en av disse felttypene i postsettet, mislykkes CopyFromRecordset-metoden, og du får følgende feilmelding:
Run-time error -2147467259:
Method CopyFromRecordset of object Range failed.
Bruke GetRows

Hvis Excel 97 er installert på maskinen, bruker du ADO-postsettets GetRows-metode til å kopiere postsettet til en matrise. Hvis du tilordner matrisen som returneres av GetRows-metoden, til et celleområde i regnearket, vises dataene bortover i kolonnene i stedet for nedover i radene. Hvis for eksempel postsettet består av to felt og ti rader, vises matrisen som to rader og ti kolonner. Du må derfor transponere matrisen ved hjelp av TransposeDim()-funksjonen før du tilordner matrisen til celleområdet. Når du tilordner en matrise til et celleområde, må du være oppmerksom på noen begrensninger:

Følgende begrensninger gjelder når du tilordner en matrise til et Excel-områdeobjekt:
  • Matrisen kan ikke inneholde OLE-objektfelt eller matrisedata som hierarkiske postsett. Legg merke til at kodeeksemplet søker etter denne betingelsen og viser Array Field for å gjøre brukeren oppmerksom på at feltet ikke kan vises i Excel.

  • Matrisen kan ikke inneholde Date-felt med datoer før år 1900. (Under Referanser finner du en kobling til den relevante artikkelen i Microsoft Knowledge Base.) Legg merke til at eksempelkoden formaterer Date-felt som Variant-strenger for å unngå dette problemet.
Legg merke til at TransposeDim()-funksjonen brukes til å transponere matrisen før den kopieres til Excel-arket. I stedet for å opprette en egen funksjon for transponering av matrisen, kan du bruke transponeringsfunksjonen i Excel ved å endre eksempelkoden slik at matrisen tilordnes til cellene, som vist nedenfor:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _      xlApp.WorksheetFunction.Transpose(recArray)				
Hvis du bestemmer deg for å transponere matrisen ved hjelp av transponeringsmetoden i Excel i stedet for TransposeDim()-funksjonen, bør du være oppmerksom på følgende begrensninger i transponeringsmetoden:
  • matrisen kan ikke inneholde elementer på mer enn 255 tegn
  • matrisen kan ikke inneholde nullverdier
  • antall elementer i matrisen kan ikke overstige 5461
Hvis det ikke tas hensyn til begrensningene ovenfor når du kopierer matriser til Excel-ark, kan en av følgende kjøretidsfeil oppstå:
Run-time Error 13: Type Mismatch
Run-time Error 5: Invalid procedure call or argument
Run-time Error 1004: Application defined or object defined error
Referanser
Hvis du vil ha mer informasjon om begrensninger for sending av matriser til ulike Excel-versjoner, kan du klikke følgende artikkelnummer for å vise artikkelen i Microsoft Knowledge Base:
177991 XL: Begrensninger for sending av matriser til Excel ved hjelp av automatisering (denne artikkelen kan være på engelsk)
Hvis du vil ha mer informasjon, kan du klikke artikkelnumrene nedenfor for å vise artiklene i Microsoft Knowledge Base:
146406 XL: Hente en tabell fra Access til Excel ved hjelp av DAO (denne artikkelen kan være på engelsk)
215965 XL2000: 12:00:00 vises for datoer før 1900 (denne artikkelen kan være på engelsk)
243394 Bruke MFC til å kopiere DAO-postsett til Excel ved hjelp av automatisering (denne artikkelen kan være på engelsk)
247412 INFO: Metoder for overføring av data til Excel fra Visual Basic (denne artikkelen kan være på engelsk)
Transpose Mismatch XL2007
Egenskaper

Artikkel-ID: 246335 – Forrige gjennomgang: 11/23/2007 12:28:00 – Revisjon: 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

  • kbhowto kbautomation kbexpertiseinter KB246335
Tilbakemelding