[Resolvido]Atualização automática na importação Access Excel

Alguém pode me ajudar com a minha dúvida?

eu recebo um arquivo em excel diariamente, e eu preciso que esse arquivo no excel verifique no meu banco do access se existem informações novas ou não:

caso exista informação nova, incluir na minha base
caso a informação já exista na base, verificar se a informação da base está igual ao do excel, se estiver diferente, manter a do excel como principal e mudar a da base
caso a informação já exista na base e ela for igual ao do excel, não fazer nada.

alguém pode me ajudar?
Obrigado.

Olá,

Uma solução poderá ser em ligar (vincular) a tua aplicação em Access ao ficheiro de Excel.
Depois podes trabalhar na base de consultas (verificar diferentes, inserir ou atualizar).

Abraço

o problema é que eu tenho 12 bases para atualizar utilizando essa planilha em excel.

Existe alguma forma de ele fazer essas ligações automáticas ao invés de eu ter que fazer essas ligações todos os dias?

Olá,

Não compreendi em que consiste "12 bases" será 12 ficheiros Excel ?
Ou será 12 tabelas?

Quanto à ligação, poderá ser feita automáticamente após selecionar o ficheiro ou outra alternativa será a colocação de ficheiros num determinado caminho (pasta) e aplicação correr a verificar o conteodo.

são 12 tabelas

uma com cadastro de lojas
uma com CEPs / Cidades / UF do BRASIL
uma com Cód Interno / Grupo

etc etc

na planilha "Diária" que eu recebo em excel, tem todas essas informações. Ai eu vou precisar conferir por ex, se o endereço de uma loja mudou (se tiver mudado, alterar, ou incluir um cadastro novo caso não tenha), se apareceu algum CEP novo (se tiver incluir).. essas coisas..

Olá de novo,

Poderá ser trabalhoso, mas se é assim que recebes a informação podes automatizar o processo.

Vou ver se encontro algum exemplo (já está na mensagem nº 7)

No entanto para facilitar, poderias postar uma base de dados com as tabelas utilizadas na tua APP com alguns dados de exemplo, assim como um ficheiro de Excel, conforme recebes, também com alguns dados de exemplo.

Olá,

Aqui vai um exemplo, utiliza tabela local temporaria, faz atualização e inserção dos novos. (verifica o nº de registos antes de efetuar operação de importação).

Abraço

não entendi como funciona esse exemplo

aqui está minha BD e o arquivo em excel que recebo diariamente.

https://drive.google.com/file/d/0B_j90dPUbrVsc1pHX2MtSGFEVTQ/edit?usp=sharing

Olá, não sou muito bom a explicar, mas vou tentar.

A tabela A_Funcionarios tem 3 registos e é a tabela que pretendemos atualizar com os dados do ficheiro de excel.



A tabela ExcelTmp está vazia e é temporária.



O ficheiro em excel Funcionarios.xlsx tem 6 registos (2 novos e 1 alterado)



O formulário Fprincipal é que tem o código em VBA que faz a abertura de uma janela para selecionar o ficheiro e processar as consulas.



Segue abaixo o código utilizado

Código:

Private Sub Comando18_Click()
    
       Call AbrirRC

End Sub


Function AbrirRC() As String
    
    ' Requer referencia a Microsoft Office 11 Object Library
    On Error GoTo PROC_ERR
    
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    fd.Title = "selecione o ficheiro"
    fd.Filters.Add "Ficheiro XLSX", "*.xlsx", 1

    fd.Show
    
    If (fd.SelectedItems.Count > 0) Then
        '------inicio importação excel para sincronização
        Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean
        blnHasFieldNames = True
        strPathFile = fd.SelectedItems(1)
        strTable = "ExcelTmp"
      
        'apaga temporarios
        DoCmd.RunSQL "Delete * from ExcelTmp"
        
        'importa para tabela local temporária
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames
        'sql verifica existentes e marca com não novo
        DoCmd.OpenQuery "xls01marcaExistentes", acViewNormal, acEdit
        'sql atualiza existentes
        DoCmd.OpenQuery "xls02AtualizaExistentes", acViewNormal, acEdit
        'sql lança novos no ficheiro funcionarios
        DoCmd.OpenQuery "xls03LancaNovos", acViewNormal, acEdit
        
        MsgBox "Operação concluída.", vbInformation, ""
        
        'apaga temporarios
        DoCmd.RunSQL "Delete * from ExcelTmp"
        
    Else
        MsgBox "Não foi escolhido nenhum ficheiro", vbInformation, ""
    End If
    
