Importar dados do excel para access VBA

come importar dados do excel no vba do access

Importar dados do excel para access VBA

importar excel para access via vba

Neste artigo, explicaremos como importar dados do Excel no Access utilizando a linguagem VBA. Especificamente, estaremos utilizando o Modelo de Objetos do Excel.

O leitor deve estar ciente de que esta não é a única abordagem para importar dados do Excel em uma tabela no Access. A escolha desta opção fornecerá alguns insights em relação ao Microsoft Access, à integração com o modelo de objetos do Microsoft Excel e, ao mesmo tempo, apresentará algumas ideias em relação a operações DML (linguagem de manipulação de dados) com tabelas do Access utilizando SQL.

Outras vantagens de utilizar o modelo de objetos do Excel são:

  1. Acesso total a cada linha / coluna, que permite validações complexas, a busca de processos e limpeza de dados
  2. Conversão do tipo de dados
  • Acesso a todas as funções de modelo de objetos do Excel se necessário

Para criar o resultado final, o leitor irá precisar de

  1. Um arquivo teste do Excel
  2. Uma base de dados no Microsoft Access com uma tabela e um único formulário com um botão

 

O primeiro passo será obter algumas amostras de dados no Excel para testar o processo. Uma vez que gerar várias linhas de dados de teste é um processo tedioso, uma forma mais fácil seria baixar alguns dados de teste. Por exemplo, eu encontrei este arquivo

http://www.sample-videos.com/xls/Sample-Spreadsheet-10000-rows.xls

Nesta mesma página, o leitor encontrará outros arquivos, menores e maiores. O layout do arquivo selecionado é o seguinte:

Existem dez colunas sem cabeçalhos.

Neste exemplo, importaremos as colunas A, B e G na tabela de dados de destino.

Vamos começar …

Após criar uma base de dados vazia no Microsoft Access, o usuário deve criar uma tabela vazia com o layout apresentado abaixo

O campo ItemId da tabela será mapeado ara a coluna A, o campo Descrição será mapeado para a coluna B e o campo Preço para a coluna G. O leitor deve prestar atenção ao campo de preço subjacente ao tipo de dados. Como os dados de origem podem ter números decimais, o campo de destino deve poder acomodá-los (lembre-se que quando se trabalha com SQL o separador decimal é um ponto). O primeiro campo é simplesmente uma sequência inteira e o segundo é uma série de textos de descrição (mais sobre isso mais tarde).

Uma vez que a tabela for criada, o próximo passo será a criação de um formulário simples com um botão para ativar o processo. Assumindo que o leitor já tem experiência em Microsoft Access, o formulário final deve ficar parecido com este

 

Mude a propriedade do botão para Importar Excel e o nome do botão para cmdImportExcel (como mencionamos no tutorial anterior, manter um padrão nos nomes é sempre uma boa prática). Clique no botão de salvar e nomeie o formulário como frmImportExcel.

Para fornecer alguns recursos intuitivos, o botão criado irá chamar uma caixa de diálogo com um seletor de arquivos, assim o usuário poderá selecionar o arquivo de origem do disco duro, escolhendo-o de qualquer localização.

Clique com o botão direito no botão de inserir, escolhe “construir evento” e então o construtor de código. O editor do Visual Basic deve abrir.

Uma observação: não vamos forçar declarações variáveis explícitas no código, para que cada variável seja declarada utilizando seu tipo de dado correto, e nenhum recurso da máquina seja alocado em vão. Em cima do código (embaixo da opção Comparar Base de Dados) simplesmente adicione

Option Explicit

Agora, todas as variáveis exigirão uma declaração.

Antes de implementar o código, vamos explicar a lógica da sequência:

  1. O usuário verá uma janela para selecionar o arquivo
  2. O usuário poderá escolher o arquivo Excel
  • Se um arquivo Excel for escolhido, seu endereço será designado a uma variável
  1. O endereço armazenado naquela variável será utilizado para abrir o Excel no plano de fundo e carregar todas as linhas na tabela do Access

