Neste tópico discutirei como integrar o Access ao Excel.

Excel e Access compartilham de diversas características similares. Por exemplo, cada planilha do Excel é, na verdade, uma tabela. A grande diferença obvia é que o Excel é limitado no número de linhas e colunas (65.536 linhas e 256 colunas).

Esta limitação, via de regra, não é grande problema. Afinal, o Excel não é banco de dados, então, qualquer coisa que necessite de um banco de dados deve ser levada a cabo no Access. O Excel nós deixamos para “mastigar” os números, isto é, o Excel nós deixamos para a análise de dados.

Iniciarei com a Exportação de um relatório para o Excel. O processo é basicamente o mesmo que o utilizado no exemplo do Word. A diferença maior é realmente o aplicativo sendo utilizado.

Exportanto relatório do Access para Excel

Este exemplo requer pouquíssima modificação no exemplo do Word. Uma vez que o relatório tenha sido exportado teremos o seguinte cenário no Excel:

Figura 4‑1 Exportando relatórios

Se você possui algum relatório que se encaixa perfeitamente em algum modelo de formatação é possível utilizar tal modelo para formatar os dados exportados para o Excel. Caso contrário, isso teria que ser feito para termos algo mais apresentável.
Deixando a digressão de lado, podemos partir para o nosso código. As explicações estão contidas diretamente nele:

Private Sub  cmdExcel_Click()

'   Em caso de erro mostrar mensagem de erro
       On Error GoTo  Err_Handler

'   Declaração das variáveis
       Dim appXL       As  Excel.Application
       Dim wb           As Excel.Workbook
       Dim strNome     As String

'   Nome do arquivo de saída para o relatório
       strNome = CurrentProject.Path &  "\ModelosExcel\Excel1.xls"

'   Seleciona o relatório que deve ser passado  para a pasta de trabalho.
                   '   Note que esta rotina é similar a rotina de  impressao do relatório,
                   '   contudo, estamos "imprimindo" para  um arquivo XLS
       Select Case Me.ReportToPrint
                   '        Caso 1 passa o relatório "Employee Sales by Country"
           Case 1

'           Executa o comando  "OutputTo"
               DoCmd.OutputTo  acOutputReport, "Employee Sales by Country", acFormatXLS, strNome

'       Caso 2 passa o relatório "Sales  Totals by Amount"
           Case 2
                   '           Executa o comando  "OutputTo"
               DoCmd.OutputTo  acOutputReport, "Sales Totals by Amount", acFormatXLS, strNome
           Case Else
               MsgBox "Você não pode gerar  este relatório...", vbInformation
       End Select

'   Fecha o formulário
       DoCmd.Close  acForm, "Sales Reports Dialog"

'   Cria o aplicativo Excel
       Set appXL =  New Excel.Application
                   '   Abre o relatório no Excel
       Set wb =  appXL.Workbooks.Open(strNome)
                   '   Mostra o aplicativo Excel
       appXL.Visible = True

'   Remove os objetos da memória
       Set appXL = Nothing
       Set wb = Nothing
       Exit Sub
       
                   Err_Handler:
       MsgBox "Um erro ocorreu!",  vbCritical
       
       If Not appXL Is Nothing Then
            appXL.Quit
           Set appXL = Nothing
       End If
       
End Sub

Como observado, este exemplo é basicamente igual ao do Word. Adicionei este exemplo, pois muitas vezes passamos por cima de soluções por não conseguirmos visualizar similaridades entre os problemas. Problemas similares requerem soluções similares. Este tópico mostra exatamente isso.

Exportanto dados do Access para Excel: CopyFromRecorset

Resolvi dar um exemplo de como utiliza o CopyFromRecordset porque ele é bastante útil, mas também porque quase sempre retorna o erro “Método ‘CopyFromRecordset’ do object ‘Range’ Falhou”.

Este erro, eu discutirei no próximo tópico. Para o momento iremos concentro somente no método.

Este método é utilizado para copiar o conteúdo de um objeto Recordset. Vale lembrar que o objeto Recordset é membro tanto da classe de objetos ADO quanto DAO. Qual o leitor usa fica a critério seu. Aqui, utilizarei os objetos da biblioteca ADOBD.

Outra coisa que devemos ter em mente é que a cópia não inclui os nomes dos campos. Para tanto é necessário um loop para laçar os campos antes de efetuarmos a cópia do Recordset.

Figura 4‑2 Método CopyFromRecordset

Como já sabemos o formato final, podemos agora partir para o nosso código:

Sub exportarTabelas()
                   '   Declaração ds variáveis. Não esquecer de  referencias
                   '   os objetos do Excel.
     Dim appXL       As  Excel.Application
     Dim wb           As Excel.Workbook
     Dim ws           As Excel.Worksheet
     Dim rs           As ADODB.Recordset
     Dim cn           As ADODB.Connection
     Dim col          As Integer
     Dim tabela      As String

'   Define a conexão como sendo a conexão do  projeto atual
       Set cn =  CurrentProject.Connection
                   '    Cria um novo recordset
       Set rs = New  ADODB.Recordset
                   '   Cria o aplicativo Excel
       Set appXL =  New Excel.Application
                   '   Cria uma nova pasta de trabalho
       Set wb =  appXL.Workbooks.Add
                   '   Determina a planilha onde se deve colocar os  dados
       Set ws =  wb.Sheets(1)
                   '   A string referente a tabela que deve ser  exportada
       tabela = "[Pedidos]"

'   Abre o recordset
       rs.Open  tabela, cn, adOpenKeyset, adLockOptimistic

'   Com a planilha
       With ws
                   '       Para todos os campos da tabela
           For col = 0 To rs.Fields.Count - 1
                   '           Faz o loop escrevendo o nome dos  campos na primeira linha
                   '            coluna por coluna
                .Cells(1, col + 1).Value = rs.Fields(col).Name
           Next
                   '       Formato os campos para  "Negrito"
           .Range(wb.Sheets(1).Cells(1,  1), .Cells(1, rs.Fields.Count)) _
               .Font.Bold = True
                   '       Copia os valores do recordset
           .Range("A2").CopyFromRecordset  rs
                   '    Fecha o bloco With
       End With

'    Mostra o aplicativo Excel
        appXL.Visible = True
       
                   '   Limpa os objetos da memória
       Set cn = Nothing
       Set rs = Nothing
       Set appXL = Nothing
       Set wb = Nothing
     
End Sub

Corrigindo erro “Método 'CopyFromRecordset' do objeto 'Range' falhou

O exemplo não gera um erro, contudo, é comum esbarrarmos no erro a seguir:

Figura 4‑3

O erro pode ocorrer por vários motivos. Um dos motivos é tipo incorreto de dado e o outro é valor nulo. Se existir um valor nulo o método falhará retornando o erro acima.

Se o problema for do tipo de dado incorreto, exporte a tabela do Access e utilize esta tabela como o documento Excel para futuras cópias de recordsets.

                  Function exportar()
                   '   Dimensiona as variáveis
     Dim appXL            As  Excel.Application
      Dim wb               As  Excel.Workbook
      Dim rng              As  Excel.Range
      Dim rs               As  ADODB.Recordset
      Dim Linhas           As Long
      Dim colunas          As Long
      Dim minhaArray()     As Variant

'   Cria o aplicativo Excel
       Set appXL =  New Excel.Application

'   Abre uma pasta de trabalho  qualquer a qual receberá os dados
                   '   (é possível criar uma  pasta nova também)
       Set wb =  appXL.Workbooks.Open("C:\arquivo.xls")

'   Cria o recordset
        Set rs = New  ADODB.Recordset
       
                   '   Abre o recordset
        rs.Open "[tabela]",  CurrentProject.Connection, adOpenKeyset

'   Redimentsiona a array
        ReDim minhaArray(1 To rs.RecordCount + 1, 1 To  rs.Fields.Count + 1)

'   Percorre o recordset e  armazena os valores na array
       For Linhas = 1 To rs.RecordCount
            For colunas = 1 To  rs.Fields.Count
               If  IsNull(rs(colunas - 1)) Then
                   minhaArray(Linhas, colunas) = Empty
               Else
                   minhaArray(Linhas, colunas) =  rs(colunas - 1)
               End If
            Next
              rs.MoveNext
        Next

'   Redimensiona o tamando do  range que receberá os dados
       Set rng =  Range("A1").Range(Cells(1, 1), _
            Cells(rs.RecordCount, rs.Fields.Count))

'   "Dump" a array no range
        rng.Value = minhaArray()

'   Mostra o Excel
       appXL.Visible = True
       
                   '   Limpa a memória
        Set appXL = Nothing
        Set rs = Nothing

End Function

O método acima funciona relativamente bem. Mas como “relativamente bem” não é o mesmo que “sem problemas”, ele ainda está longe de ser um método perfeito.

Em caso de muitos dados, o leitor pode se encontrar na situação:

Figura 4‑4

Obviamente, voltamos ao problema inicial: como exportar os dados sem que o processo falhe?

Uma excelente solução a qual dificilmente lhe dará dor de cabeça é uma solução extremamente simples:

                  Sub exportar()
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
         "tabela",  "C:\arquivo.xls"
 End Sub