PROC_EXIT:
    Exit Function
    
PROC_ERR:
    DoCmd.Hourglass False
    If Err.Number = 3011 Then
       LocalXML = ""
       MsgBox ("Ficheiro inválido.")
    Else
        MsgBox Err.Description
    End If
    Resume PROC_EXIT
    
End Function
        


Ficando a tabela A_funcionarios atualizada, conforme abaixo



Fico aguardar retorno

Acho que isso vai me ajudar, como faço para ver o código usado para eu poder implantar no meu?
o meu Office é o 2010.

Só não entendi o AlunoID, qual o critério que ele utiliza para criar o ID?

O AlunoID está aí, mas nem faz falta, considera que é o ID de registo automático.
Foi do exemplo que fui buscar e adaptei, (feito a correr, quando estava no metro).

Para abrir o ficheiro deves manter tecla SHIFT primida e clicar duas vezes com o rato em AccessExcel_exemplo.mdb

Para ver código ALT+F11

Quanto ao teu ficheiro ainda não examinei.

Abraço

tentei colocar o código que você fez aqui.

1 - quando eu abro o seu access, ele vem em um formato que não consigo fazer nada:
http://tinypic.com/r/wiae1k/8

2 - eu importei as suas tabelas/consultas/macros para um access novo, e aparece o seguinte erro:
http://tinypic.com/r/fok4ux/8

o que estou fazendo de errado?

Para abrir o ficheiro deves manter tecla SHIFT primida e clicar duas vezes com o rato em AccessExcel_exemplo.mdb

ou

Faz download novamente da mensagem nº 7 (já activei menus)

Relativamente ao ponto 2, verifica as referencias

Sim, eu fiz isso tanto para abrir o que você enviou, quanto para abrir o que eu fiz a partir de um access novo importanto os dados da sua tabela, e continua igual as telas conforme a imagem que eu mandei.

Fizeste novo download??

Na tua importação verifica as referencias

Olá,

No entanto, segue abaixo link da tua base de dados no formato 2010.

AccessExcel_Dados.zip

Apenas está a importar o ficheiro escolhido (Diario Teste.xls) para a tabela ExcelTmp.

Aguardamos retorno.

o access tem 12 tabelas. Qual dessas tabelas ele vai atualizar?

eu precisava atualizar coisas diferentes em tabelas diferentes (pegando as informações do mesmo arquivo que no caso é o excel). Tem como fazer isso tudo de uma vez?

e eu comparei rodando o arquivo e não rodando, aparentemente não atualizou.

mas o arquivo "ExcelTmp" está com informações, não foi excluído.

Olá,

Como estavas com dificuldades, apenas coloquei o codigo na tua base de dados a importar para a tabela ExcelTmp.

A atualização dos campos terá que ser criadas as consultas, conforme o outro exemplo.

entendi, vou tentar mais tarde e falo se deu certo

Bem Belker vamos ver se te consigo ajudar.

A) Ainda estás com problemas de não abrir a base de dados ou dar erro?
B) O código já funciona na versão disponibilizada acima?

Se já passamos esta fase então vamos à "sincronização"

O facto de ser 12 tabelas, se tem que ser , tem que ser.
Agora não vamos stressar  
Temos que começar por uma.


C) Por que tabela começamos? e qual o campo identificador unico (para relacionar)

Bem Belker vamos ver se te consigo ajudar.

A) Ainda estás com problemas de não abrir a base de dados ou dar erro?
B) O código já funciona na versão disponibilizada acima?

Se já passamos esta fase então vamos à "sincronização"

