Så här överför du data från en ADO-postuppsättning till Excel med automatisering

Artikel
06/20/2023
Gäller för:

Excel 2007, Excel 2003, Excel 2002, Excel 2000, Excel 97

Sammanfattning

Du kan överföra innehållet i en ADO-postuppsättning till ett Microsoft Excel-kalkylblad genom att automatisera Excel. Vilken metod du kan använda beror på vilken version av Excel du automatiserar. Excel 97, Excel 2000 och Excel 2002 har en CopyFromRecordset-metod som du kan använda för att överföra en postuppsättning till ett intervall. CopyFromRecordset i Excel 2000 och 2002 kan användas för att kopiera antingen en DAO- eller en ADO-postuppsättning. CopyFromRecordset i Excel 97 stöder dock endast DAO-postuppsättningar. Om du vill överföra en ADO-postuppsättning till Excel 97 kan du skapa en matris från postuppsättningen och sedan fylla i ett intervall med innehållet i matrisen.

I den här artikeln beskrivs båda metoderna. Exempelkoden som visas visar hur du kan överföra en ADO-postuppsättning till Excel 97, Excel 2000, Excel 2002, Excel 2003 eller Excel 2007.

Mer information

Kodexemplet nedan visar hur du kopierar en ADO-postuppsättning till ett Microsoft Excel-kalkylblad med hjälp av automatisering från Microsoft Visual Basic. Koden kontrollerar först versionen av Excel. Om Excel 2000 eller 2002 identifieras används metoden CopyFromRecordset eftersom den är effektiv och kräver mindre kod. Men om Excel 97 eller tidigare identifieras kopieras postuppsättningen först till en matris med metoden GetRows för ADO-postuppsättningsobjektet. Matrisen transponeras sedan så att posterna är i den första dimensionen (i rader) och fälten är i den andra dimensionen (i kolumner). Sedan kopieras matrisen till ett Excel-kalkylblad genom att matrisen tilldelas ett cellområde. (Matrisen kopieras i ett steg i stället för att loopa igenom varje cell i kalkylbladet.)

Kodexemplet använder Northwind-exempeldatabasen som ingår i Microsoft Office. Om du valde standardmappen när du installerade Microsoft Office finns databasen i:

\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

Om Northwind-databasen finns i en annan mapp på datorn måste du redigera sökvägen till databasen i koden nedan.

Om du inte har Northwind-databasen installerad i systemet kan du använda alternativet Lägg till/ta bort för Microsoft Office-konfigurationen för att installera exempeldatabaserna.

Observera Northwind-databasen installeras inte när du installerar Microsoft Office 2007. Om du vill hämta Northwind 2007 går du till följande Microsoft-webbplats:

Teman för Office-mallar &

Steg för att skapa exempel

Starta Visual Basic och skapa ett nytt Standard EXE-projekt. Form1 skapas som standard.
Lägg till en CommandButton i Form1.
Klicka på Referenserfrån Projekt-menyn. Lägg till en referens till Microsoft ActiveX-dataobjekt 2.1-biblioteket.

Klistra in följande kod i kodavsnittet 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

Tryck på F5 för att köra projektet. Formulär1 visas.
Klicka på CommandButton på Formulär1 och observera att innehållet i tabellen Beställningar visas i en ny arbetsbok i Excel.

Använda CopyFromRecordset

För effektivitet och prestanda är CopyFromRecordset den bästa metoden. Eftersom Excel 97 endast stöder DAO-postuppsättningar med CopyFromRecordset får du följande fel om du försöker skicka en ADO-postuppsättning till CopyFromRecordset med Excel 97:

Körningsfel 430: Klassen stöder inte Automation eller har inte stöd för förväntat gränssnitt. I kodexemplet kan du undvika det här felet genom att kontrollera Excel-versionen så att du inte använder CopyFromRecordset för 97-versionen.

Observera När du använder CopyFromRecordset bör du vara medveten om att den ADO- eller DAO-postuppsättning som du använder inte får innehålla OLE-objektfält eller matrisdata, till exempel hierarkiska postuppsättningar. Om du inkluderar fält av någon typ i en postuppsättning misslyckas metoden CopyFromRecordset med följande fel:

Körningsfel -2147467259: Metoden CopyFromRecordset för objektintervallet misslyckades.

Använda GetRows

Om Excel 97 identifieras använder du metoden GetRows för ADO-postuppsättningen för att kopiera postuppsättningen till en matris. Om du tilldelar matrisen som returneras av GetRows till ett cellområde i kalkylbladet går data över kolumnerna i stället för nedåt på raderna. Om postuppsättningen till exempel har två fält och 10 rader visas matrisen som två rader och 10 kolumner. Därför måste du transponera matrisen med funktionen TransposeDim() innan du tilldelar matrisen till cellområdet. När du tilldelar en matris till ett cellområde finns det vissa begränsningar att känna till:

Följande begränsningar gäller när du tilldelar en matris till ett Excel Range-objekt:

Matrisen får inte innehålla OLE-objektfält eller matrisdata, till exempel hierarkiska postuppsättningar. Observera att kodexemplet söker efter det här villkoret och visar "Matrisfält" så att användaren blir medveten om att fältet inte kan visas i Excel.
Matrisen får inte innehålla datumfält som har ett datum före år 1900. (Se avsnittet Referenser för en microsoft knowledge base-artikellänk.) Observera att kodexemplet formaterar datumfält som variantsträngar för att undvika det här potentiella problemet.

Observera användningen av funktionen TransposeDim() för att transponera matrisen innan matrisen kopieras till Excel-kalkylbladet. I stället för att skapa en egen funktion för att transponera matrisen kan du använda Funktionen Transponera i Excel genom att ändra exempelkoden för att tilldela matrisen till cellerna enligt nedan:

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

Om du bestämmer dig för att använda Transponeringsmetoden i Excel i stället för funktionen TransposeDim() för att transponera matrisen bör du vara medveten om följande begränsningar med transponeringsmetoden:

Matrisen får inte innehålla ett element som är större än 255 tecken.
Matrisen får inte innehålla Null-värden.
Antalet element i matrisen får inte överstiga 5461.

Om ovanstående begränsningar inte beaktas när du kopierar en matris till ett Excel-kalkylblad kan något av följande körningsfel inträffa:

Körningsfel 13: Typmatchningsfel

Körningsfel 5: Ogiltig procedur

anrop eller argument Körningsfel 1004: Programdefinierat eller objektdefinierat fel

Referenser

Om du vill ha mer information om begränsningar för att skicka matriser till olika versioner av Excel klickar du på följande artikelnummer för att visa artikeln i Microsoft Knowledge Base:

177991 XL: Begränsningar för att skicka matriser till Excel med automation

247412 INFO: Metoder för att överföra data till Excel från Visual Basic