Överföra data från en ADO-postuppsättning till Excel med automatisering

Artikelöversättning Artikelöversättning
Artikel-id: 246335 - Visa produkter som artikeln gäller.
Visa alla | Dölj alla

På den här sidan

Sammanfattning

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

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

Mer Information

Av exemplet framgår hur du kopierar en ADO-postuppsättning till ett Microsoft Excel-kalkylblad med hjälp av automatisering från Microsoft Visual Basic. I koden kontrolleras först Excel-versionen. Om Excel 2000 eller 2002 identifieras används CopyFromRecordset-metoden, eftersom den är effektiv och kräver mindre kod. Om Excel 97 eller tidigare identifieras kopieras emellertid postuppsättningen först till en matris med hjälp av GetRows-metoden för ADO-postuppsättningsobjektet. Matrisen transponeras sedan så att poster är i den första dimensionen (i rader) och fält i den andra dimensionen (i kolumner). Sedan kopieras matrisen till ett Excel-kalkylblad genom att matrisen tilldelas till ett cellområde. (Matrisen kopieras i ett steg i stället för att varje cell i kalkylbladet gås igenom i en loop.)

I kodexemplet används Northwind-exempeldatabasen som ingår i Microsoft Office. Om du valde standardmappen när du installerade Microsoft Office finns databasen här:

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

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

Om Northwind-databasen inte finns på datorn kan du använda alternativet Lägg till/ta bort för Microsoft Office-installationen för att installera exempeldatabaserna.

Obs! Northwind-databasen installeras inte tillsammans med Microsoft Office 2007, men du kan hämta Northwind 2007 på följande Microsoft-webbplats:
http://office.microsoft.com/sv-se/templates/TC012289971053.aspx

Skapa exempel

  1. Starta Visual Basic och skapa ett nytt Standard-EXE-projekt. Form1 skapas som standard.
  2. Lägg till en CommandButton i Form1.
  3. Klicka på ReferencesProject-menyn. Lägg till en referens till Microsoft ActiveX Data Objects 2.1 Library.
  4. 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\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. Kör projektet genom att trycka på F5. Form1 visas.
  6. Klicka på CommandButton i Form1, och observera att innehållet i tabellen Orders visas i en ny arbetsbok i Excel.
Använda CopyFromRecordset

CopyFromRecordset ger bäst effektivitet och prestanda. Eftersom Excel 97 bara stöder DAO-postuppsättningar med CopyFromRecordset, visas följande felmeddelande om du försöker överföra en ADO-postuppsättning till CopyFromRecordset med Excel 97:
Körfel nr 430:
Klassen stöder inte Automation eller förväntat gränssnitt.
I kodexemplet kan du undvika felmeddelandet genom att kontrollera Excel-versionen så att CopyFromRecordset inte används för 97-versionen.

Obs! När du använder CopyFromRecordset bör du vara medveten om att ADO- eller DAO-postuppsättningen du använder inte kan innehålla OLE-objektfält eller matrisdata som hierarkiska postuppsättningar. Om du har med fält av dessa typer i en postuppsättning fungerar inte CopyFromRecordset-metoden, och följande felmeddelande visas:
Körfel nr -2147467259:
Metoden CopyFromRecordset i objektet Range misslyckades.
Använda GetRows

Om Excel 97 identifieras ska du använda GetRows-metoden 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 tvärs över kolumnerna i stället för nedåt raderna. Om postuppsättningen exempelvis har två fält och tio rader visas matrisen som två rader och tio kolumner. Därför måste du transponera matrisen med hjälp av TransposeDim()-funktionen innan du tilldelar matrisen till cellområdet. När du tilldelar en matris till ett cellområde finns det några begränsningar du måste tänka på:

Följande begränsningar gäller när du tilldelar en matris till ett Excel Range-objekt:
  • Matrisen kan inte innehålla OLE-objektfält eller matrisdata, till exempel hierarkiska postuppsättningar. Observera att det finns en kontroll av det här tillståndet i koden som medför att "Array Field" visas, så att användaren blir medveten om att fältet inte kan visas i Excel.

  • Matrisen kan inte innehålla Datum-fält med ett datum som är tidigare än 1900. (I avsnittet "Referenser" finns en länk till en artikel i Microsoft Knowledge Base.) Observera att Datum-fält formateras som variantsträngar i kodexemplet för att det här problemet inte ska uppstå.
Observera att TransposeDim()-funktionen används för att transponera matrisen innan den kopieras till Excel-kalkylbladet. I stället för att skapa en egen funktion för att transponera matrisen kan du använda transponeringsfunktionen i Excel genom att ändra kodexemplet så att matrisen tilldelas till cellerna så här:
   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 TransposeDim()-funktionen för att transponera matrisen, bör du vara medveten om följande begränsningar hos transponeringsmetoden:
  • Matrisen kan inte innehålla ett element med mer än 255 tecken.
  • Matrisen kan inte innehålla Null-värden.
  • Antalet element i matrisen kan inte överstiga 5461.
Om du inte tar hänsyn till ovanstående begränsningar när du kopierar en matris till ett Excel-kalkylblad kan något av följande körfel uppstå:
Körfel 13: Typblandningsfel
Körfel 5: Ogiltigt proceduranrop eller argument
Körfel 1004: Program- eller objektdefinierat fel

Referenser

Om du vill veta mer om begränsningar för överföring av matriser till olika versioner av Excel klickar du på följande artikelnummer och läser artikeln i Microsoft Knowledge Base:
177991 XL: Begränsningar för överföring av matriser till Excel med hjälp av Automation (Länken kan leda till en webbplats som är helt eller delvis på engelska)
Om du vill veta mer klickar du på följande artikelnummer och läser artiklarna i Microsoft Knowledge Base:
146406 XL: Hämta en tabell från Access till Excel med hjälp av DAO (Länken kan leda till en webbplats som är helt eller delvis på engelska)
215965 XL2000: 12:00:00 visas för tidigare datum än 1900 (Länken kan leda till en webbplats som är helt eller delvis på engelska)
243394 Använda MFC för att kopiera en DAO-postuppsättning till Excel med automation (Länken kan leda till en webbplats som är helt eller delvis på engelska)
247412 INFO: Metoder för överföring av data till Excel från Visual Basic (Länken kan leda till en webbplats som är helt eller delvis på engelska)

Egenskaper

Artikel-id: 246335 - Senaste granskning: den 23 november 2007 - Revision: 5.0
Informationen i denna artikel gäller:
  • 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
Nyckelord: 
kbhowto kbautomation kbexpertiseinter KB246335

Ge 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