DetailPage-MSS-KB

Base de Dados de Conhecimento

Artigo: 246335 - Última revisão: terça-feira, 25 de Março de 2008 - Revisão: 5.0

Nesta página

Sumário

É possível transferir o conteúdo de um conjunto de dados ADO para uma folha de cálculo do Microsoft Excel automatizando o Excel. A abordagem que poderá utilizar depende da versão do Excel que está a automatizar. O Excel 97, o Excel 2000 e o Excel 2002 têm um método CopyFromRecordset que pode ser utilizado para transferir um conjunto de registos para um intervalo. No Excel 2000 e 2002, CopyFromRecordset pode ser utilizado para copiar um conjunto de registos DAO ou ADO. No entanto, no Excel 97, CopyFromRecordset só suporta conjuntos de registos DAO. Para transferir um conjunto de registos ADO para o Excel 97, pode criar uma matriz a partir do conjunto de registos e, em seguida, povoar o intervalo com o conteúdo dessa matriz.

Este artigo explica ambas as abordagens. O código de exemplo apresentado ilustra como pode transferir um conjunto de registos ADO para o Excel 97, Excel 2000, Excel 2002, Excel 2003 ou Excel 2007.

Mais Informação

O código de exemplo fornecido abaixo mostra como copiar um conjunto de registos ADO para uma folha de cálculo do Microsoft Excel utilizando a automatização a partir do Microsoft Visual Basic. Em primeiro lugar, o código verifica a versão do Excel. Se for detectado o Excel 2000 ou 2002, é utilizado o método CopyFromRecordset porque é eficiente e necessita de menos código. No entanto, se for detectado o Excel 97 ou uma versão anterior, o conjunto de registos é copiado primeiro para uma matriz, utilizando o método GetRows do objecto de conjunto de registos ADO. Em seguida, a matriz é transposta de modo a que os registos fiquem na primeira dimensão (em linhas) e os campos fiquem na segunda dimensão (em colunas). Depois disso, a matriz é copiada para uma folha de cálculo do Excel através da respectiva atribuição a um intervalo de células. (A matriz é copiada num passo, em vez de ser efectuado um ciclo por cada célula existente na folha de cálculo.)

O código de exemplo utiliza a base de dados de exemplo Adamastor, incluída com o Microsoft Office. Se tiver seleccionado a pasta predefinida quando instalou o Microsoft Office, a base de dados está localizada em:

\Programas\Microsoft Office\Office\Samples\Adamastor.mdb

Se a base de dados Adamastor estiver localizada noutra pasta do computador, terá de editar o caminho da base de dados existente no código fornecido abaixo.

Se não tiver a base de dados Adamastor instalada no sistema, poderá utilizar a opção Adicionar/Remover da configuração do Microsoft Office para instalar as bases de dados de exemplo.

Nota A base de dados Adamastor não é instalada durante a instalação do Microsoft Office de 2007. Para obter a base de dados Adamastor 2007, visite o seguinte Web site da Microsoft:
http://office.microsoft.com/pt-pt/templates/TC012289972070.aspx (http://office.microsoft.com/pt-pt/templates/TC012289972070.aspx)

Passos para Criar o Exemplo

  1. Inicie o Visual Basic e crie um novo projecto EXE Padrão. Por predefinição, é criado o Form1.
  2. Adicione um CommandButton ao Form1.
  3. Clique em References no menu Project. Adicione uma referência a Microsoft ActiveX Data Objects 2.1 Library.
  4. Cole o código seguinte na secção de código do 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\Adamastor.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. Prima a tecla F5 para executar o projecto. É apresentado o Form1.
  6. Clique no the CommandButton existente no Form1 e note que o conteúdo da tabela Encomendas é apresentado num livro novo no Excel.
Utilizar CopyFromRecordset

Para uma maior eficiência e um melhor desempenho, CopyFromRecordset é o método preferido. Visto que o Excel 97 só suporta conjuntos de registos DAO com CopyFromRecordset, se tentar passar um conjunto de registos ADO para CopyFromRecordset com o Excel 97, receberá o erro seguinte:
Erro durante a execução 430:
A classe não suporta Automatização ou não suporta a interface esperada.
No código de exemplo, poderá evitar este erro verificando a versão do Excel, de modo a não utilizar CopyFromRecordset para a versão 97.

