Jesteś obecnie w trybie offline. Czekamy na ponowne połączenie z Internetem.

Jak transferować dane z zestawu rekordów ADO do programu Excel przy użyciu automatyzacji

Zakończono świadczenie pomocy technicznej dla pakietu Office 2003

Firma Microsoft zakończyła świadczenie pomocy technicznej dla pakietu Office 2003 8 kwietnia 2014. Ta zmiana wpłynęła na Twoje aktualizacje oprogramowania i opcje zabezpieczeń. Dowiedz się, co to oznacza dla Ciebie i jak zapewnić sobie kontynuację ochrony.

Streszczenie
Dzięki automatyzacji programu Excel można przesłać zawartość zestawu rekordów ADO do arkusza programu Microsoft Excel. Metoda, której można użyć, jest zależna od automatyzowanej wersji programu Excel. W przypadku programów Excel 97, Excel 2000 i Excel 2002 dostępna jest metoda CopyFromRecordset, której można użyć do transferu zestawu rekordów do zakresu. Metoda CopyFromRecordset w programach Excel 2000 i Excel 2002 umożliwia kopiowanie zestawu rekordów DAO lub ADO. Jednak metoda CopyFromRecordset w programie Excel 97 obsługuje tylko zestawy rekordów DAO. Aby przesłać zestaw rekordów ADO do programu Excel 97, można utworzyć tablicę z zestawu rekordów, a następnie wypełnić zakres zawartością tablicy.

W tym artykule omówiono obie metody. Przedstawiony przykładowy kod demonstruje sposób transferu 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 demonstruje sposób kopiowania zestawu rekordów ADO do arkusza programu Microsoft Excel przy użyciu automatyzacji z programu Microsoft Visual Basic. Ten kod najpierw sprawdza wersję programu Excel. Jeżeli zostanie wykryty program Excel 2000 lub Excel 2002, używana jest metoda CopyFromRecordset, ponieważ jest efektywna i wymaga mniejszej ilości kodu. Jeżeli jednak zostanie wykryty program Excel 97 lub starsza wersja programu Excel, zestaw rekordów jest najpierw kopiowany do tablicy przy użyciu metody GetRows obiektu zestawu rekordów ADO. Tablica jest następnie transponowana tak, aby rekordy znajdowały się 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 pojedynczym kroku, zamiast w pętli dla każdej komórki arkusza.)

W przykładzie kodu wykorzystano przykładową bazę danych Northwind uwzględnioną w pakiecie Microsoft Office. Jeżeli domyślny folder został wybrany podczas instalowania pakietu Microsoft Office, ta baza danych znajduje się w następującej lokalizacji:

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

Jeżeli na danym komputerze baza danych Northwind znajduje się w innym folderze, konieczne jest edytowanie ścieżki bazy danych w poniższym kodzie.

Jeżeli w danym systemie baza danych Northwind nie jest zainstalowana, można zainstalować przykładowe bazy danych przy użyciu opcji Dodaj/Usuń programy dla instalacji pakietu Microsoft Office.

Uwaga: Baza danych Northwind nie jest instalowana podczas instalacji pakietu Microsoft Office 2007. Aby uzyskać bazę danych Northwind 2007, odwiedź następującą witrynę firmy Microsoft w sieci Web:

Kroki tworzenia przykładu

  1. Uruchom program Visual Basic i utwórz nowy projekt Standard EXE. Domyślnie zostanie utworzony formularz Form1.
  2. Dodaj formant CommandButton do formularza Form1.
  3. Kliknij polecenie References (Odwołania) w menu Project (Projekt). Dodaj odwołanie do biblioteki Microsoft ActiveX Data Objects 2.1 Library.
  4. 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 = NothingEnd SubFunction 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 = tempArrayEnd Function
  5. Naciśnij klawisz F5, aby uruchomić projekt. Pojawia się formularz Form1.
  6. Kliknij formant CommandButton na formularzu Form1 i zauważ, że zawartość tabeli Orders (Zamówienia) jest wyświetlana w nowym arkuszu w programie Excel.
Korzystanie z metody CopyFromRecordset

Metoda CopyFromRecordset jest preferowana ze względu na jej efektywność i wydajność. Program Excel 97 obsługuje tylko zestawy rekordów DAO przy użyciu metody CopyFromRecordset, dlatego podczas próby przekazania zestawu rekordów ADO do metody CopyFromRecordset przy użyciu programu Excel 97 zgłaszany jest następujący błąd:
Błąd wykonania „430” :
Klasa nie obsługuje automatyzacji lub oczekiwanego interfejsu.
W przykładzie kodu można uniknąć tego błędu, sprawdzając wersję Excel, tak aby nie używać metody CopyFromRecordset dla wersji 97.

