Como transferir dados de um Conjunto de Registos ADO para o Excel com automatização

Artigo
06/20/2023
Aplica-se a:

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

Resumo

Pode transferir os conteúdos de um pacote de registos ADO para uma página de trabalho do Microsoft Excel ao automatizar o Excel. A abordagem que pode utilizar depende da versão do Excel que está a automatizar. O Excel 97, Excel 2000 e Excel 2002 têm um método CopyFromRecordset que pode utilizar para transferir um conjunto de registos para um intervalo. O copyFromRecordset no Excel 2000 e 2002 pode ser utilizado para copiar um daO ou um registo ADO. No entanto, CopyFromRecordset no Excel 97 só suporta registos DAO. Para transferir um conjunto de registos do ADO para o Excel 97, pode criar uma matriz a partir do conjunto de registos e, em seguida, preencher um intervalo com os conteúdos da mesma.

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

Mais Informações

O exemplo de código fornecido abaixo mostra como copiar um registo ADO para uma ficha do Microsoft Excel através de automatização do Microsoft Visual Basic. Primeiro, o código verifica a versão do Excel. Se for detetado o Excel 2000 ou 2002, o método CopyFromRecordset é utilizado porque é eficiente e requer menos código. No entanto, se for detetado o Excel 97 ou anterior, o conjunto de registos é copiado primeiro para uma matriz através do método GetRows do objeto conjunto de registos ADO. Em seguida, a matriz é transposta para que os registos sejam na primeira dimensão (em linhas) e os campos na segunda dimensão (nas colunas). Em seguida, a matriz é copiada para uma mesma ao atribuir a matriz a um intervalo de células. (A matriz é copiada num passo, em vez de perdoá-la por cada célula na mesma.)

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

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

Se a base de dados Northwind estiver localizada numa pasta diferente no seu computador, tem de editar o caminho da base de dados no código fornecido abaixo.

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

Nota A base de dados da Northwind não é instalada quando instala o Microsoft Office 2007. Para obter a Northwind 2007, visite o seguinte site da Microsoft:

Temas de modelos & do Office

Passos para Criar Exemplo

Inicia o Visual Basic e crie um novo projeto EXE Padrão. O formulário1 é criado por predefinição.
Adicionar um BotãoDe Comando ao Formulário1.
Clique em Referências no menu Projeto. Adicione uma referência à Biblioteca Microsoft ActiveX Data Objects 2.1.

Colar o seguinte código na secção de código do Formulário1:

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

Prima a tecla F5 para executar o projeto. O formulário1 é exibido.
Clique no BotãoDe Comando no Formulário1 e tenha em atenção que o conteúdo da tabela Encomendas é apresentado num novo livro no Excel.

Utilizar CopyFromRecordset

Para eficiência e desempenho, CopyFromRecordset é o método preferencial. Uma vez que o Excel 97 só suporta registos DAO com o CopyFromRecordset, se tentar passar um ficheiro de registos ADO para CopyFromRecordset com o Excel 97, receberá o seguinte erro:

Erro de tempo de utilização 430: A turma não suporta Automatização ou não suporta a interface esperada. No exemplo de código, pode evitar este erro ao selecionar a versão do Excel para que não utilize CopyFromRecordset para a versão de 97.

Nota Ao utilizar o CopyFromRecordset, deve ter em atenção que o conjunto de registos ADO ou DAO que utiliza não pode conter campos de objetos OLE ou dados de matriz, como conjuntos de registos hierárquicos. Se incluir campos de um dos tipos numet de registos, o método CopyFromRecordset falha com o seguinte erro:

Erro de tempo de executar -2147467259: Falha no Método CopyFromRecordset do Intervalo de Objetos.

Utilizar GetRows

Se o Excel 97 for detetado, 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 mesma, os dados vão ao longo das colunas em vez de ao longo das linhas. Por exemplo, se o conjunto de registos tiver dois campos e 10 linhas, a matriz aparecerá em duas linhas e 10 colunas. Assim, tem de transpor a matriz utilizando a função TransporDim() 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 ter em conta:

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

A matriz não pode conter campos de objetos OLE ou dados de matriz, como conjuntos de registos hierárquicos. Repare que o exemplo de código verifica esta condição e apresenta "Campo de Matriz" para que o utilizador tenha em atenção que não é possível apresentar o campo no Excel.
A matriz não pode conter campos de Data que tenham uma data anterior ao ano 1900. (Consulte a secção "Referências" de uma ligação de artigo da Base de Dados de Conhecimento Microsoft.) Tenha em atenção que o exemplo de código formata os campos data como cadeias de variantes para evitar este possível problema.

Repare na utilização da função TransporDim() para transpor a matriz antes de a matriz ser copiada para a mesma. Em vez de criar a sua própria função para transpor a matriz, pode utilizar a função Transpor do Excel ao modificar o código de exemplo para atribuir a matriz às células, conforme apresentado abaixo:

   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)

Se decidir utilizar o método de Transposição do Excel em vez da função TransporDim() para transpor a matriz, deve ter em atenção as seguintes limitações com o método de Transpor:

A matriz não pode conter um elemento com mais de 255 carateres.
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 são levadas em consideração ao copiar uma matriz para uma livro do Excel, poderá ocorrer um dos seguintes erros em tempo de resposta:

Erro 13 de Tempo de utilização: Escrever Incomparável

Erro 5 de Tempo de executar: procedimento inválido

chamada ou argumento Erro 1004 em Tempo de Reposição: Aplicação definida ou objeto definido como erro

Referências

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

177991 XL: Limitações da passagem de Matrizes ao Excel Através da Automatização

247412 INFORMAÇÕES: Métodos para Transferir Dados para o Excel a partir do Visual Basic