Перенесення даних з набору записів ADO до Excel за допомогою автоматизації

Переклади статей Переклади статей
Номер статті: 246335 - Показ продуктів, яких стосується ця стаття.
Розгорнути все | Згорнути все

На цій сторінці

ПІДСУМКИ

Вміст набору записів ADO можна перенести до книги Microsoft Excel за допомогою автоматизації Excel. Спосіб реалізації залежить від версії Excel, яка автоматизується. В Excel 97, Excel 2000 та Excel 2002 є метод CopyFromRecordset, який можна використовувати для перенесення набору записів до діапазону. Метод CopyFromRecordset в Excel 2000 та 2002 можна використовувати для копіювання наборів записів DAO та ADO. Проте в Excel 97 метод CopyFromRecordset підтримує тільки набори записів DAO. Для перенесення набору записів ADO до Excel 97 з набору записів можна створити масив, а потім заповнити діапазон вмістом цього масиву.

У цій статті описуються обидва способи. Представлений зразок коду демонструє, як можна перенести набір записів ADO до Excel 97, Excel 2000, Excel 2002, Excel 2003 або Excel 2007.

ДОДАТКОВІ ВІДОМОСТІ

У наданому нижче зразку коду Microsoft Visual Basic показано, як за допомогою автоматизації скопіювати набір записів ADO до книги Microsoft Excel. Спочатку код перевіряє версію Excel. Якщо виявлено версію Excel 2000 або 2002, використовується метод CopyFromRecordset, тому що він ефективний та потребує менше коду. Проте, якщо виявлено версію Excel 97 або ранішу, набір записів спочатку копіюється до масиву за допомогою методу GetRows об'єкта ADO recordset. Потім масив транспонується таким чином, що записи опиняються в першій розмірності (в рядках), а поля — у другій розмірності (у стовпцях). Потім масив копіюється до книги Excel за допомогою прив'язки масива до діапазону клітинок. (Масив копіється за один крок, на відміну від циклічного копіювання вмісту у кожну клітинку книги.)

У коді використовується база даних "Борей", яка входить до складу Microsoft Office. Якщо під час інсталяції Microsoft Office вибрано папку за промовчанням, база даних буде розміщена в папці

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

Якщо базу даних "Борей" інстальовано на комп'ютері в іншій папці, необхідно відредагувати шлях до бази даних у наданому нижче коді.

Можливо, ви одразу не інсталювали базу даних "Борей" в системі. Тоді можна скористатися параметром "Додати/Видалити" програми інсталяції Microsoft Office, щоб інсталювати зразкові бази даних.

Примітка. Під час інсталяції Microsoft Office 2007 база даних "Борей" не інсталюється. Щоб отримати базу даних "Борей" версії 2007, відвідайте веб-сайт корпорації Майкрософт:
http://office.microsoft.com/uk-ua/templates/TC012289971058.aspx