O facto de ser 12 tabelas, se tem que ser , tem que ser.
Agora não vamos stressar Smile
Temos que começar por uma.


C) Por que tabela começamos? e qual o campo identificador unico (para relacionar)

A) Agora está abrindo normal.
B) O código parece estar funcionando sim, quando eu seleciono o arquivo em excel para ele importar, os dados vão todos para a tabela temporária no access.
C) podemos começar pela tabela PDV, o código único dela é o CNPJ (no access), e na planilha em excel chama (PDV_CNPJ)

ahteixeira, muito obrigado pela ajuda que está me dando!!   

Olá de novo.

Muito bem, agora tenho a seguinte pergunta.

Pretendes atualizar a tabela CNPJ (no access), com os dados da planilha em excel (PDV_CNPJ) nos campos que são comuns?

Sendo o resultado final o mesmo nº de registos, certo?

Ola

Na tabela de Excel existe mais que um registo do mesmo codigo.
Como pretendes processar, ter todos os registos? agrupar? o ultimo?

Exemplo

PDV_CNPJ           (duplicado)
02543945000690   (80)
61412110010380   (49)
61412110005033   (49)

para atualizar a planilha "PDV", precisa agrupar os duplicados do excel e atualizar os demais campos que tem em comum.

Olá,

Vamos então ver, tenho um tempinho.

Uma coisa que reparei é que na tabela PDV os campos são todos de texto com tamanho 255, será necessário?
No menu entender os campos deveriam estar dimensionados com o tipo de dados que se trabalha.

não é necessário limitar o tamanho não. Mas tem que deixar tudo como texto.

Como faço para mandar uma imagem aqui? pq sempre que tento anexar, fala que o tamanho é mto grande para enviar.
E como enviar mais de uma imagem?

Para continuar necessito de correspondencias de todos os campos do PDV e os correspondentes do exel

Exemplo:

CNPJ ---> PDV_CNPJ
COD_IMS ---->....

Para enviar imagem

Clicar no botão da barra que diz "hospedar uma imagem", deves fazer upload e depois só colocas aqui o link

Relativamente ao tamanho dos campos e Tipo de dados, deve ser efetuado um levantamento do tipo de dados que a tua aplicação necessita.

o facto de se estar a importar de excel não obriga a campos texto.

Vamos ver o seguinte exemplo.

Se o código PDV  for sempre numerico e não passar de 10 digitos, deves configurar assim.

Vou sair

xau

necessariamente precisa configurar que o código de PDV tem 10 digitos e é numérico? não pode deixar sem configuração?
pois creio que seja um trabalho grande fazer tudo isso.



Excel Access (PDV)
BRICK_CODIGO BRICK_CODE
PDV_CNPJ CNPJ
PDV_NOME PDV
PDV_ENDERECO ENDEREÇO
PDV_TELEFONE TELEFONE
PDV_CEP CEP
PDV_BAIRRO BAIRRO
PDV_CIDADE CIDADE
PDV_UF UF

Excel.....................Access (PDV)

BRICK_CODIGO........BRICK_CODE
PDV_CNPJ..............CNPJ
PDV_NOME.............PDV
PDV_ENDERECO.......ENDEREÇO
PDV_TELEFONE.......TELEFONE
PDV_CEP................CEP
PDV_BAIRRO...........BAIRRO
PDV_CIDADE...........CIDADE
PDV_UF..................UF

Ola,

Se pretendes os campos assim , tudo bem.

Procedi à alteração e está a funcionar a Importação de Excel e actualização da tabela PVD.

No código podes verificar, está a chamar as consultas de 01... até 06...

Para as restantes tabelas é repetir o processo da 04... até 06...

Ou caso pretendas outro agrupamento

repetir processo da 02... até 06

Nota:

Para a proxima atualizaçao de tabela deves copiar ou criar consulta nova 07...
Todo o processo de "sincronização" está a ser feito à base de consultas.

AccessExcel_Dados_novo.zip

Abraço

ahteixeira, funcionou perfeitamente!!!! muito obrigado!!!

