Exportar área de dados do Excel para o Access
- Última atualização em 19 Fevereiro 2012
- Acessos: 14276
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.
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