Sådan overføres data automatisk fra et ADO-recordsæt til Excel

Oversættelser af artikler Oversættelser af artikler
Artikel-id: 246335 - Få vist de produkter, som denne artikel refererer til.
Udvid alle | Skjul alle

På denne side

Sammenfatning

Du kan overføre indholdet af et ADO-recordsæt til et Microsoft Excel-regneark ved at automatisere Excel. Den benyttede fremgangsmåde afhænger af den version af Excel, du vil automatisere. Excel 97, Excel 2000 og Excel 2002 har en CopyFromRecordset-metode, du kan bruge til at overføre et recordsæt til et område. CopyFromRecordset i Excel 2000 og 2002 kan bruges til at kopiere enten et DAO- eller et ADO-recordsæt. Dog understøtter CopyFromRecordset i Excel 97 kun DAO-recordsæt. Hvis du vil overføre et ADO-recordsæt til Excel 97, kan du oprette en matrix på baggrund af recordsættet og derefter automatisk indsætte indholdet af den pågældende matrix i et område.

Denne artikel omhandler begge fremgangsmåder. Den angivne eksempelkode illustrerer, hvordan du kan overføre et ADO-recordsæt til Excel 97, Excel 2000, Excel 2002, Excel 2003 eller Excel 2007.

Yderligere Information

Kodeeksemplet nedenfor viser, hvordan du kopierer et ADO-recordsæt til et Microsoft Excel-regneark ved hjælp af automatisering fra Microsoft Visual Basic. Koden kontrollerer først Excel-versionen. Hvis Excel 2000 eller 2002 registreres, benyttes CopyFromRecordset-metoden, fordi den er effektiv og kræver færre koder. Men hvis Excel 97 eller tidligere versioner registreres, kopieres recordsættet først til en matrix ved hjælp af ADO-recordsætobjektets GetRows-metode. Matrixen transponeres derefter, så der vises poster i første dimension (i rækker) og felter i anden dimension (i kolonner). Dernæst kopieres matrixen til et Excel-regneark, idet du tildeler matrixen til et celleområde. (Matrixen kopieres i ét trin i stedet for at skulle bevæge sig gennem hver celle i regnearket).

Kodeeksemplet benytter eksempeldatabasen Northwind, der følger med Microsoft Office. Hvis du valgte standardmappen, da du installerede Microsoft Office, findes databasen under:

\Programmer\Microsoft Office\Office\Samples\Northwind.mdb

Hvis databasen Northwind findes i en anden mappe på computeren, skal du redigere stien til databasen i koden nedenfor.

Hvis databasen Northwind ikke er installeret på systemet, kan du bruge indstillingen Tilføj/Fjern til at få Microsoft Office-installationen til at installere eksempeldatabasen.

Bemærk! Databasen Northwind installeres ikke, når du installerer 2007 Microsoft Office. Du kan hente Northwind 2007 på følgende Microsoft-websted:
http://office.microsoft.com/en-us/templates/TC012289971033.aspx

Trin til oprettelse af eksempel

  1. Start Visual Basic, og opret et nyt standard-EXE-projekt. Form1 oprettes som standard.
  2. Føj CommandButton til Form1.
  3. Klik på Referencer i menuen Projekt. Føj en reference til Microsoft ActiveX Data Objects 2.1-biblioteket.
  4. Indsæt følgende kode i kodeafsnittet i 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. Tryk på F5-tasten for at køre projektet. Form1 vises.
  6. Klik på CommandButton på Form1, og vær opmærksom på, at indholdet i tabellen Ordrer vises i en ny projektmappe i Excel.
Brug af CopyFromRecordset

CopyFromRecordset er den foretrukne metode hvad angår effektivitet og ydeevne. Da Excel 97 kun understøtter DAO-recordsæt med CopyFromRecordset, vises nedenstående fejlmeddelelse, hvis du forsøger at videresende et ADO-recordsæt til CopyFromRecordset med Excel 97:
Kørselsfejl 430:
Klassen understøtter ikke Automation eller understøtter ikke den forventede brugergrænseflade.
I kodeeksemplet kan du undgå denne fejl ved at kontrollere Excel-versionen, så du ikke bruger CopyFromRecordset til 97-versionen.

