Importar colunas de uma planilha do excel para várias tabelas relacionadas

Olá pessoas,

Estamos aqui a tentar resolver este quebra-cabeça...
Fizemos alguns testes porém sem sucesso... colocamos um botão no formulário e o código abaixo, porém nada acontece

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = True
strPath = "C:\" ' drive onde se situa o seu documento excel
strTable = "SuaTabela" 'nome da tabela no seu banco
strFile = Dir(strPath & "SeuExcel.xls") 'nome do seu excel, se mudar para "*.xls" importa todas as folhas excel _ que estiverem em C:\ para a tabela do banco.
Do While Len(strFile) > 0 strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
strFile = Dir()
Loop
End
escreveu:

Qual seria a melhor maneira de organizar estas colunas da planilha do Excel para importar ao bd?
Qual seria a melhor maneira de adaptar este código a este caso?
Como fazer com que ao clicar de um botão em um formulário, esta rotina seja automatizada para que não seja feita mais de forma manual?

...desculpem-nos a falta de experiência...

Como já dizíamos em outro poste, temos algumas tabelas relacionadas - um para muitos, um para um, chaves primárias, chaves estrangeiras como suas integridades referenciais e variados tipos de campos, etc...

Recebemos periodicamente planilhas do Excel com dados que terão que ser atualizados ou acrescentados às tabelas, e mais ainda, alguns desses dados do Excel talvez já exista em alguns campos das tabelas, outros dados são novos, os quais serão acrescentados ou atualizados no bd.

Acontece que algumas colunas desta planilha se referem a uma primeira tabela, outras colunas se referem a uma subtabela relacionada à primeira, com suas respectivas integridades, e assim por diante...
Fato é que todas as tabelas do bd estão relacionadas com suas chaves e suas integridades referenciais etc...

Somos a favor de importar esta planilha de uma única vez, fazendo com que as colunas de cada grupo da planilha, sejam acrescentadas e atualizadas nas respectivas tabelas e subtabelas do bd. Porém o impasse é que o bd, como vemos, não está baseado em apenas em uma única tabela, mas em tabelas sub-relacionadas e formulário e subfolhas como que em forma de cascata

Utilizamos o Office 2003... gratos por qualquer ajuda

Olá bom dia.....

Apenas uma ideia: importaria os dados para uma tabela temporária dentro do Access, criaria um formulário para ver esses dados, depois, nesse mesmo formulário, um comando para exportar os campos pretendidos para suas referidas tabelas. Ao final desse processo de exportação para as tabelas, um comando para excluir os dados da tabela temporária.

Lembrando a ti, é apenas uma ideia.


Abraços.

importaria os dados para uma tabela temporária dentro do Access, criaria um formulário para ver esses dados, depois, nesse mesmo formulário, um comando para exportar os campos pretendidos para suas referidas tabelas. Ao final desse processo de exportação para as tabelas, um comando para excluir os dados da tabela temporária escreveu: escreveu:

Bom dia Silvio,

Ainda não tínhamos pensado nesta possibilidade...
Sabes onde encontrar um código para adaptar esta ideia?
De fato, acredito que esta ideia resolveria também o processo de importação.
Qualquer código que acharmos, faremos os testes e postaremos aqui os resultados.

Abraços.

Bom dia...

vamos por etapas.

1- criar uma tabela no Access.
2- criar campos nessa tabela com os mesmos nomes que há nas colunas da planilha em Excel.
3- de a ela, um nome amigável, exemplo tbltemp.
4- realizar a importação via Access para essa tbltemp.
5-  Criar um formulário baseado nessa tabela.  Criar um botão para exportar esses campos para as tabelas que necessitam dos mesmo.
6- de-lhe um nome amigável, exemplo " Exportar ".
7- nessa botão, vá em propriedades do mesmo, evento " clicar ".
8- insira o código abaixo:

Private Sub exportar_Click()
   
Dim strSQL As String, strSQL1 As String, strSQL2 As String
 
   strSQL = "INSERT INTO tabela 1 (idOS,Idcliente, Nome, Fone, Celular, CPF, CNPJ, DataOS, valortotal) Values (" & Me.IdOs.Value & "," & Me.Idcliente.Value & ",'" & Me.Nome.Value & "','" & Me.Fone.Value & "','" & Me.Celular.Value & "','" & Me.CPF.Value & "','" & Me.CNPJ.Value & "', '" & Me.DataOS.Value & "', '" & Me.TGOS.Value & "')"
   CurrentDb.Execute strSQL