Кроки створення зразка

  1. Запустіть Visual Basic та створіть проект Standard EXE. Форма Form1 створюється за промовчанням.
  2. Додайте до Form1 об'єкт CommandButton.
  3. Клацніть References (Посилання) в меню Project (Проект). Додайте посилання на бібліотеку Microsoft ActiveX Data Objects 2.1 Library.
  4. Вставте наступний код до розділу коду 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
        
        ' Встановити шлях до бази даних "Борей" як значення строкової змінної
        strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb"
      
        ' Відкрити підключення до бази даних
        cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strDB & ";"
        
        ''Якщо використовутьсєя база даних "Борей" для Access 2007
        ''закоментуйте попередній код та розкоментуйте наступний код.
        'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        '    "Data Source=" & strDB & ";"
            
        ' Відкрити набір записів на основі таблиці "Замовлення"
        rst.Open "Select * From Orders", cnt
        
        ' Створити новий екземпляр Excel та додати книгу
        Set xlApp = CreateObject("Excel.Application")
        Set xlWb = xlApp.Workbooks.Add
        Set xlWs = xlWb.Worksheets("Sheet1")
      
        ' Відкрити Excel та передати керування Excel користувачеві
        xlApp.Visible = True
        xlApp.UserControl = True
        
        ' Скопіювати назви полів до першого рядку книги
        fldCount = rst.Fields.Count
        For iCol = 1 To fldCount
            xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
        Next
            
        ' Перевірити версію Excel
        If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
            'EXCEL 2000,2002,2003 або 2007: використовувати метод CopyFromRecordset
             
            ' Скопіювати набір записів до книги, починаючи з клітинки A2
            xlWs.Cells(2, 1).CopyFromRecordset rst
            'Примітка: Метод CopyFromRecordset буде невдалим, якщо набір записів
            'містить поле об'єкту OLE або дані-масиви, наприклад
            ' ієрархічні набори записів
            
        Else
            'EXCEL 97 або раніша версія: використати метод GetRows, а потім скопіювати масив до Excel
        
            ' Скопіювати набір записів до масива
            recArray = rst.GetRows
            'Примітка: Метод GetRows повертає масив з нульовим першим індексом, у якому перша
            'розмірність містить поля, а друга розмірність
            'містить записи. Масив транспонується таким чином, що
            'перша розмірність населяється записами, дозволяючи
            'даним під час копіювання до Excel отримувати правильну форму
            
            ' Визначити число записів
    
            recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
            
    
            ' Перевірити масив на наявність неправильного вмісту під час
            ' копіювання масиву до книги Excel
            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))
                    ' Перевірити поля об'єкту OLE або поля масиву
                    ElseIf IsArray(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = "Array Field"
                    End If
                Next iRow 'next record
            Next iCol 'next field
                
            ' Транспонувати та копіювати масив до книги,
            ' починаючи з клітинки A2
            xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
                TransposeDim(recArray)
        End If
    
        ' Автоматично регулювати ширину стовпців та висоту рядків
        xlApp.Selection.CurrentRegion.Columns.AutoFit
        xlApp.Selection.CurrentRegion.Rows.AutoFit
    
        ' Закрити об'єкти ADO
        rst.Close
        cnt.Close
        Set rst = Nothing
        Set cnt = Nothing
        
        ' Очистити посилання Excel
        Set xlWs = Nothing
        Set xlWb = Nothing
    
        Set xlApp = Nothing
    
    End Sub
    
    
    Function TransposeDim(v As Variant) As Variant
    ' Функція для транспонування масива з нульовим першим індексом (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. Натисніть клавішу F5, щоб запустити проект. З'явиться форма Form1.
  6. Клацніть кнопку CommandButton на Form1 та зверніть увагу, що вміст таблиці "Замовлення" відображається в новій книзі Excel.
Використання методу CopyFromRecordset

Метод CopyFromRecordset є найбільш ефективним та продуктивним. Excel 97 з методом CopyFromRecordset підтримує тільки набори записів DAO. Тому, якщо спробувати передати набір записів ADO методом CopyFromRecordset з Excel 97, з'явиться наступна помилка:
Помилка виконання 430:
клас не підтримує автоматизацію або очікуваний інтерфейс.
У зразку коду цієї помилки можна уникнути шляхом перевірки версії Excel, щоб гарантовано не використовувати метод CopyFromRecordset з версією Excel 97.

Примітка. Використовуючи метод CopyFromRecordset, необхідно враховувати, що застосовуваний набір записів ADO або DAO не може містити поля об'єктів OLE або такі дані-масиви, як ієрархічні набори записів. Якщо включити поля одного з цих типів до набору записів, метод CopyFromRecordset виконається з такою помилкою:
Помилка виконання -2147467259:
не вдалося виконати метод CopyFromRecordset об'єкту Range.
Використання методу GetRows

Якщо виявлено Excel 97, скористайтеся методом GetRows для набору записів ADO, щоб скопіювати набір записів до масиву. Якщо прив'язати масив, який повернув метод GetRows, до діапазону клітинок книги, дані розмістяться у стовпчиках, замість рядків. Наприклад, якщо набір записів містить два поля та 10 рядків, масив буде виглядати як два рядки та 10 стовпчиків. Тому, перш ніж прив'язувати масив до діапазону клітинок, необхідно транспонувати масив функцією TransposeDim(). Під час прив'язки масива до діапазону клітинок необхідно враховувати декілька обмежень.

Наступні обмеження застосовуються під час призначення масива об'єкту Excel Range:
  • Масив не може містити поля об'єктів OLE або такі дані-масиви, як ієрархічні набори записів. Зверніть увагу, що зразок коду перевіряє цю умову і відображає повідомлення "Поле масиву", яке попереджає користувача, що це поле неможливо відобразити в Excel.

  • Масив не може містити поля "Дата", які мають значення до 1900 року. (Див. у розділі "Посилання" посилання на статтю бази знань Microsoft Knowledge Base.) Зауважте, що, щоб уникнути потенційних проблем, зразок коду призначає полям "Дата" строковий тип Variant.
Зауважте також, що, перш ніж масив буде скопійовано до книги Excel, необхідно скористатися функцією TransposeDim() для транспонування масиву. Замість створення власної функції для транспонування масиву можна скористатися функцією Excel Transpose шляхом зміни зразка коду для призначення масиву клітинкам, як показано нижче:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
Якщо для транспонування масиву вирішите використовувати метод Excel Transpose, замість функції TransposeDim(), необхідно враховувати деякі обмеження методу Transpose:
  • Масив не може містити елемент обсягом більше 255 символів.
  • Масив не може містити значення Null.
  • Число елементів у масиві не може перевищувати 5461.
Якщо під час копіювання масиву до книги Excel зазначені вище обмеження не брати до уваги, може статися одна з наступних помилок виконання:
Помилка виконання 13: невідповідність типу
Помилка виконання 5: неправильний виклик процедури або аргумент
Помилка виконання 1004: помилка визначеного застосунку або об'єкта

ПОСИЛАННЯ

Щоб отримати додаткові відомості про обмеження під час передавання масивів до різних версій Excel, клацніть номер статті бази знань Microsoft Knowledge Base:
177991 XL: Обмеження під час передавання масивів до Excel за допомогою автоматизації (Це посилання може вказувати на матеріали, повністю або частково викладені англійською мовою.)
Щоб отримати додаткові відомості, клацніть відповідні номери статей бази знань Microsoft Knowledge Base:
146406 XL: Відкривання таблиці Access в Excel за допомогою DAO (Це посилання може вказувати на матеріали, повністю або частково викладені англійською мовою.)
215965 XL2000: 12:00:00 AM відображається для дат, раніших ніж 1900 (Це посилання може вказувати на матеріали, повністю або частково викладені англійською мовою.)
243394 Використання MFC для копіювання набору записів DAO до Excel за допомогою автоматизації (Це посилання може вказувати на матеріали, повністю або частково викладені англійською мовою.)
247412 Способи передачі даних з Visual Basic до Excel

Властивості

Номер статті: 246335 - Востаннє переглянуто: 28 листопада 2007 р. - Редакція: 5.0
ЗАСТОСОВУЄТЬСЯ ДО:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 - стандартний випуск
  • Microsoft Excel 2000 - стандартний випуск
  • Microsoft Excel 97 - стандартний випуск
  • 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

Надіслати відгук

 

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