Uwaga: Podczas korzystania z metody CopyFromRecordset należy pamiętać o tym, że używany zestaw rekordów ADO lub DAO nie może zawierać pól obiektów OLE lub danych tablicowych, takich jak hierarchiczne zestawy rekordów. Jeżeli pola jednego z tych typów zostaną uwzględnione w zestawie rekordów, metoda CopyFromRecordset zawiedzie i zostanie zgłoszony następujący błąd:
Błąd wykonania -2147467259:
Metoda CopyFromRecordset obiektu Range nie powiodła się.
Korzystanie z metody GetRows

Jeżeli zostanie wykryty program Excel 97, należy użyć metody GetRows zestawu rekordów ADO w celu skopiowania zestawu rekordów do tablicy. Jeżeli tabela zwrócona przez metodę GetRows zostanie przypisana do zakresu komórek w arkuszu, dane są umieszczane w kolumnach, a nie w wierszach. Na przykład, jeżeli zestaw rekordów zawiera dwa pola i 10 wierszy, tablica pojawia się w dwóch wierszach i 10 kolumnach. Konieczne jest więc transponowanie tablicy przy użyciu funkcji TransposeDim() przed przypisaniem tablicy do zakresu komórek. Podczas przypisywania tablicy do zakresu komórek należy uwzględnić następujące ograniczenia:

Następujące ograniczenia obowiązują podczas przypisywania tablicy do obiektu Range programu Excel:
  • Tablica nie może zawierać pól obiektów OLE lub danych tablicowych, takich jak hierarchiczne zestawy rekordów. Należy zauważyć, że przykład kodu sprawdza ten dodatkowy warunek i wyświetla komunikat „Array Field” (Pole tablicowe), aby poinformować użytkownika, że nie można wyświetlić danego pola w programie Excel.

  • Tablica nie może zawierać pól zawierających daty przed rokiem 1900. (Skorzystaj z łącza do artykułu z bazy wiedzy Microsoft Knowledge Base zamieszczonego w sekcji „Materiały referencyjne” .) Należy zauważyć, że w przykładzie kodu pola daty są formatowane jako ciągi Variant w celu uniknięcia tego potencjalnego problemu.
Należy zwrócić uwagę na użycie funkcji TransposeDim() do transponowania tablicy przed skopiowaniem jej do arkusza programu Excel. Zamiast tworzenia własnej funkcji w celu transponowania tablicy można użyć funkcji Transpose programu Excel przez zmodyfikowanie kodu przykładowego w celu przypisania tablicy do komórek w następujący sposób:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _      xlApp.WorksheetFunction.Transpose(recArray)				
Jeżeli zostanie podjęta decyzja o użyciu metody Transpose programu Excel zamiast funkcji TransposeDim() w celu transponowania tablicy, należy uwzględnić następujące ograniczenia metody Transpose:
  • Tablica nie może zawierać elementu większego niż 255 znaków.
  • Tablica nie może zawierać wartości zerowych (Null).
  • Liczba elementów w tablicy nie może przekraczać 5461.
Jeżeli powyższe ograniczenia nie zostaną wzięte pod uwagę podczas kopiowania tablicy do arkusza programu Excel, może wystąpić jeden z następujących błędów wykonania:
Błąd wykonania 13: Niezgodność typów
Błąd wykonania 5: Nieprawidłowy argument lub wywołanie procedury
Błąd wykonania 1004: Błąd zdefiniowany przez aplikację lub obiekt
Materiały referencyjne
Aby uzyskać dodatkowe informacje na temat ograniczeń przekazywania tablic do różnych wersji programu Excel, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
177991 XL: Limitations of Passing Arrays to Excel Using Automation
Aby uzyskać dodatkowe informacje, kliknij następujące numery artykułów w celu wyświetlenia tych artykułów z bazy wiedzy Microsoft Knowledge Base:
146406 XL: How to Retrieve a Table from Access into Excel Using DAO
215965 XL2000: 12:00:00 AM Displayed for Dates Earlier Than 1900
243394 Jak używać klasy MFC do kopiowania zestawu rekordów DAO do programu Excel przy użyciu automatyzacji
247412 INFORMACJE: Metody transferu danych do programu Excel z programu Visual Basic
Transpose Mismatch XL2007
Właściwości

Identyfikator artykułu: 246335 — ostatni przegląd: 01/03/2008 19:59:00 — zmiana: 5.0

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

  • kbhowto kbautomation kbexpertiseinter KB246335
Opinia