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

Artikkeloversettelser Artikkeloversettelser
Artikkel-ID: 246335 - Vis produkter som denne artikkelen gjelder for.
Vis alt | Skjul alt

På denne siden

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:
http://office.microsoft.com/en-us/templates/TC012289971033.aspx

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 = 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 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)

Egenskaper

Artikkel-ID: 246335 - Forrige gjennomgang: 23. november 2007 - Gjennomgang: 5.0
Informasjonen i denne artikkelen gjelder:
  • 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
Nøkkelord: 
kbhowto kbautomation kbexpertiseinter KB246335

Gi tilbakemelding

 

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