Bemærk! Når du bruger CopyFromRecordset, skal du være opmærksom på, at det ADO- eller DAO-recordsæt, du benytter, ikke må indeholde OLE-objekter eller matrixdata, f.eks. hierarkiske recordsæt. Hvis du medtager disse typer felter, mislykkes metoden CopyFromRecordset med følgende fejl:
Kørselsfejl -2147467259:
Metoden CopyFromRecordset mislykkedes for objektet Range.
Brug af GetRows

Hvis Excel 97 registreres, skal du bruge metoden GetRows i ADO-recordsættet til at kopiere recordsættet til en matrix. Hvis du tildeler den matrix, der returneres af GetRows, til et celleområde i regnearket, går dataene på tværs af kolonnerne i stedet for ned langs rækkerne. Hvis recordsættet f.eks. indeholder to felter og 10 rækker, vises matrixen som to rækker og 10 kolonner. Du skal derfor transponere matrixen ved hjælp af funktionen TransposeDim(), inden du tildeler matrixen til celleområdet. Når du tildeler en matrix til et celleområde, findes der nogle begrænsninger, du skal være opmærksom på:

Følgende begrænsninger gælder, når du tildeler en matrix til et Excel-områdeobjekt:
  • Matrixen må ikke indeholde OLE-objektfelter eller matrixdata, f.eks. hierarkiske recordsæt. Vær opmærksom på, at kodeeksemplet kontrollerer, om denne betingelse er overholdt, og viser meddelelsen "Arrayfelt", så brugeren gøres opmærksom på, at feltet ikke kan vises i Excel.

  • Matrixen må ikke indeholde datofelter, hvor der er angivet en dato før år 1900. (Se afsnittet "Referencer", hvor du kan finde et link til en artikel i Microsoft Knowledge Base). Vær opmærksom på, at kodeeksemplet formaterer datofelter som variantstrenge for at undgå dette potentielle problem.
Vær opmærksom på brugen af funktionen TransposeDim() til transponering af matrixen, inden matrixen kopieres til Excel-regnearket. I stedet for at oprette din egen funktion til transponering af matrixen, kan du bruge funktionen Transponer i Excel ved at ændre eksempelkoden, så matrixen tildeles til cellerne som vist nedenfor:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
Hvis du beslutter dig for at bruge metoden Transponer i Excel i stedet for funktionen TransposeDim() til at transponere matrixen, skal du være opmærksom på følgende begrænsninger i forbindelse med metoden Transponer:
  • Matrixen må ikke indeholde et element, der består af mere end 255 tegn.
  • Matrixen må ikke indeholde Null-værdier.
  • Antallet af elementer i matrixen må ikke overstige 5461.
Hvis der ikke tages hensyn til ovennævnte begrænsninger, når du kopierer en matrix til et Excel-regneark, vises muligvis en af følgende kørselsfejl:
Kørselsfejl 13: Typeuoverensstemmelse
Kørselsfejl 5: Ugyldigt procedurekald eller argument
Kørselsfejl 1004: Programdefineret eller objektdefineret fejl

Referencer

Du kan finde flere oplysninger om begrænsninger i forbindelse med videresendelse af matrixer til forskellige versioner af Excel ved at klikke på nedenstående artikelnummer for at få vist artiklen i Microsoft Knowledge Base:
177991 Excel: Begrænsninger i forbindelse med automatisk videresendelse af matrixer til Excel. Artiklen er evt. på engelsk.
Du kan finde flere oplysninger ved at klikke på nedenstående artikelnumre for at få vist artiklerne i Microsoft Knowledge Base:
146406 Excel: Sådan hentes en tabel fra Access til Excel ved hjælp af DAO. Artiklen er evt. på engelsk.
215965 Excel 2000: I forbindelse med datoer før 1900 vises klokkeslættet 12:00:00. Artiklen er evt. på engelsk.
243394 Sådan bruges MFC til automatisk at kopiere et DAO-recordsæt til Excel. Artiklen er evt. på engelsk.
247412 INFO: Metoder til overførsel af data til Excel fra Visual Basic. Artiklen er evt. på engelsk.

Egenskaber

Artikel-id: 246335 - Seneste redigering: 23. november 2007 - Redigering: 5.0
Oplysningerne i denne artikel gælder:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard
  • Microsoft Excel 2000 Standard
  • Microsoft Excel 97 Standard
  • 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øgleord: 
kbhowto kbautomation kbexpertiseinter KB246335

Send feedback

 

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