strSQL1 = "INSERT INTO tabela 2(idOS,Idcliente, Nome, Fone, Celular, CPF, CNPJ, DataOS, valortotal) Values (" & Me.IdOs.Value & "," & Me.Idcliente.Value & ",'" & Me.Nome.Value & "','" & Me.Fone.Value & "','" & Me.Celular.Value & "','" & Me.CPF.Value & "','" & Me.CNPJ.Value & "', '" & Me.DataOS.Value & "', '" & Me.TGOS.Value & "')"
   CurrentDb.Execute strSQL1


strSQL2 = "INSERT INTO tblqualquer (idOS,Idcliente, Nome, Fone, Celular, CPF, CNPJ, DataOS, valortotal) Values (" & Me.IdOs.Value & "," & Me.Idcliente.Value & ",'" & Me.Nome.Value & "','" & Me.Fone.Value & "','" & Me.Celular.Value & "','" & Me.CPF.Value & "','" & Me.CNPJ.Value & "', '" & Me.DataOS.Value & "', '" & Me.TGOS.Value & "')"
   CurrentDb.Execute strSQL2

strSQL3 = "INSERT INTO tblqualquer (idOS,Idcliente, Nome, Fone, Celular, CPF, CNPJ, DataOS, valortotal) Values (" & Me.IdOs.Value & "," & Me.Idcliente.Value & ",'" & Me.Nome.Value & "','" & Me.Fone.Value & "','" & Me.Celular.Value & "','" & Me.CPF.Value & "','" & Me.CNPJ.Value & "', '" & Me.DataOS.Value & "', '" & Me.TGOS.Value & "')"
   CurrentDb.Execute strSQL3
     
   
End sub



Agora é apenas adaptar a instrução SQL para as tabelas referidas, com os referidos campos.

Bons estudos e sucesso a ti.

Abraços.

Private Sub exportar_Click()
Dim strSQL As String, strSQL1 As String, strSQL2 As String

strSQL = "INSERT INTO tabela 1 (idOS,Idcliente, Nome, Fone, Celular, CPF, CNPJ, DataOS, valortotal) Values (" & Me.IdOs.Value & "," & Me.Idcliente.Value & ",'" & Me.Nome.Value & "','" & Me.Fone.Value & "','" & Me.Celular.Value & "','" & Me.CPF.Value & "','" & Me.CNPJ.Value & "', '" & Me.DataOS.Value & "', '" & Me.TGOS.Value & "')" CurrentDb.Execute strSQL

strSQL1 = "INSERT INTO tabela 2(idOS,Idcliente, Nome, Fone, Celular, CPF, CNPJ, DataOS, valortotal) Values (" & Me.IdOs.Value & "," & Me.Idcliente.Value & ",'" & Me.Nome.Value & "','" & Me.Fone.Value & "','" & Me.Celular.Value & "','" & Me.CPF.Value & "','" & Me.CNPJ.Value & "', '" & Me.DataOS.Value & "', '" & Me.TGOS.Value & "')" CurrentDb.Execute strSQL1

strSQL2 = "INSERT INTO tblqualquer (idOS,Idcliente, Nome, Fone, Celular, CPF, CNPJ, DataOS, valortotal) Values (" & Me.IdOs.Value & "," & Me.Idcliente.Value & ",'" & Me.Nome.Value & "','" & Me.Fone.Value & "','" & Me.Celular.Value & "','" & Me.CPF.Value & "','" & Me.CNPJ.Value & "', '" & Me.DataOS.Value & "', '" & Me.TGOS.Value & "')" CurrentDb.Execute strSQL2

strSQL3 = "INSERT INTO tblqualquer (idOS,Idcliente, Nome, Fone, Celular, CPF, CNPJ, DataOS, valortotal) Values (" & Me.IdOs.Value & "," & Me.Idcliente.Value & ",'" & Me.Nome.Value & "','" & Me.Fone.Value & "','" & Me.Celular.Value & "','" & Me.CPF.Value & "','" & Me.CNPJ.Value & "', '" & Me.DataOS.Value & "', '" & Me.TGOS.Value & "')" CurrentDb.Execute strSQL3

End sub escreveu:

Boa Noite Silvio,