agora eu preciso fazer a atualização para as outras bases, ai não entendi a partir de onde eu vou copiar.

eu vou usar o mesmo arquivo Diario para atualizar outras bases, por ex a base: "INFORMANTES".

a partir de qual código eu preciso copiar e mudar as informações para atualizar a "INFORMANTES"?

Olá

A importação já está feita , agora criar novas consultas, repara que elas estão numeradas, estuda o processo.

Abraço

uma dúvida..
para fazer uma nova atualização, preciso criar um novo arquivo temporário com as informações que vou atualizar?

Olá de novo.
Não sei como vai ser a nova atualização.
Mas importação não é necessário, pois os dados importados já estão na tabela ExcelTmp.

Se tens que fazer novo agrupamento, podes utilizar a tabela ExcelTmpAgrupado (Atenção que não tem os campos todos, mas podes adicionar) ou criar uma nova para esse efeito.

Exemplo , imagina que vais atualizar a proxima tabela e necessitas do agrupamento por Brick_code

Crias as seguintes consultas

07Apaga_Dados_ExcelTmpAgrupado  (é igual à 02...)
08Agrupar_para_ExcelTmpAgrupado  (podes copiar a 03..  e alterar o agrupamento por Brick_code)
09xxx_marca_registos_novos  (podes copiar a 04..  e atualizar conforme pretendido)
10xxx_Atualiza_Existentes (podes copiar a 05..  e atualizar conforme pretendido)
11xxx_Lanca_novos (podes copiar a 06..  e atualizar conforme pretendido)

Depois no código ondes está 'Aqui deves iniciar a proxima tabela

Código:

...        'apaga temporarios
        DoCmd.OpenQuery "01Apaga_Dados_ExcelTmp", acViewNormal, acEdit
        DoCmd.OpenQuery "02Apaga_Dados_ExcelTmpAgrupado", acViewNormal, acEdit
        DoEvents
        
        'importa para tabela local temporária
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames
        DoEvents
        
        'agrupa e lança na tabela temporaria ExcelTmpAgrupado
        DoCmd.OpenQuery "03Agrupar_para_ExcelTmpAgrupado", acViewNormal, acEdit
        DoEvents
        
        '==== INICIO ACTUALIZAR TABELA PDV ====
        'verifica existentes da tabela PDV e marca com não novo na tabela temporaria ExcelTmpAgrupado
        DoCmd.OpenQuery "04PDV01_marca_registos_novos", acViewNormal, acEdit
        DoEvents
        
        'atualiza existentes
        DoCmd.OpenQuery "05PDV01_Atualiza_Existentes", acViewNormal, acEdit
        DoEvents
        
        'lança novos no ficheiro PDV
        DoCmd.OpenQuery "06PDV01_Lanca_novos", acViewNormal, acEdit
        DoEvents
        '==== FIM ACTUALIZAR TABELA PDV ====
        
        'Aqui deves iniciar a proxima tabela

Deves incluir os comandos para executar as consultas.

exemplo:

Código:

...
        'lança novos no ficheiro PDV
        DoCmd.OpenQuery "06PDV01_Lanca_novos", acViewNormal, acEdit
        DoEvents
        '==== FIM ACTUALIZAR TABELA PDV ====
        
        'Aqui deves iniciar a proxima tabela
        
        '==== INICIO ACTUALIZAR TABELA xxx ====
        'apaga dados ....
        DoCmd.OpenQuery "07Apaga_Dados_ExcelTmpAgrupado", acViewNormal, acEdit
        DoEvents
        
        'abrupa
        DoCmd.OpenQuery "08Agrupar_para_ExcelTmpAgrupado", acViewNormal, acEdit
        DoEvents

        .......... e continuar igual acima
        

Repara que o exemplo que disponibilizei a alteração das consultas podes fazer no access pelo assistente ou alterar, sem necessidade instruções de SQL diretamente no código.

Fiz vários códigos para atualizar todas as bases que preciso, ficou assim:

Código:

Option Compare Database

Private Sub Comando18_Click()
    
       Call AbrirRC

