Slik overfører du data fra et ADO-postsett til Excel med automatisering

  • Artikkel
  • Gjelder for:
    Excel 2007, Excel 2003, Excel 2002, Excel 2000, Excel 97

Sammendrag

Du kan overføre innholdet i et ADO-postsett til et Microsoft Excel-regneark ved å automatisere Excel. Fremgangsmåten du kan bruke, avhenger av hvilken versjon av Excel du automatiserer. Excel 97, Excel 2000 og Excel 2002 har en CopyFromRecordset-metode som du kan bruke til å overføre et postsett til et område. CopyFromRecordset i Excel 2000 og 2002 kan brukes til å kopiere enten et DAO- eller ADO-postsett. CopyFromRecordset 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 fylle ut et område med innholdet i denne matrisen.

Denne artikkelen tar for seg begge fremgangsmåtene. Eksempelkoden som presenteres illustrerer hvordan du kan overføre et ADO-postsett til Excel 97, Excel 2000, Excel 2002, Excel 2003 eller Excel 2007.

Mer informasjon

Kodeeksempelet nedenfor viser hvordan du kopierer et ADO-postsett til et Microsoft Excel-regneark ved hjelp av automatisering fra Microsoft Visual Basic. Koden kontrollerer først versjonen av Excel. Hvis Excel 2000 eller 2002 oppdages, brukes Metoden CopyFromRecordset fordi den er effektiv og krever mindre kode. Hvis Excel 97 eller tidligere oppdages, kopieres postsettet først til en matrise ved hjelp av GetRows-metoden for ADO-postsettobjektet. Matrisen transponeres deretter slik at postene er i den første dimensjonen (i rader), og feltene er i den andre dimensjonen (i kolonner). Deretter kopieres matrisen til et Excel-regneark ved å tilordne matrisen til et celleområde. (Matrisen kopieres i ett trinn i stedet for å gå gjennom hver celle i regnearket.)

Kodeeksempelet bruker eksempeldatabasen Gastronor som er inkludert i Microsoft Office. Hvis du valgte standardmappen da du installerte Microsoft Office, er databasen plassert i:

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

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

Hvis du ikke har Northwind-databasen installert på systemet, kan du bruke alternativet Legg til / fjern for installasjonsprogrammet for Microsoft Office for å installere eksempeldatabasene.

Merk Northwind-databasen er ikke installert når du installerer 2007 Microsoft Office. Hvis du vil ha Northwind 2007, kan du gå til følgende Microsoft-webområde:

Temaer & for Office-maler

Fremgangsmåte for å opprette eksempel

  1. Start Visual Basic, og opprett et nytt Standard EXE-prosjekt. Skjema1 er opprettet som standard.

  2. Legg til en CommandButton i Form1.

  3. Klikk Referanser fra Prosjekt-menyen. Legg til en referanse i biblioteket Microsoft ActiveX Data Objects 2.1.

  4. Lim inn følgende kode i kodedelen i Skjema1:

    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. Trykk F5-tasten for å kjøre prosjektet. Skjema1 vises.

  6. Klikk CommandButton på Skjema1, og vær oppmerksom på at innholdet i Ordrer-tabellen vises i en ny arbeidsbok i Excel.

Bruke CopyFromRecordset

For effektivitet og ytelse er CopyFromRecordset den foretrukne metoden. Siden Excel 97 bare støtter DAO-postsett med CopyFromRecordset, får du følgende feilmelding hvis du prøver å sende et ADO-postsett til CopyFromRecordset med Excel 97:

Kjøretidsfeil 430: Klassen støtter ikke automatisering eller støtter ikke forventet grensesnitt. I kodeeksempelet kan du unngå denne feilen ved å kontrollere Excels versjon slik at du ikke bruker CopyFromRecordset for 97-versjonen.

Merk Når du bruker CopyFromRecordset, bør du være oppmerksom på at ADO- eller DAO-postsettet du bruker, ikke kan inneholde OLE-objektfelt eller matrisedata, for eksempel hierarkiske postsett. Hvis du inkluderer felt av en av typene i et postsett, mislykkes CopyFromRecordset-metoden med følgende feil:

Kjøretidsfeil -2147467259: Method CopyFromRecordset for objektområdet mislyktes.

Bruke GetRows

Hvis Excel 97 oppdages, bruker du GetRows-metoden for ADO-postsettet til å kopiere postsettet til en matrise. Hvis du tilordner matrisen som returneres av GetRows til et celleområde i regnearket, går dataene over kolonnene i stedet for nedover i radene. Hvis for eksempel postsettet har to felt og 10 rader, vises matrisen som to rader og 10 kolonner. Derfor må du 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, finnes det noen begrensninger å være klar over:

Følgende begrensninger gjelder når du tilordner en matrise til et Excel-områdeobjekt:

  • Matrisen kan ikke inneholde OLE-objektfelt eller matrisedata, for eksempel hierarkiske postsett. Legg merke til at kodeeksempelet kontrollerer denne betingelsen og viser Matrisefelt slik at brukeren blir gjort oppmerksom på at feltet ikke kan vises i Excel.

  • Matrisen kan ikke inneholde datofelt som har en dato før året 1900. (Se «Referanser»-delen for en artikkelkobling for Microsoft Knowledge Base.) Vær oppmerksom på at kodeeksempelet formaterer datofelt som variantstrenger for å unngå dette potensielle problemet.

Legg merke til bruken av TransposeDim()-funksjonen til å transponere matrisen før matrisen kopieres til Excel-regnearket. I stedet for å opprette din egen funksjon for å transponere matrisen, kan du bruke Funksjonen Transponer i Excel ved å endre eksempelkoden for å tilordne matrisen til cellene som vist nedenfor:

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

Hvis du bestemmer deg for å bruke Transponer-metoden i Excel i stedet for TransposeDim()-funksjonen til å transponere matrisen, bør du være oppmerksom på følgende begrensninger med transponeringsmetoden:

  • Matrisen kan ikke inneholde et element som er større enn 255 tegn.
  • Matrisen kan ikke inneholde nullverdier.
  • Antall elementer i matrisen kan ikke overskride 5461.

Hvis begrensningene ovenfor ikke tas i betraktning når du kopierer en matrise til et Excel-regneark, kan én av følgende kjøretidsfeil oppstå:

Kjøretidsfeil 13: Typekonflikt

Kjøretidsfeil 5: Ugyldig prosedyre

kall eller argument Kjøretidsfeil 1004: Programdefinert eller objektdefinert feil

Referanser

Hvis du vil ha mer informasjon om begrensninger for å sende matriser til ulike versjoner av Excel, klikker du følgende artikkelnummer for å vise artikkelen i Microsoft Knowledge Base:

177991 XL: Begrensninger ved å sende matriser til Excel ved hjelp av automatisering

247412 INFO: Metoder for overføring av data til Excel fra Visual Basic