Implementando a caixa de diálogo

Para utilizar a caixa de diálogo, devemos fornecer uma referência à Biblioteca de Objetos do Microsoft Office XX (dependerá da versão do Office que está instalada). Para fazer isto, acesse Ferramentas -> Referências no editor VBA.

Vá para baixo utilizando a barra de rolagem e escolha a biblioteca de objetos do Office instalado.

Agora, todos os objetos e métodos fornecidos por ele estarão disponíveis.

Vamos adicionar o código do selecionador de arquivos…

No editor VBA, dentro do evento cmdImportExcel_Click() coloque o seguinte código. O código é altamente recomendado, para que o leitor entenda o que cada linha está fazendo.

Private Sub cmdImportExcel_Click()

On Error GoTo cmdImportExcel_Click_err:

Dim fdObj As Office.FileDialog ‘declara a variável fdObj

Dim varfile As Variant ‘variável de tipo variante que armazenará o endereço do arquivo selecionado

Set fdObj = Application.FileDialog(msoFileDialogFilePicker) ‘instancia a variable criando um objeto filepicker utilizando o método late binding

 

With fdObj ‘utilizando a declaração “with”, estaremos trabalhando com o fdObj como padrão

‘não permite selecionar mais que um arquivo

.allowmultiselect = False

‘Limpa os filtros existentes no arquivo

.Filters.Clear

‘este arquivo irá apenas permitir a seleção de arquivos xlsx

.Filters.Add “Excel 2007+”, “*.xlsx”

 

‘opcional, configurar o título do arquivo

.Title = “Por favor selecione o arquivo do Excel a ser importado…”

.Show

 

If .SelectedItems.Count = 1 Then ‘um arquivo foi selecionado

Call MsgBox(“O arquivo selecionado foi: ” & .SelectedItems(1)) ‘por enquanto testaremos a escolha de arquivos ao enviar o endereço do arquivo para uma caixa de mensagem que aparecerá na tela

Else ‘nenhum arquivo foi selecionado

Call MsgBox(“Nenhum arquivo foi selecionado.”)

End If

End With

 

Exit Sub

 

cmdImportExcel_Click_err:

Select Case Err.Number

Case Else

Call MsgBox(Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”)

End Select

End Sub

Agora vamos testar o código e ver como funciona. Primeiro, vamos visualizar o formulário ao clicar na Visualização de Formulário, como na imagem abaixo

Então o formulário será apresentado

O leitor agora deve pressionar no botão Importar Excel e uma caixa de seleção de arquivos será apresentada

A seguir, por favor, selecione o arquivo que você baixou, clique no botão abrir, e o resultado deve ser parecido ao da imagem abaixo, mostrando o endereço do arquivo.

Se este for o resultado, tudo está funcionando como esperado e o componente de escolha de arquivos já está funcionando. A seguir, veremos como incluir o modelo de objetos do Excel na solução do Microsoft Access. O leitor terá que incluir a referência correta ao objeto de modelos do Excel. Novamente, acesse Ferramentas -> Refereências no editor VBA.

E marque a Caixa referência à aplicação do Excel

De agora em diante, todos os métodos de objetos e propriedades do Excel estarão disponíveis uma vez que uma variável do Excel for criada dentro do código VBA no Microsoft Access. Esta técnica é chamada early binding, em que se sabe os tipos de dados com antecedência, mas não é obrigatório verificar estas referências. Se estas referências não forem configuradas, a ténica de late binding pode ser utilizada, mas o desenvolvimento será mais difícil, já que o editor de códigos não mostrará as propriedades e métodos, e você precisará fazer uma leitura dos documentos. Uma abordagem comum é utilizar a referência enquanto desenvolve e removê-la ao terminar, e então as constantes desconhecidas podem ser substituídas pelos seus valores inteiros correspondentes. Não entraremos em detalhes aqui, mas é importante estar ciente de que estas referências não são obrigatórias, mas facilitam sua vida.