Nota Quando utilizar CopyFromRecordset, deverá estar ciente de que o conjunto de registos ADO ou DAO que utilizar não pode conter campos de objectos OLE ou de dados de matriz, tais como conjuntos de registos hierárquicos. Se incluir campos de qualquer um dos tipos num conjunto de registos, o método CopyFromRecordset falha com o seguinte erro:
Erro durante a execução -2147467259:
O método CopyFromRecordset do objecto Range falhou.
Utilizar GetRows

Se o Excel 97 for detectado, utilize o método GetRows do conjunto de registos ADO para copiar o conjunto de registos para uma matriz. Se atribuir a matriz devolvida por GetRows a um intervalo de células na folha de cálculo, os dados são distribuídos pelas colunas e não pelas linhas. Por exemplo, se o conjunto de registos tiver dois campos e 10 linhas, a matriz aparecerá como duas linhas e 10 colunas. Consequentemente, necessita de transpor a matriz utilizando a função TransposeDim() antes de atribuir a matriz ao intervalo de células. Quando atribuir uma matriz a um intervalo de células, deverá estar ciente de algumas limitações:

As limitações seguintes aplicam-se quando atribuir uma matriz a um objecto de Intervalo do Excel:
  • A matriz não pode conter campos de objectos OLE ou dados de matriz, tais como conjuntos de registos hierárquicos. Note que o código de exemplo verifica esta condição e apresenta "Array Field", para que o utilizador seja alertado para o facto de que o campo não pode ser apresentado no Excel.

  • A matriz não pode conter campos de Data com uma data anterior ao ano 1900. (Consulte a secção "Referências" para obter uma hiperligação para um artigo da Base de Dados de Conhecimento Microsoft.) Note que o código de exemplo formata os campos de Data como cadeias de variante, para evitar este problema potencial.
Repare na utilização da função TransposeDim() para transpor a matriz antes que esta seja copiada para a folha de cálculo do Excel. Em vez de criar uma função própria para transpor a matriz, poderá utilizar a função Transpose do Excel, modificando o código de exemplo para atribuir a matriz às células, conforme ilustrado abaixo:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
Se decidir utilizar o método Transpose do Excel em vez da função TransposeDim() para transpor a matriz, deverá estar ciente das limitações seguintes do método Transpose:
  • A matriz não pode conter um elemento superior a 255 caracteres.
  • A matriz não pode conter valores Nulos.
  • O número de elementos da matriz não pode exceder 5461.
Se as limitações acima indicadas não forem tomadas em consideração quando copiar uma matriz para uma folha de cálculo do Excel, poderá ocorrer um dos seguintes erros durante a execução:
Erro Durante a Execução 13: Tipo Incompatível
Erro Durante a Execução 5: Chamada de procedimento ou argumento inválido.
Erro Durante a Execução 1004: Erro definido pela aplicação ou definido pelo objecto

Referências

Para obter informações adicionais sobre as limitações da passagem de matrizes para as várias versões do Excel, clique no número de artigo que se segue para visualizar o artigo na Base de Dados de Conhecimento Microsoft:
177991  (http://support.microsoft.com/kb/177991/ ) XL: Limitações da Transferência de Matrizes para o Excel Utilizando Automatização
Para obter informações adicionais, clique nos números de artigo existentes abaixo para visualizar os artigos na Base de Dados de Conhecimento Microsoft:
146406  (http://support.microsoft.com/kb/146406/ ) XL: Como Obter uma Tabela do Access para o Excel Utilizando DAO
215965  (http://support.microsoft.com/kb/215965/ ) XL2000: 12:00:00 Apresentado para Datas Anteriores a 1900
243394  (http://support.microsoft.com/kb/243394/ ) Como Utilizar MFC para Copiar um Conjunto de Registos DAO para o Excel com Automatização
247412  (http://support.microsoft.com/kb/247412/ ) Métodos para Transferir Dados para o Excel a partir do Visual Basic

A informação contida neste artigo aplica-se a:
  • 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
Palavras-chave: 
kbexpertiseinter kbautomation kbhowto KB246335
Partilhar
Opções de suporte adicionais
Fóruns de Suporte da Comunidade Microsoft
Contacte-nos directamente
Encontre um parceiro certificado Microsoft
Loja Microsoft