End Sub
Private Sub Comando20_Click()
    DoCmd.Close
        
End Sub

Function AbrirRC() As String
    
    ' Requer referencia a Microsoft Office 11 Object Library
    On Error GoTo PROC_ERR
    
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    fd.Title = "selecione o ficheiro"
    fd.Filters.Add "Ficheiro XLS", "*.xls", 1

    fd.Show
    
    If (fd.SelectedItems.Count > 0) Then
        '------inicio importação excel para sincronização
        Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean
        blnHasFieldNames = True
        strPathFile = fd.SelectedItems(1)
        strTable = "ExcelTmp"
      
        'apaga temporarios
        DoCmd.OpenQuery "01Apaga_Dados_ExcelTmp", acViewNormal, acEdit
        DoCmd.OpenQuery "02_1Apaga_Dados_ExcelTmpAgrupadoBRICK", acViewNormal, acEdit
        DoCmd.OpenQuery "02_2Apaga_Dados_ExcelTmpAgrupadoCEPBRICK", acViewNormal, acEdit
        DoCmd.OpenQuery "02_3Apaga_Dados_ExcelTmpAgrupadoINFORMANTES", acViewNormal, acEdit
        DoCmd.OpenQuery "02_4Apaga_Dados_ExcelTmpAgrupadoPDV", acViewNormal, acEdit
        DoCmd.OpenQuery "02_5Apaga_Dados_ExcelTmpAgrupadoPROUTOS", acViewNormal, acEdit
        DoEvents
        
        'importa para tabela local temporária
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames
        DoEvents
        
        'agrupa e lança nas tabelas temporarias
        DoCmd.OpenQuery "03_1Agrupar_para_ExcelTmpAgrupadoBRICK", acViewNormal, acEdit
        DoCmd.OpenQuery "03_2Agrupar_para_ExcelTmpAgrupadoCEPBRICK", acViewNormal, acEdit
        DoCmd.OpenQuery "03_3Agrupar_para_ExcelTmpAgrupadoINFORMANTES", acViewNormal, acEdit
        DoCmd.OpenQuery "03_4Agrupar_para_ExcelTmpAgrupadoPDV", acViewNormal, acEdit
        DoCmd.OpenQuery "03_5Agrupar_para_ExcelTmpAgrupadoPRODUTOS", acViewNormal, acEdit
        DoEvents
        
        '==== INICIO ACTUALIZAR TABELA ====
        'verifica existentes da tabela e marca com não novo na tabela temporaria
        DoCmd.OpenQuery "04_1Marca_Registos_NovosBRICK", acViewNormal, acEdit
        DoCmd.OpenQuery "04_2Marca_Registos_NovosCEPBRICK", acViewNormal, acEdit
        DoCmd.OpenQuery "04_3Marca_Registos_NovosINFORMANTES", acViewNormal, acEdit
        DoCmd.OpenQuery "04_4Marca_Registos_NovosPDV", acViewNormal, acEdit
        DoCmd.OpenQuery "04_5Marca_Registos_NovosPRODUTOS", acViewNormal, acEdit
        DoEvents
        
        'atualiza existentes
        DoCmd.OpenQuery "05_1Atualiza_ExistentesBRICK", acViewNormal, acEdit
        DoCmd.OpenQuery "05_2Atualiza_ExistentesCEPBRICK", acViewNormal, acEdit
        DoCmd.OpenQuery "05_3Atualiza_ExistentesINFORMANTES", acViewNormal, acEdit
        DoCmd.OpenQuery "05_4Atualiza_ExistentesPDV", acViewNormal, acEdit
        DoCmd.OpenQuery "05_5Atualiza_ExistentesPRODUTOS", acViewNormal, acEdit
        DoEvents
        
        'lança novos no ficheiro PDV
        DoCmd.OpenQuery "06_1Lanca_NovosBRICK", acViewNormal, acEdit
        DoCmd.OpenQuery "06_2Lanca_NovosCEPBRICK", acViewNormal, acEdit
        DoCmd.OpenQuery "06_3Lanca_NovosINFORMANTES", acViewNormal, acEdit
        DoCmd.OpenQuery "06_4Lanca_NovosPDV", acViewNormal, acEdit
        DoCmd.OpenQuery "06_5Lanca_NovosPRODUTOS", acViewNormal, acEdit
        DoEvents
        '==== FIM ACTUALIZAR TABELA PDV ====
        
        'Aqui deves iniciar a proxima tabela
        
        
        MsgBox "Operação concluída.", vbInformation, ""
        
        
    Else
        MsgBox "Não foi escolhido nenhum ficheiro", vbInformation, ""
    End If
    
