Přenos dat ze sady záznamů ADO do Excelu pomocí automatizace

Souhrn

Obsah sady záznamů ADO můžete převést na list aplikace Microsoft Excel automatizací aplikace Excel. Přístup, který můžete použít, závisí na verzi Excelu, kterou automatizujete. Aplikace Excel 97, Excel 2000 a Excel 2002 mají metodu CopyFromRecordset, kterou můžete použít k přenosu sady záznamů do oblasti. CopyFromRecordset v aplikaci Excel 2000 a 2002 lze použít ke zkopírování sady záznamů DAO nebo ADO. CopyFromRecordset ale v Excelu 97 podporuje jenom sady záznamů DAO. Chcete-li přenést sadu záznamů ADO do aplikace Excel 97, můžete vytvořit pole ze sady záznamů a pak naplnit oblast obsahem tohoto pole.

Tento článek popisuje oba přístupy. Uvedený ukázkový kód ukazuje, jak můžete převést sadu záznamů ADO do Excelu 97, Excelu 2000, Excelu 2002, Excelu 2003 nebo Excelu 2007.

Další informace

Následující ukázka kódu ukazuje, jak zkopírovat sadu záznamů ADO do listu aplikace Microsoft Excel pomocí automatizace z jazyka Microsoft Visual Basic. Kód nejprve zkontroluje verzi Aplikace Excel. Pokud je zjištěna aplikace Excel 2000 nebo 2002, metoda CopyFromRecordset se používá, protože je efektivní a vyžaduje méně kódu. Pokud je však zjištěna aplikace Excel 97 nebo starší, sada záznamů se nejprve zkopíruje do pole pomocí metody GetRows objektu sady záznamů ADO. Pole se pak transponuje tak, aby záznamy byly v první dimenzi (v řádcích) a pole byla ve druhé dimenzi (ve sloupcích). Potom se pole zkopíruje do excelového listu přiřazením pole k oblasti buněk. (Pole se kopíruje v jednom kroku, nikoli ve smyčce mezi jednotlivými buňkami listu.)

Ukázka kódu používá ukázkovou databázi Northwind, která je součástí Microsoft Office. Pokud jste při instalaci Microsoft Office vybrali výchozí složku, databáze se nachází v:

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

Pokud je databáze Northwind umístěna v jiné složce v počítači, je nutné upravit cestu k databázi v níže uvedeném kódu.

Pokud nemáte v systému nainstalovanou databázi Northwind, můžete k instalaci ukázkových databází použít možnost Přidat nebo odebrat pro instalaci systému Microsoft Office.

Poznámka Databáze Northwind není nainstalována při instalaci systému Microsoft Office 2007. Chcete-li získat Northwind 2007, navštivte následující web společnosti Microsoft:

Motivy šablon & Office

Postup vytvoření ukázky

1. Spusťte Visual Basic a vytvořte nový projekt Standard EXE. Formulář1 je ve výchozím nastavení vytvořen.

2. Přidejte commandButton do Form1.

3. Klepněte na příkaz Odkazyz nabídky projekt. Přidejte odkaz na knihovnu Microsoft ActiveX Data Objects 2.1.

4. Do oddílu kódu formuláře Form1 vložte následující kód:

   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. Stisknutím klávesy F5 spusťte projekt. Zobrazí se formulář 1.

6. Klepněte commandButton na Form1 a všimněte si, že obsah tabulky Objednávky se zobrazí v novém sešitu v aplikaci Excel.

Použití copyFromRecordset

Pro zvýšení efektivity a výkonu je upřednostňovanou metodou CopyFromRecordset. Protože Excel 97 podporuje pouze sady záznamů DAO s CopyFromRecordset, pokud se pokusíte předat sadu záznamů ADO CopyFromRecordset s Excelem 97, zobrazí se následující chybová zpráva:

Chyba za běhu 430: Třída nepodporuje automatizaci nebo nepodporuje očekávané rozhraní. V ukázce kódu se této chybě můžete vyhnout tak, že zkontrolujete verzi Excelu, abyste pro verzi 97 nepoužívali CopyFromRecordset.

Poznámka Při použití CopyFromRecordset byste měli vědět, že sada záznamů ADO nebo DAO, kterou používáte, nemůže obsahovat pole objektů OLE nebo data pole, jako jsou hierarchické sady záznamů. Pokud do sady záznamů zahrnete pole obou typů, metoda CopyFromRecordset selže s následující chybou:

Chyba za běhu -2147467259: Metoda CopyFromRecordset rozsahu objektů se nezdařila.

Použití GetRows

Pokud je zjištěna aplikace Excel 97, pomocí metody GetRows sady záznamů ADO zkopírujte sadu záznamů do pole. Pokud přiřadíte matici vrácenou příkazem GetRows k oblasti buněk v listu, data přecházejí přes sloupce místo dolů v řádcích. Pokud má sada záznamů například dvě pole a 10 řádků, pole se zobrazí jako dva řádky a 10 sloupců. Proto je nutné matici transponovat pomocí funkce TransponovatDim() před přiřazením pole k oblasti buněk. Při přiřazování pole k oblasti buněk je třeba mít na paměti určitá omezení:

Při přiřazování pole k objektu Oblasti aplikace Excel platí následující omezení:

• Pole nemůže obsahovat pole objektů OLE nebo data pole, například hierarchické sady záznamů. Všimněte si, že vzorový kód tuto podmínku zkontroluje a zobrazí pole Pole, aby uživatel věděl, že pole nelze zobrazit v aplikaci Excel.

• Pole nemůže obsahovat pole Data s datem před rokem 1900. (Odkaz na článek znalostní báze Microsoft Knowledge Base najdete v části Odkazy.) Všimněte si, že vzorový kód formátuje pole date jako řetězce variant, aby se tomuto potenciálnímu problému zabránilo.

Všimněte si použití funkce TransponovatDim() k transpozici pole před zkopírování pole do listu aplikace Excel. Místo vytvoření vlastní funkce k transpozici pole můžete použít excelovou funkci Transponovat úpravou vzorového kódu tak, aby přiřadil matici k buňkám, jak je znázorněno níže:

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

Pokud se rozhodnete k transpozici pole použít excelovou metodu Transponovat místo funkce TransponovatDim(), měli byste si být vědomi následujících omezení metody Transpozice:

• Pole nemůže obsahovat prvek, který je větší než 255 znaků.
• Pole nemůže obsahovat hodnoty Null.
• Počet prvků v poli nesmí překročit 5461.

Pokud se výše uvedená omezení při kopírování pole do excelového listu nezohlední, může dojít k jedné z následujících chyb za běhu:

Chyba za běhu 13: Neshoda typů

Chyba za běhu 5: Neplatná procedura

Chyba za běhu volání nebo argumentu 1004: Chyba definovaná aplikací nebo definovaná objektem

Odkazy

Další informace o omezeních předávání polí do různých verzí aplikace Excel získáte v následujícím článku znalostní báze Microsoft Knowledge Base:

177991 XL: Omezení předávání polí do Excelu pomocí automatizace

247412 INFORMACE: Metody pro přenos dat do Excelu z jazyka Visual Basic