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:
Postup vytvoření ukázky
Spusťte Visual Basic a vytvořte nový projekt Standard EXE. Formulář1 je ve výchozím nastavení vytvořen.
Přidejte commandButton do Form1.
Klepněte na příkaz Odkazyz nabídky projekt. Přidejte odkaz na knihovnu Microsoft ActiveX Data Objects 2.1.
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
Stisknutím klávesy F5 spusťte projekt. Zobrazí se formulář 1.
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