Uma vez que a referência para o Excel for criada, podemos declarar os tipos de variáveis definidos aqui. Precisaremos declarer três variáveis

  1. xlApp – será uma referência a uma aplicação Excel escondida
  2. xlWb – será uma referência ao workbook aberto como resultado da abertura do arquivo para ser importado
  • xlWs – será uma referência à planilha com os dados a serem importados

Os passos envolvidos no processo de carregamento podem ser descritos como a seguir

  1. Delete os dados existentes na tabela de destino
  2. Faça um loop por todas as linhas do Excel, executando uma declaração de inserir por linha até chegar na última linha
  • O processo de loop irá parar quando ele não encontrar mais dados na coluna A

O código requerido para o processo é o seguinte. Adicionaremos comentários em todas as linhas, por favor, verifique-os abaixo em verde

Private Sub cmdImportExcel_Click()

On Error GoTo cmdImportExcel_Click_err:

 

Dim fdObj As Office.FileDialog ‘declara a variável fdObj

Dim varfile As Variant ‘variável de tipo variante que irá armazenas o caminho selecionado do arquivo

 

Set fdObj = Application.FileDialog(msoFileDialogFilePicker) ‘instancia a variável criando um objeto filepicker utilizando o método early binding

 

With fdObj ‘utilizando a declaração “with” estaremos trabalhando com o fdObj como padrão

‘não permite selecionar mais de um arquivo

.allowmultiselect = False

‘limpa os filtros existentes no arquivo

.Filters.Clear

‘esta caixa de diálogo só permitirá a seleção de arquivos Excel, e isto pode ser feito ao lidar com a coleção de Filtros

.Filters.Add “Excel 2003”, “*.xls”

.Filters.Add “Excel 2007+”, “*.xlsx”

 

‘opcional para configurar o título da caixa de diálogo

.Title = “Por favor selecione o arquivo Excel para importar …”

.Show

 

If .SelectedItems.Count = 1 Then ‘um arquivo foi selecionado para que os dados possam ser importado do Excel, a partir deste ponto o processo de loop para importação será executado

‘declaração das variáveis

Dim xlApp As Excel.Application ‘a aplicação Excel

Dim xlWb As Excel.Workbook ‘a referência do workbook do Excel que levará ao workbook aberto

Dim xlWs As Excel.Worksheet ‘a planilha do excel com os dados

Dim intLine As Long ‘o contador de linhas

Dim strSqlDml As String ‘série de variáveis que armazena as declarações SQL executadas

Dim strColumnBcleaned As String ‘série de variáveis que armazena valores da coluna B após substituir aspas únicas por quatro aspas únidas

‘lembre-se que as aspas são as delimitadoras de série no SQL, então ela precisa ser inserida

Dim strColumnGcleaned As String ‘série de variáveis que armazena valores da coluna G que foi limpa, o passo limpo substitui vírgulas por pontos

‘o separador decimal no SQL é o ponto

varfile = .SelectedItems(1) ‘selecione o caminho do arquivo selecionado

limpe a tabela existente

CurrentDb.Execute “DELETE * FROM tblExcelImport”, dbFailOnError

 

‘instancie a aplicação Excel, criando a aplicação Excel na memória, então ela estará visível para que o usuário possa ver o loop operando pelas linhas do Excel. Mas geralmente, este processo é escondido, sendo visível apenas se for necessário

Set xlApp = New Excel.Application

xlApp.Visible = True

‘ao abrir o arquivo escolhido ao utilizar o método de abertura pela coleção de workbooks do Excel, ele recebe a localização do arquivo como um parâmetro e devolve uma referência para o arquivo aberto

Set xlWb = xlApp.Workbooks.Open(varfile)

‘configurar a planilha como a primeira entre as disponíveis, já que ela é a que tem os dados a serem importados