Feito alguns testes com o código proposto e algumas adaptações, e no entanto, até o momento, está dando alguns erros. O último foi "Erro em tempo de execução '3134:' Erro de sintaxe na instrução INSERT INTO."...Ao depurar abre-se o vb em "CurrentDb.Execute strSQL ". Até aqui não foi possível descobrir o que fazer exatamente...
Feito o passo-a-passo exatamente na sequencia como demonstrou. Desculpe a inexperiência, se puderes ainda dar uma analisada, segue o bd teste com os testes feitos...

Quanto ao código que apresentastes, ele é bem intuitivo e fácil de trabalhar, muito bom... Sua explanação também é fácil de entender. Caso se, por aqui, conseguirmos resolver estes erros, daremos um retorno...

Abraços

Boa noite.

Vou dar uma olhada nele.

Abraços.

Bom dia...

Isso vai dar trabalho......


Precisamos melhorar a padronização de dados desde a planilha envolvida na importação.

a- palavras com espaços e com assentos ( NÚMERO, DATA PEDIDO, DATA ANÁLISE, COD_PEDIDOS ), terá que mudar isso para:num, dtpedido, dtanalise, codpedidos.  Tais campos devem ser os mesmos nas tabelas.

b- campo confirma cliente....é do tipo verdadeiro/falso através de uma checkbox ? se for, mude para digitação do sim ou não.

c- codcliente, aqui que o bicho pega.  Na Access ele está como chave primária.  Sugestão: na tabCliente, crie um campo numérico para receber esses dados vindo da planilha.  Ex: Idcliente.  Pois você relacionando a chave primária com outras tabelas.  Isso vale também para outras tabelas ( identrega, idrecebidos..etc..etc..).

d- campos datas, use data abreviada e sem máscaras de entrada.  Ou deixar na tabelas que contém esse campo como texto e sem máscara de entrada.


Bom, com tudo isso vai ter um monte de coisas para mudar, tanto no Access como no teu Excel.  Quanto estiver tudo mudado, adapte esse código:

Código:

Private Sub Comando37_Click()


Dim db1 As Database, db2 As Database, db3 As Database, rs1 As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset
    If MsgBox("Confirma a Exportação desses campos  ?", vbYesNo + vbQuestion, "Aviso....") = vbYes Then
    
    Set db1 = CurrentDb
    
    Set rs1 = db1.OpenRecordset("tab_clientes")
    
    With rs1
    ' os campos abaixo do formulário tem que ser iguais aos que estão na tabela para realizar a exportação
    .AddNew
    ' campos que estão na tabela = campos do fomulario
   ' ![COD_CLIENTES] = Me.COD_CLIENTES
   ' ![CONFIRMA_CLIENTE] = Me.CONFIRMA_CLIENTE
    ![ANO] = Me.ANO
    ![UF] = Me.UF
    ![CLIENTE] = Me.CLIENTE
    ![CONSULTOR] = Me.CONSULTOR
    ![OBS] = Me.OBS
    ![NÚMERO] = Me.NÚMERO
  
   .Update
    End With
  
   DoCmd.RunCommand acCmdRefresh
    
'*************************************************************************************************

    Set db2 = CurrentDb
    Set rs2 = db2.OpenRecordset("tab_pedidos")
    With rs2
    .AddNew
    ![COD_PEDIDOS] = Me.COD_PEDIDOS
'    ![FK CLIENTES] = Me.FK_CLIENTES
'    ![CONF_PEDIDO] = Me.CONF_PEDIDO
  '  ![DATA_PEDIDO] = Me.DATA_PEDIDO
    
    
    '![PEDIDO] = Me.PEDIDO
    '![DATA_ANÁLISE] = Me.DATA_ANÁLISE
    ![DATA_APROVAÇÃO] = Me.DATA_APROVAÇÃO
    ![DATA_SITUAÇÃO] = Me.DATA_SITUAÇÃO
    ![VALOR] = Me.VALOR
    ![CAMPO3] = Me.CAMPO3
    .Update
    End With
DoCmd.RunCommand acCmdRefresh


Em tempo, para nomenclatura de tabelas use:tblcliente...para consultas qrycliente.....para formulários frmcliente
Bons estudos...e bom trabalho a ti.

Código:

Option Compare Database

