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

Tłumaczenia artykułów Tłumaczenia artykułów
Numer ID artykułu: 246335 - Zobacz jakich produktów dotyczą zawarte w tym artykule porady.
Rozwiń wszystko | Zwiń wszystko

Na tej stronie

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:
http://office.microsoft.com/pl-pl/templates/TC012289971033.aspx

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 = 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. 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

Właściwości

Numer ID artykułu: 246335 - Ostatnia weryfikacja: 3 stycznia 2008 - Weryfikacja: 5.0
Informacje zawarte w tym artykule dotyczą:
  • 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
Słowa kluczowe: 
kbhowto kbautomation kbexpertiseinter KB246335

Przekaż opinię

 

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