Jak przesyłać dane z zestawu rekordów ADO do programu Excel za pomocą automatyzacji
Podsumowanie
Zawartość zestawu rekordów ADO można przenieść do arkusza programu Microsoft Excel, automatyzując program Excel. Podejście, którego można użyć, zależy od wersji programu Excel, którą automatyzujesz. Program Excel 97, Excel 2000 i Excel 2002 mają metodę CopyFromRecordset, która umożliwia przeniesienie zestawu rekordów do zakresu. Element CopyFromRecordset w programach Excel 2000 i 2002 może służyć do kopiowania obiektu DAO lub zestawu rekordów ADO. Jednak copyFromRecordset w programie Excel 97 obsługuje tylko zestawy rekordów DAO. Aby przenieść zestaw rekordów ADO do programu Excel 97, możesz utworzyć tablicę na podstawie zestawu rekordów, a następnie wypełnić zakres zawartością tej tablicy.
W tym artykule omówiono oba podejścia. Przedstawiony przykładowy kod ilustruje sposób przenoszenia zestawu rekordów ADO do programu Excel 97, Excel 2000, Excel 2002, Excel 2003 lub Excel 2007.
Więcej informacji
Poniższy przykład kodu przedstawia sposób kopiowania zestawu rekordów ADO do arkusza programu Microsoft Excel przy użyciu automatyzacji z programu Microsoft Visual Basic. Kod najpierw sprawdza wersję programu Excel. W przypadku wykrycia programu Excel 2000 lub 2002 używana jest metoda CopyFromRecordset, ponieważ jest wydajna i wymaga mniejszej ilości kodu. Jeśli jednak zostanie wykryty program Excel 97 lub starszy, zestaw rekordów zostanie najpierw skopiowany do tablicy przy użyciu metody GetRows obiektu zestawu rekordów ADO. Tablica jest następnie transponowana tak, aby rekordy były w pierwszym wymiarze (w wierszach), a pola w drugim wymiarze (w kolumnach). Następnie tablica jest kopiowana do arkusza programu Excel poprzez przypisanie tablicy do zakresu komórek. (Tablica jest kopiowana w jednym kroku, a nie w pętli przez każdą komórkę w arkuszu).
W przykładowym kodzie użyto przykładowej bazy danych Northwind dołączonej do pakietu Microsoft Office. Jeśli podczas instalowania pakietu Microsoft Office wybrano folder domyślny, baza danych znajduje się w następujących lokalizacjach:
\Program Files\Microsoft Office\Office\Samples\Northwind.mdb
Jeśli baza danych Northwind znajduje się w innym folderze na komputerze, musisz edytować ścieżkę bazy danych w kodzie podanym poniżej.
Jeśli nie masz zainstalowanej bazy danych Northwind w systemie, możesz użyć opcji Dodaj/Usuń dla instalatora pakietu Microsoft Office, aby zainstalować przykładowe bazy danych.
Uwaga Baza danych northwind nie jest zainstalowana podczas instalacji pakietu Microsoft Office 2007. Aby uzyskać program Northwind 2007, odwiedź następującą witrynę sieci Web firmy Microsoft:
Motywy szablonów & pakietu Office
Kroki tworzenia przykładu
Uruchom program Visual Basic i utwórz nowy projekt standardu EXE. Formularz 1 jest tworzony domyślnie.
Dodaj element CommandButton do formularza Form1.
Kliknij pozycję Odwołania z menu Projekt. Dodaj odwołanie do biblioteki Microsoft ActiveX Data Objects 2.1.
Wklej następujący kod do sekcji kodu formularza 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
Naciśnij klawisz F5, aby uruchomić projekt. Zostanie wyświetlony formularz Form1.
Kliknij przycisk Polecenia w formularzu Form1 i zwróć uwagę, że zawartość tabeli Orders jest wyświetlana w nowym skoroszycie w programie Excel.
Używanie polecenia CopyFromRecordset
Dla wydajności i wydajności preferowaną metodą jest CopyFromRecordset. Ponieważ program Excel 97 obsługuje tylko zestawy rekordów DAO z elementem CopyFromRecordset, w przypadku próby przekazania zestawu rekordów ADO do programu CopyFromRecordset z programem Excel 97 występuje następujący błąd:
Błąd czasu wykonywania 430: Klasa nie obsługuje automatyzacji lub nie obsługuje oczekiwanego interfejsu. W przykładzie kodu można uniknąć tego błędu, sprawdzając wersję programu Excel, aby nie używać polecenia CopyFromRecordset dla wersji 97.
Uwaga W przypadku korzystania z zestawu CopyFromRecordset należy pamiętać, że używany zestaw rekordów ADO lub DAO nie może zawierać pól obiektów OLE ani danych tablicy, takich jak hierarchiczne zestawy rekordów. W przypadku uwzględnienia pól dowolnego typu w zestawie rekordów metoda CopyFromRecordset kończy się niepowodzeniem z powodu następującego błędu:
Błąd czasu wykonywania -2147467259: Metoda CopyFromRecordset zakresu obiektów nie powiodła się.
Korzystanie z funkcji GetRows
Jeśli zostanie wykryty program Excel 97, użyj metody GetRows zestawu rekordów ADO, aby skopiować zestaw rekordów do tablicy. Jeśli przypiszesz tablicę zwracaną przez funkcję GetRows do zakresu komórek w arkuszu, dane będą przechodzić przez kolumny, a nie w dół wierszy. Jeśli na przykład zestaw rekordów ma dwa pola i 10 wierszy, tablica jest wyświetlana jako dwa wiersze i 10 kolumn. W związku z tym należy transponować tablicę przy użyciu funkcji TransposeDim() przed przypisaniem tablicy do zakresu komórek. Podczas przypisywania tablicy do zakresu komórek istnieją pewne ograniczenia, o których należy pamiętać:
Podczas przypisywania tablicy do obiektu zakresu programu Excel obowiązują następujące ograniczenia:
Tablica nie może zawierać pól obiektów OLE ani danych tablicy, takich jak hierarchiczne zestawy rekordów. Zwróć uwagę, że przykładowy kod sprawdza ten warunek i wyświetla wartość "Pole tablicy", aby użytkownik był świadomy, że pole nie może być wyświetlane w programie Excel.
Tablica nie może zawierać pól Daty, które mają datę wcześniejszą niż rok 1900. (Zobacz sekcję "Odwołania", aby uzyskać link do artykułu bazy wiedzy Microsoft Knowledge Base). Należy pamiętać, że przykładowy kod formatuje pola Daty jako ciągi wariantów, aby uniknąć tego potencjalnego problemu.
Zwróć uwagę na użycie funkcji TransposeDim() do transponowania tablicy przed skopiowaniem tablicy do arkusza programu Excel. Zamiast tworzyć własną funkcję transponowania tablicy, możesz użyć funkcji Transpose programu Excel, modyfikując przykładowy kod w celu przypisania tablicy do komórek, jak pokazano poniżej:
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
xlApp.WorksheetFunction.Transpose(recArray)
Jeśli zdecydujesz się użyć metody Transpose programu Excel zamiast funkcji TransposeDim(), aby transponować tablicę, należy pamiętać o następujących ograniczeniach w metodzie Transpose:
- Tablica nie może zawierać elementu większego niż 255 znaków.
- Tablica nie może zawierać wartości Null.
- Liczba elementów w tablicy nie może przekraczać 5461.
Jeśli powyższe ograniczenia nie zostaną uwzględnione podczas kopiowania tablicy do arkusza programu Excel, może wystąpić jeden z następujących błędów czasu wykonywania:
Błąd czasu wykonywania 13: niezgodność typu
Błąd 5 czasu wykonywania: nieprawidłowa procedura
wywołanie lub argument Błąd czasu wykonywania 1004: błąd zdefiniowany przez aplikację lub zdefiniowany obiekt
Informacje
Aby uzyskać dodatkowe informacje o ograniczeniach przekazywania tablic do różnych wersji programu Excel, kliknij następujący numer artykułu, aby wyświetlić artykuł w bazie wiedzy Microsoft Knowledge Base:
177991 XL: ograniczenia przekazywania tablic do programu Excel przy użyciu automatyzacji
247412 INFO: Metody przesyłania danych do programu Excel z programu Visual Basic