Private Sub Comando37_Click()

    Dim db1 As Database, db2 As Database, db3 As Database, db4 As Database, rs1 As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset, rs4 As DAO.Recordset
        If MsgBox("Confirma a Exportação desses campos  ?", vbYesNo + vbQuestion, "Aviso....") = vbYes Then
        
        Set db1 = CurrentDb
        
        Set rs1 = db1.OpenRecordset("tblClientes")
        
        With rs1
        ' os campos abaixo do formulário tem que ser iguais aos que estão na tabela para realizar a exportação
        .AddNew
        ' campos que estão na tabela = campos do fomulario
       ' ![COD_CLIENTES] = Me.COD_CLIENTES
       ' ![CONFIRMA_CLIENTE] = Me.CONFIRMA_CLIENTE
        ![idcliente] = Me.idcliente
        ![numcliente] = Me.numcliente
        ![ano] = Me.ano
        ![uf] = Me.uf
        ![cliente] = Me.cliente
        ![consultor] = Me.consultor
        ![obs] = Me.obs
        
       .Update
        End With
      
       DoCmd.RunCommand acCmdRefresh
        
    '*************************************************************************************************

        Set db2 = CurrentDb
        Set rs2 = db2.OpenRecordset("tblPedidos")
        With rs2
        .AddNew
        ![idpedidos] = Me.idpedidos
        ![fkclientes] = Me.fkclientes
        ![dtpedido] = Me.dtpedido
        ![pedido] = Me.pedido
        ![dtanalise] = Me.dtanalise
        ![dtaprovacao] = Me.dtaprovacao
        ![tipopedido] = Me.tipopedido
        ![dtsitpedido] = Me.dtsitpedido
        ![valor] = Me.valor
        ![camp3] = Me.camp3
        .Update
        End With
    DoCmd.RunCommand acCmdRefresh


'**********************************************************************************************************
        Set db3 = CurrentDb
        Set rs3 = db3.OpenRecordset("tblEntregas")
        With rs3
        .AddNew
        ![identregas] = Me.identregas
        ![tipoentrega] = Me.tipoentrega
        ![dtsolicitacao] = Me.dtsolicitacao
        ![dtentrega] = Me.dtentrega
        ![dtprazo] = Me.dtprazo
        ![dtsitentrega] = dtsitentrega
        ![usuarioentreg] = Me.usuarioentreg
        
        .Update
        End With
    DoCmd.RunCommand acCmdRefresh
'**********************************************************************************************************
      Set db4 = CurrentDb
        Set rs4 = db4.OpenRecordset("tblRecibos")
        With rs4
        .AddNew
        ![idrecibos] = Me.idrecibos
        ![fkentregas] = Me.fkentregas
        ![recebidosituacao] = Me.recebidosituacao
        ![assdata] = Me.assdata
        ![dtsitrecibo] = Me.dtsitrecibo
        ![dtbaixa] = Me.dtbaixa
        ![usuarioreceb] = Me.usuarioreceb
        ![dt] = Me.dt
        
        .Update
        End With
    DoCmd.RunCommand acCmdRefresh
End If
End Sub



Boa noite Silvio

as sugestões foram bem vindas e foi bom a observação sobre a questão dos campos de chave primária, realmente foi meio crucial. A padronização deu um pouco de trabalho - mas conseguimos deixar um pouco melhor!

Até aqui, adaptamos o código acima que sugeriu para exportar e então ele roda legal até esta linha->   ![pedido] = Me.pedido  <-
e então ele para e exibe uma mensagem e pede pra depurar:
"Erro em tempo de execução '3421':
Erro de conversão de tipo de dados."

Por aqui, conseguimos os seguintes passos que sugeriu...
1) feita a importação da planilha1 para a tabela temporária tblPlan1;
2) feito o formulário para tentar exportar os novos registros para as respectivas tabelas...

Se por aqui, conseguirmos saber o que fazer... daremos retorno novamente.

Até...

Bom dia...


Tome cuidado com os campos tipo sim/não nas tabelas do Acess, deixe-os como texto; Pois vem da planilha do Excel como texto.

O correto, é deixar quase todos os campos como texto mesmo para facilitar a importação dos mesmos. com exceção das chaves primárias veja um exemplo simples:

tblpedidos: Idpedido,chave primária com numeração automática.

tblentrega: Identrega, chave primária com numeração automática.

Dentro da tblentrega, cria-se um campo chamado, Idpedidos tipo número, o qual a tblpedidos, irá se relacionar com a tblentrega na condição de um para muitos ( 1-n ).

Bom por enquanto é isso, é que eu estou um pouco enrolado no dia de hoje....mas comece por mudar isso...

Abraços.