PROC_EXIT:
    Exit Function
    
PROC_ERR:
    DoCmd.Hourglass False
    If Err.Number = 3011 Then
       LocalXML = ""
       MsgBox ("Ficheiro inválido.")
    Else
        MsgBox Err.Description
    End If
    Resume PROC_EXIT
    
End Function
        



Está rodando sem erros, agora preciso fazer um teste pra ver se está atualizando/incluindo os itens certinho!!

Olá,

Muito Bem, como verificas são 12 tabelas mas se olhar-mos para um problema de cada vez conseguimos resolver.

Caso esteja resolvido, não esquecer de dar o tópico por encerrado (que já está a ficar grande).

Abraço

ahteixeira funcionou perfeitamente.
Muito obrigado pela ajuda.

Vou abrir um outro tópico para uma outra questão, mas esse já está resolvido.

Mais uma vez, obrigado!!

Olá Dilson, obrigado pelo retorno, isto é a prova da interajuda e razão da existência deste forúm.
Abraço

Boa Noite

Eu peguei um exemplo aqui e achei muito interessante.

Estou querendo saber se por um acaso teria como importar duas bases diferentes.
Sendo que uma delas a quantidade iria somar e não sobrepor.

Tenham uma boa noite.

Ola Deison,
Será melhor abrir um tópico novo.
Pode fazer referência a este e postar o seu exemplo.
Este está dado como resolvido.
Ficamos aguardar.
Abraço

Olá Amigos,
Neste tópico demonstra como fazer a importação de um Excel e "sincronização" à medida que se vai importando mais ficheiros de Excel.
Quem desejar efetuar tudo via RecordSet, recomendo o tópico abaixo (mensagem 12) contribuíção do nosso grande guru JPaulo:
https://www.maximoaccess.com/t30006-resolvidoatualizar-tabela-do-bd-ao-importar-do-excel~

Abraço e bons estudos com o MaximoAccess

Boa noite senhores e muito obrigado ahteixeira pelo ótimo exemplo.

Depois de algum tempo estudando e alterando os códigos e consultas para se encaixar ao meu projeto, tive exito na importação dos dados.
Porem, quando inicio a importação aparecem mensagens para confirmar se desejo realmente continuar com processo, como ilustrado na foto abaixo (aparecem nesta exata ordem da imagem).



Como são diversas vezes que tenho que confirmar, se torna um pouco incomodo. Há alguma do processo ocorrer sem necessitar dessas confirmações.
Aceito ideias e sugestões de todos.
Agradeço desde já.

Resolvi momentaneamente com o código:

Código:

Private Sub Btn_Importar_Click()

DoCmd.SetWarnings False
       Call ImportarPlanilhaOrca
DoCmd.SetWarnings True

End Sub

Olá a todos,

Allan Silva, é isso mesmo.
Obrigado pela partilha, outro membro pode ter a mesma dúvida.

Duvida referente ao caso resolvido, para aplicar de outra forma.
Para o caso em questão, fui atendido parcialmente. Tenho um processo de arquivamento que requer atualização com novos registros. Em vez de solicitar a abertura do arquivo, já tenho o caminho e a pasta do Excel onde as informações para atualização estão armazenadas. Além disso, esse arquivo do Excel contém outros arquivos e o arquivo que necessito se chama novo_atualizar.

Olá Romildo Luz,
Seja bem-vindo ao fórum.

Então talvez seja melhor criar um tópico novo e colocar o que tem feito e relatar a sua dúvida.

Abraço