Como transferir dados de um conjunto de registros ADO para o Excel com a automação

Traduções deste artigo Traduções deste artigo
ID do artigo: 246335 - Exibir os produtos aos quais esse artigo se aplica.
Expandir tudo | Recolher tudo

Neste artigo

Sumário

É possível transferir o conteúdo de um conjunto de registros ADO para uma planilha pela automação do Excel. A abordagem que pode ser usada depende da versão do Excel que você está automatizando. O Excel 97, o Excel 2000 e o Excel 2002 têm um método CopyFromRecordset que pode ser usado para transferir um conjunto de registros para um intervalo. O CopyFromRecordset no Excel 2000 e 2002 pode ser usado para copiar um conjunto de registros DAO ou ADO. Porém, o CopyFromRecordset no Excel 97 suporta apenas conjuntos de registros DAO. Para transferir um conjunto de registros ADO para o Excel 97, é possível criar uma matriz a partir do conjunto de registros e preencher um intervalo com o conteúdo da matriz.

Este artigo discute as duas abordagens. O código de exemplo apresentado ilustra como você pode transferir um conjunto de registros ADO para o Excel 97, Excel 2000, Excel 2002, Excel 2003 ou Excel 2007.

Mais Informações

O código de exemplo fornecido abaixo mostra como copiar um conjunto de registros ADO em uma planilha do Microsoft Excel usando a automação do Microsoft Visual Basic. Primeiro, o código verifica a versão do Excel. Se o Excel 2000 ou 2002 for detectado, o método CopyFromRecordset será usado pois é eficiente e solicita menos código. Entretanto, se o Excel 97 ou versão anterior for detectado, o conjunto de registros será copiado primeiro em uma matriz usando o método GetRows do objeto do conjunto de registros ADO. A matriz é então transposta de modo que os registros estejam na primeira dimensão (em linhas), e os campos estejam na segunda dimensão (em colunas). Em seguida, a matriz é copiada em uma planilha do Excel ao atribuí-la a um intervalo de células. (A matriz é copiada em uma etapa, em vez de fazer loops em cada célula na planilha.)

O exemplo de código usa o banco de dados de exemplo Northwind incluído no Microsoft Office. Se você selecionou a pasta padrão ao instalar o Microsoft Office, o banco de dados estará localizado em:

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

Se o banco de dados Northwind estiver localizado em uma pasta diferente no seu computador, será necessário editar o caminho do banco de dados no código fornecido abaixo.

Se você não tiver o banco de dados Northwind instalado no seu sistema, será possível usar a opção Adicionar/Remover para que a configuração do Microsoft Office instale os bancos de dados de exemplo.

Observação O banco de dados Northwind não é instalado ao instalar o Microsoft Office 2007. Para obter o Northwind 2007, visite o seguinte site da Microsoft:
http://office.microsoft.com/pt-br/templates/TC012289971046.aspx?pid=CT101428651046

Etapas para criar exemplo

  1. Inicie o Visual Basic e crie um novo projeto Standard EXE. O Form1 é criado por padrão.
  2. Adicione um CommandButton ao Form1.
  3. Clique em Referências no menu Projeto. Adicione uma referência à Biblioteca do Microsoft ActiveX Data Objects 2.1.
  4. Cole o seguinte código na seçã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\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. Pressione a tecla F5 para executar o projeto. O Form1 é exibido.
  6. Clique em CommandButton no Form1 e observe que o conteúdo da tabela Pedidos é exibido em uma nova pasta de trabalho do Excel.
Usando o CopyFromRecordset

Para eficiência e desempenho, CopyFromRecordset é o método preferido. Como o Excel 97 suporta apenas conjuntos de registros DAO com o CopyFromRecordset, se você tentar transmitir um conjunto de registros ADO para o CopyFromRecordset com o Excel 97, a seguinte mensagem de erro será exibida:
Erro de tempo de execução 430:
A classe não suporta a Automação ou não suporta a interface esperada.
No exemplo de código, é possível evitar esse erro ao verificar a versão do Excel para não usar o CopyFromRecordset na versão 97.

Observação Ao usar o CopyFromRecordset, você deve estar ciente de que o conjunto de registros ADO ou DAO usado não pode conter campos objeto OLE ou dados de matriz, tais como conjuntos de registros hierárquicos. Se você incluir campos de ambos os tipos em um conjunto de registros, o método CopyFromRecordset irá falhar com o seguinte erro:
Erro de tempo de execução -2147467259:
Falha no método CopyFromRecordset do objeto Range.
Usando o método GetRows

Se o Excel 97 for detectado, use o método GetRows do conjunto de registros ADO para copiar o conjunto de registros em uma matriz. Se você atribuir a matriz retornada por GetRows a um intervalo de células na planilha, os dados irão para as colunas em vez de irem para as linhas. Por exemplo, se o conjunto de registros tiver dois campos e 10 linhas, a matriz será exibida como duas linhas e 10 colunas. Portanto, você precisa transpor a matriz usando a função TransposeDim() antes de atribuir a matriz ao intervalo de células. Ao atribuir uma matriz a um intervalo de células, existem algumas limitações a saber:

As limitações a seguir aplicam-se ao atribuir uma matriz a um objeto Range do Excel:
  • A matriz não pode conter campos objeto OLE ou dados de matriz, tais como conjuntos de registros hierárquicos. Observe que o exemplo de código verifica essa condição e exibe "Campo de Matriz" para que o usuário fique ciente de que o campo não pode ser exibido no Excel.

  • A matriz não pode conter campos Data que têm uma data anterior ao ano de 1900. (Consulte a seção "Referências" de um link da Base de Dados de Conhecimento Microsoft.) Observe que o exemplo de código formata os campos Data como seqüências variantes para evitar este possível problema.
Observe o uso da função TransposeDim() para transpor a matriz antes da matriz ser copiada para a planilha do Excel. Em vez de criar sua própria função para transpor a matriz, é possível usar a função Transpor do Excel modificando o código de exemplo para atribuir a matriz às células como exibido abaixo:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
Se você decidir usar o método Transpor do Excel em vez da função TransposeDim() para transpor a matriz, você deverá estar ciente das seguintes limitações do método Transpor:
  • A matriz não pode conter um elemento maior que 255 caracteres.
  • A matriz não pode conter valor Nulo.
  • O número de elementos na matriz não pode exceder 5461.
Se as limitações acima não forem consideradas ao copiar uma matriz em uma planilha do Excel, um dos seguintes erros de tempo de execução poderá ocorrer:
Erro de tempo de execução 13: Tipo incompatível
Erro de tempo de execução 5: Argumento ou chamada de procedimento inválida
Erro de tempo de execução 1004: Erro definido por objeto ou definido por aplicativo

Referências

Para obter informações adicionais sobre as limitações ao transferir matrizes para várias versões, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft (a página pode estar em inglês):
177991 XL: Limitações ao transferir matrizes para o Excel usando automação
Para obter informações adicionais, clique nos números abaixo para ler os artigos na Base de Dados de Conhecimento Microsoft (alguns artigos podem estar em inglês):
146406 XL: Como recuperar uma tabela do Access no Excel usando DAO
215965 XL2000: 12:00:00 AM exibido para datas anteriores ao ano de 1900
243394 Como usar MFC para copiar um conjunto de registros DAO no Excel com a automação
247412 INFO: Métodos para transferir dados do Visual Basic para o Excel

Propriedades

ID do artigo: 246335 - Última revisão: sexta-feira, 28 de dezembro de 2007 - Revisão: 5.0
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: 
kbhowto kbautomation kbexpertiseinter KB246335

Submeter comentários

 

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