Como transferir dados de um Recordset do ADO para o Excel com automação

Artigo
06/20/2023
Aplica-se a:

Excel 2007, Excel 2003, Excel 2002, Excel 2000, Excel 97

Resumo

Você pode transferir o conteúdo de um conjunto de registros do ADO para uma planilha do Microsoft Excel automatizando o Excel. A abordagem que você pode usar depende da versão do Excel que você está automatizando. Excel 97, Excel 2000 e Excel 2002 têm um método CopyFromRecordset que você pode usar para transferir um conjunto de registros para um intervalo. CopyFromRecordset no Excel 2000 e 2002 pode ser usado para copiar um conjunto de registros DOO ou ADO. No entanto, CopyFromRecordset no Excel 97 dá suporte apenas a conjuntos de registros DAO. Para transferir um conjunto de registros do ADO para o Excel 97, você pode criar uma matriz do conjunto de registros e preencher um intervalo com o conteúdo dessa matriz.

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

Informações adicionais

O exemplo de código fornecido abaixo mostra como copiar um conjunto de registros do ADO para uma planilha do Microsoft Excel usando a automação do Microsoft Visual Basic. O código primeiro verifica a versão do Excel. Se o Excel 2000 ou 2002 for detectado, o método CopyFromRecordset será usado porque ele é eficiente e requer menos código. No entanto, se o Excel 97 ou anterior for detectado, o conjunto de registros será copiado primeiro para uma matriz usando o método GetRows do objeto do conjunto de registros do ADO. Em seguida, a matriz é transposta para 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 para uma planilha do Excel por meio da atribuição da matriz a um intervalo de células. (A matriz é copiada em uma etapa em vez de executar um loop 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:

\Arquivos de Programas\Microsoft Office\Office\Samples\Northwind.mdb

Se o banco de dados Northwind estiver localizado em uma pasta diferente no computador, você precisará editar o caminho do banco de dados no código fornecido abaixo.

Se você não tiver o banco de dados Northwind instalado em seu sistema, poderá usar a opção Adicionar/Remover para a instalação do Microsoft Office para instalar os bancos de dados de exemplo.

Nota O banco de dados Northwind não é instalado quando você instala o Microsoft Office 2007. Para obter o Northwind 2007, visite o seguinte site da Microsoft:

Temas de modelos do & Office

Etapas para criar exemplo

Inicie o Visual Basic e crie um novo projeto EXE Standard. O Form1 é criado por padrão.
Adicione um CommandButton ao Form1.
Clique em Referências do menu Projeto. Adicione uma referência à Biblioteca do Microsoft ActiveX Data Objects 2.1.

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

Pressione a tecla F5 para executar o projeto. Form1 é exibido.
Clique no CommandButton no Form1 e observe que o conteúdo da tabela Pedidos é exibido em uma nova pasta de trabalho no Excel.

Usando CopyFromRecordset

Para eficiência e desempenho, CopyFromRecordset é o método preferencial. Como o Excel 97 dá suporte apenas a conjuntos de registros DAO com CopyFromRecordset, se você tentar passar um conjunto de registros do ADO para CopyFromRecordset com o Excel 97, receberá o seguinte erro:

Erro de tempo de execução 430: a classe não dá suporte à Automação ou não dá suporte à interface esperada. No exemplo de código, você pode evitar esse erro verificando a versão do Excel para que você não use CopyFromRecordset para a versão 97.

Nota Ao usar CopyFromRecordset, você deve estar ciente de que o conjunto de registros ADO ou DAO usado não pode conter campos de objeto OLE ou dados de matriz, como conjuntos de registros hierárquicos. Se você incluir campos de qualquer tipo em um conjunto de registros, o método CopyFromRecordset falhará com o seguinte erro:

Erro em tempo de execução -2147467259: Falha no método CopyFromRecordset do intervalo de objetos.

Usando GetRows

Se o Excel 97 for detectado, use o método GetRows do conjunto de registros do 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 passarão pelas colunas em vez de descer as linhas. Por exemplo, se o conjunto de registros tiver dois campos e 10 linhas, a matriz aparecerá como duas linhas e 10 colunas. Portanto, você precisa transpor a matriz usando sua função TransposeDim() antes de atribuir a matriz ao intervalo de células. Ao atribuir uma matriz a um intervalo de células, há algumas limitações a serem consideradas:

As seguintes limitações se aplicam ao atribuir uma matriz a um objeto Intervalo do Excel:

A matriz não pode conter campos de objeto OLE ou dados de matriz, 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 tenham uma data anterior ao ano 1900. (Consulte a seção "Referências" para um link de artigo da Base de Dados de Conhecimento Microsoft.) Observe que o exemplo de código formatada campos Date como cadeias de caracteres variantes para evitar esse problema potencial.

Observe o uso da função TransposeDim() para transpor a matriz antes que a matriz seja copiada para a planilha do Excel. Em vez de criar sua própria função para transpor a matriz, você pode usar a função Transpor do Excel modificando o código de exemplo para atribuir a matriz às células, conforme mostrado 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, deverá estar ciente das seguintes limitações com o método Transpor:

A matriz não pode conter um elemento maior que 255 caracteres.
A matriz não pode conter valores Nulos.
O número de elementos na matriz não pode exceder 5461.

Se as limitações acima não forem levadas em consideração ao copiar uma matriz para uma planilha do Excel, poderá ocorrer um dos seguintes erros de tempo de execução:

Erro em tempo de execução 13: Incompatibilidade de tipo

Erro em tempo de execução 5: Procedimento inválido

call or argument Runtime Error 1004: Application defined or object defined error

Referências

Para obter informações adicionais sobre limitações de passagem de matrizes para várias versões do Excel, clique no seguinte número de artigo para exibir o artigo na Base de Dados de Conhecimento Microsoft:

177991 XL: limitações de passagem de matrizes para o Excel usando automação

247412 INFO: Métodos para transferir dados para o Excel do Visual Basic