MS Office Gurus

Objetivo da Macro:

•     Exportações de uma tabela de dados do Excel em um banco de dados, usando uma conexão ADO para passar strings SQL.

Exemplos de onde esta função brilha:

•  Funciona bem para o arquivamento de dados do Excel para um banco de dados Access.
•  Não insere linhas se todas as células na linha estiverem em branco (evita entrar registros completamente nulos no banco de dados).
•  Código é robusto o suficiente para suportar diferentes quantidades de colunas ou linhas.
•  A estrutura da tabela inteira do banco de dados não precisam ser reproduzidas no Excel, desde que o Sistema de Gerenciamento de Banco de Dados (SGBD) tenha valores padrões, ou pode aceitar valores nulos, para todos os campos omitidos.
•     Usa processamento de transações que reverter todas as transações se a atualização completa não for bem sucedida.

Pontos Fracos da Macro:

• Cuidados devem ser tomados na parte do Excel para garantir que todos os dados sejam válidos pelas seguintes razões: Este procedimento não valida qualquer um dos dados do Excel contra a estrutura da tabela do banco de dados.
• Passa todos os valores do Excel para o banco de dados na forma de textos, (não datas, valores, etc ..). Isso não parece causar um problema no Access, uma vez que os converte para o formato adequado, mas não se sabe se isto é verdade para outros SGBDs.
• Não é verdadeiramente um problema com o código, mas com o SQL no Access: você não pode passar uma variável em um campo que tem um nome que entra em conflito com um nome reservado, mesmo que totalmente qualificado. ou seja, um título de coluna de Data causará uma falha na instrução SQL INSERT.

Versões testadas:

Esta função foi testado com o Access e Excel 97, e Access & Excel 2003, e também deve trabalhar com o Access e Excel 2000 e 2002 (XP), sem quaisquer modificações. Para usá-lo com um Access 2007 ou superior formato (*. Accdb arquivos), você deve atualizar as seqüências de conexão (no topo muito do código - só descomentar a linha * accdb e remova a linha * mdb..). Se você quiser usar outro Database Management System (DBMS), ver "Adaptação isto a um SGBD diferente do Access" abaixo.

Instalação Requisitos pasta de trabalho:

    A estrutura da pasta de trabalho deve ser configurado como mostrado na ilustração a seguir:

• Por favor, note:
  • Células A3:F3 são nomeadas "tblHeadings"
  • Células A4:F11 são nomeadas "tblRecords"
• As tabelas podem ser expandidas ou constritas, mas as referências tblHeadings e tblRecords devem ser modificadas para representar as linhas corretamente para a rotina funcionar corretamente

Código VBA necessário:

• É preciso referenciar a biblioteca Microsoft ActiveX Data Objects
• O código abaixo deve ser colocado em um módulo padrão:

Sub DB_Insert_via_ADOSQL()
'Author       : Ken Puls (www.excelguru.ca)
'Macro purpose: To add record to Access database using ADO and SQL
'NOTE:  Reference to Microsoft ActiveX Data Objects Libary required
    Dim cnt As New ADODB.Connection, _
            rst As New ADODB.Recordset, _
            dbPath As String, _
            tblName As String, _
            rngColHeads As Range, _
            rngTblRcds As Range, _
            colHead As String, _
            rcdDetail As String, _
            ch As Integer, _
            cl As Integer, _
            notNull As Boolean, _
            sConnect As String
    'Set the string to the path of your database as defined on the worksheet
    dbPath = ActiveSheet.Range("B1").Value
    tblName = ActiveSheet.Range("B2").Value
    Set rngColHeads = ActiveSheet.Range("tblHeadings")
    Set rngTblRcds = ActiveSheet.Range("tblRecords")
    'Set the database connection string here
    'Private sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbPath & "';"     'For use with *.accdb files
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"     'For use with *.mdb files
    'Concatenate a string with the names of the column headings
    colHead = " ("
    For ch = 1 To rngColHeads.Count
        colHead = colHead & rngColHeads.Columns(ch).Value
        Select Case ch
            Case Is = rngColHeads.Count
                colHead = colHead & ")"
            Case Else
                colHead = colHead & ","
        End Select
    Next ch
    'Open connection to the database
    cnt.Open sConnect
    'Begin transaction processing
    On Error GoTo EndUpdate
    cnt.BeginTrans
    'Insert records into database from worksheet table
    For cl = 1 To rngTblRcds.Rows.Count
        'Assume record is completely Null, and open record string for concatenation
        notNull = False
        rcdDetail = "('"
        'Evaluate field in the record
        For ch = 1 To rngColHeads.Count
            Select Case rngTblRcds.Rows(cl).Columns(ch).Value
                    'if empty, append value of null to string
                Case Is = Empty
                    Select Case ch
                        Case Is = rngColHeads.Count
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
                        Case Else
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
                    End Select
                    'if not empty, set notNull to true, and append value to string
                Case Else
                    notNull = True
                    Select Case ch
                        Case Is = rngColHeads.Count
                            rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
                        Case Else
                            rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
                    End Select
            End Select
        Next ch
        'If record consists of only Null values, do not insert it to table, otherwise
        'insert the record
        Select Case notNull
            Case Is = True
                rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
            Case Is = False
                'do not insert record
        End Select
    Next cl
EndUpdate:
    'Check if error was encounted
    If Err.Number <> 0 Then
        'Error encountered.  Rollback transaction and inform user
        On Error Resume Next
        cnt.RollbackTrans
        MsgBox "There was an error.  Update was not succesful!", vbCritical, "Error!"
    Else
        On Error Resume Next
        cnt.CommitTrans
    End If
    'Close the ADO objects
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
    On Error GoTo 0
End Sub 

Como funciona:

• Variao caminho do banco, nome da tabela, coluna de gama detítulose os detalhes sãotodosatribuídos a variáveispara uso posteriorno procedimento
• Cabeçalhosdas colunas da tabelaestão todosunidos emuma string paraser usado mais tardeno processo.
• No casodoexemplo, a seqüênciaé "(CustID, Type, DatePaid, DateStart, DateEnd, Amount)"
• Aconexãoao banco de dadosé estabelecida
• Para cadalinha da tabela, cada campo é avaliada,e juntou-seemumacadeia de caracteres paraser usadono procedimento.Se o campotem um valor,o valor é adicionado, caso contrário, o valorNULLé adicionado.Três dosregistros doexemplo seria:

Cada linha é inserido no banco de dados, uma linha de cada vez, a menos que consiste puramente de valores nulos, caso em que ele é ignorado
     A ligação para o banco de dados é fechada

O resultado final:

• A imagem seguinte mostrao resultadodo processo,onde a tabelade Acessorealizadasem dados(mas não existe nabase de dados)antes do procedimentoa serexecutado:

Adaptando isso para um SGBD diferente do Access:

• Para utilizaresta rotina, comum SGBDque não sejao Microsoft Access, o Provedor deve seralterado para coincidir como SGBDque você desejausar
• Especificamente, esta seção do código acima:

'Open connection to the database
               cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & dbPath & ";"

• precisa ser atualizado para refletir o motor Provider adequada OLE banco de dados de Microsoft.Jet.
• Mais informações sobre uma enorme variedade de provedores de banco de dados OLE pode ser encontrada aqui.

Arquivos de exemplo:

Um arquivo compactado contendo tanto um banco de dados Access eo arquivo do Excel estão ligados. Você precisa atualizar o caminho do arquivo no arquivo do Excel antes de executar o arquivo de exemplo.

Baixar Exemplo

Este artigo foi sindicalizado com a permissão de Ken Puls de www.excelguru.ca
A versão original (escrita em inglês) é disponível em: Export Excel Range to Access