Set xlWs = xlWb.Worksheets(1)

‘valor/linha inicial padrão do Contador, o que significa que começamos a operação na linha um intLine = 1

Do

‘nas próximas duas linhas, substitua as aspas únicas no valor da coluna b e as vírgulas por pontos como separadores decimais na coluna G

strColumnBcleaned = Replace(xlWs.Cells(intLine, 2).Value2, “‘”, “”””)

strColumnGcleaned = Replace(xlWs.Cells(intLine, 7).Value2, “‘”, “”””)

‘a próxima linha cria uma declaração SQL inserir utilizando as variáveis obtidas anteriormente e o valor para a coluna A

A declaração inserir deve ter a sequência presente na tabela de destino e é obtida ao concatenar valores para cada linha apresentada no arquivo Excel enquanto itera

strSqlDml = “INSERT INTO tblExcelImport VALUES(” & xlWs.Cells(intLine, 1).Value2 & “, ‘” & strColumnBcleaned & “‘, ” & strColumnGcleaned & “)”

‘executa a declaração inserir na base de dados, o dbFailOnError é um valor opcional que fará o processo de Execução mostrar um erro se o SQL não for executado corretamente

CurrentDb.Execute strSqlDml, dbFailOnError

‘a próxima linha apenas coloca a célula selecionada no Excel na posição da linha, e isto não é obrigatório e até mesmo deixará o processo mais lento, só foi colocado aqui para que o leitor possa ver o processo sendo executado

xlWs.Cells(intLine, 1).Select

intLine = intLine + 1

Loop Until IsEmpty(xlWs.Cells(intLine, 1)) ‘critério de parade, quando os valores na coluna A pararem, o loop irá parar. Por favor, note na coleção das células que o primeiro índice é a linha e o Segundo é a coluna, então estamos mudando de linhas. Uma vez que o loop parar, feche o workbook aberto, sais do Excel e limpe as referências para os objetos criados

xlWb.Close False

xlApp.Quit

Set xlApp = Nothing

Set xlWb = Nothing

Set xlWs = Nothing

‘o próximo passo abre a tabela carregada para que o usuário possa ver os dados importados

DoCmd.OpenTable “tblExcelImport”, acViewNormal, acEdit

‘este parte só acontece se nenhum arquivo foi selecionado

Else ‘nenhum arquivo foi selecionado

Call MsgBox(“Nenhum arquivo foi selecionado.”)

End If

End With

 

Exit Sub

 

cmdImportExcel_Click_err:

Select Case Err.Number

Case Else

Call MsgBox(Err.Number & ” – ” & Err.Description, vbCritical + vbOKOnly, “System Error …”)

End Select

End Sub

Como mencionado anteriormente, esta não é a única abordagem para importar dados do Excel. É possível criar tabelas conectadas e importar os dados no Microsoft Access. O leitor também deve considerar o método DoCmd.TransferDatabase. As tabelas conectadas podem ser feitas com drivers de conexão, para que possam não só mostrar bases de dados, mas também é possível criar uma tabela ligada a um arquivo de texto.

Alguns exemplos relevantes relacionados à cláusula IN podem ser encontrados aqui

https://msdn.microsoft.com/en-us/library/bb177907(v=office.12).aspx

Para executar uma consulta no Access, obtendo dados de um arquivo Excel, uma solução parecida pode ser utilizada

SELECT CustomerID, CompanyName

FROM [Customers$]

IN “c:\documents\xldata.xls” “EXCEL 5.0;”

WHERE CustomerID Like “A*”

ORDER BY CustomerID;

 

Além disso, quando lidamos com declarações SQL construídas dinamicamente, é muito importante lidar com possíveis valores nulos, substituindo-os por valores padrão ou até mesmo forçando a inserção do valor nulo.

Se um valor pode ser nulo, então a variável precisa ser to tipo Variante, para armazená-lo e então utilizá-lo na declaração de inserir construída.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *