Como inserir dados num arquivo excel atualizando um banco de dados

[Luiz Antônio de Almeida]

Olá pessoal!

 

Elaborei num arquivo Excel um banco de dados de alunos, onde faço a distribuição de turmas e lista de presença, porém, preciso controlar o fornecimento de materiais fornecido aos alunos.

 

No arquivo anexo, percebam que nomeei uma planilha como "Mat Forn" e, nesta planilha criei uma tabela onde ao informar o NI do aluno, automaticamente aparece o nome do aluno e turma. À partir de então, quando forem inseridos nesta tabela os dados referentes ao material fornecido (Tam e Quant.), gostaria que estes dados fossem atualizados na planilha "Lista Geral" e, mais um detalhe: Caso o NI digitado já tenha recebido os materiais, que estes apareçam nesta tabela.

 

Difícil, não? Será que é possível? 

 

Mas, enfim, quero ao inserir os dados (Tam e Quant) na Tabela da planilha "Mat Forn", estes sejam gravados na planilha "Lista Geral" e não mais aparecerem quando trocado o NI por outro que não tenha recebido os materiais.

 

Quem me socorre? rs...

 

Exemplo de cadastro de alunos-2.xlsx

[Luiz Antônio de Almeida]

"Como inserir dados num arquivo excel atualizando um banco de dados"

 

Como podem ver neste outro anexo, até fiz algumas fórmulas, mas, quando apago um NI para digitar outro, na planilha "Lista Geral" não ficam gravados os dados referentes aos materiais fornecidos.

Exemplo de cadastro de alunos-2.xlsx

[CasaDoHardware]

Minha pergunta o aluno recebe todos os materiais de uma vez, ou corre o risco de receber o material parcialmente?

[Luiz Antônio de Almeida]

A ideia é que o aluno receba o material de uma só vez, porém, por um motivo ou outro, faltam alguns itens, assim, temos que aguardar a reposição para entregarmos.

 

Por esta razão é que precisamos fazer este contrôle.

 

Obrigado pela atenção!

 

Luiz

 

[Visitante]

Olá, Luiz.

Veja se ajuda.

 

Instale o código abaixo em um módulo comum, assim:
1. copie o código daqui
2. a partir de qualquer planilha tecle 'Alt+F11' para acessar o editor de VBA
3. no menu do editor >> Inserir >> Módulo
4. cole o código na janela em branco que vai se abrir
5. feito! 'Alt+Q' para retornar para a planilha e testar

para rodar o código:
6. tecle 'Alt+F8' >> selecione a macro CadastraMaterial >> Executar, ou insira um botão na planilha "Mat Forn" e vincule-o à macro ou vincule-a a um atalho de teclado (Alt+F8 > Opções).

Sub CadastraMaterial()
 Dim NI As Range
  Set NI = Sheets("Lista Geral").[D:D].Find([B7])
   If Not NI Is Nothing Then
    Sheets("Lista Geral").Cells(NI.Row, 10).Resize(, 6).Value = _
     Range("E7:J7").Value
    Range("B7,E7:J7").Value = ""
   Else: MsgBox "NI não encontrado"
   End If
End Sub

 

Instale o código abaixo no módulo da planilha "Mat Forn", assim:
1. copie o código daqui
2. clique com o direito na guia da planilha "Mat Forn" e escolha 'Exibir código'
3. cole o código na janela em branco que vai se abrir
4. feito! 'Alt+Q' para retornar para a planilha e testar
 

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim NI As Range
  If Target.Count > 1 Then Exit Sub
  If Target.Address = "$B$7" And Target.Value <> "" Then
    Set NI = Sheets("Lista Geral").[D:D].Find(Target.Value)
      If Not NI Is Nothing Then
        Range("E7:J7").Value = Sheets("Lista Geral").Cells(NI.Row, 10).Resize(, 6).Value
      Else: MsgBox "NI não encontrado"
      End If
  ElseIf Target.Address = "$B$7" And Target.Value = "" Then
   Range("E7:J7").Value = ""
  End If
End Sub

 

[Luiz Antônio de Almeida]

Obrigado, Osvaldo!

Mas, vou testar somente quando chegar em casa, pois, aqui no meu trabalho não consegui habilitar a "Guia Desenvolvedor" onde tenho acesso. Um é Excel 2008 e o outro é Excel em inglês de um Mac...   =/

Tentei, tentei e não consegui...

Mas, vou tentar e te retorno!

[Luiz Antônio de Almeida]

Perfeito, Osvaldo!

Acabei de testar e funcionou à contento.

Muito bom!

 

Não abusando, gostaria de saber se, caso eu queira na Planilha "Mat Form" separar a minha tabela em tres tabelas, ou seja, ao invés de ter uma tabela numa linha só (E7:J7), eu ter três, por exemplo: B12:C12 e E12, B16:C16 e E16, conforme anexo, teria que fazer muitas alterações nos códigos?

 

Grato pela atenção e forte abraço!

 

Luiz

Exemplo de cadastro de alunos-TESTANDO.xlsx

[Visitante]

Coloque estes no lugar dos anteriores.

 

Sub CadastraMaterialV2()
 Dim NI As Range
  Set NI = Sheets("Lista Geral").[D:D].Find([B7])
   If Not NI Is Nothing Then
    With Sheets("Lista Geral")
     .Cells(NI.Row, 10).Resize(, 2).Value = Range("B12:C12").Value
     .Cells(NI.Row, 12).Resize(, 2).Value = Range("B16:C16").Value
     .Cells(NI.Row, 14).Value = Range("E12").Value
     .Cells(NI.Row, 15).Value = Range("E16").Value
    End With
    Range("B7,B12:C12,B16:C16,E12,E16").Value = ""
   Else: MsgBox "NI não encontrado"
   End If
End Sub

 

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim NI As Range
  If Target.Count > 1 Then Exit Sub
  If Target.Address = "$B$7" And Target.Value <> "" Then
    Set NI = Sheets("Lista Geral").[D:D].Find(Target.Value)
      If Not NI Is Nothing Then
        With Sheets("Lista Geral")
         Range("B12:C12").Value = .Cells(NI.Row, 10).Resize(, 2).Value
         Range("B16:C16").Value = .Cells(NI.Row, 12).Resize(, 2).Value
         Range("E12").Value = .Cells(NI.Row, 14).Value
         Range("E16").Value = .Cells(NI.Row, 15).Value
        End With
      Else: MsgBox "NI não encontrado"
      End If
  ElseIf Target.Address = "$B$7" And Target.Value = "" Then
   Range("B7,B12:C12,B16:C16,E12,E16").Value = ""
  End If
End Sub

 

[Luiz Antônio de Almeida]

Perfeito, Osvaldo!

Você é fera!!!

Tanto que me faz interessar por programação. Vou aprender!

Obrigado mais uma vez e muito sucesso à você, cara!!!

[Luiz Antônio de Almeida]

Oi Osvaldo!

Olha eu aqui de novo... rs

Fiz um outro arquivo (bem maior), baseado nas suas orientações, mas, na hora de finalizar está informando Erro em tempo de execução 1004... =/

Informa que as células que quero modificar devem estar protegidas, mas, não estão e eu não sei como solucionar...

Desde já, muitíssimo obrigado!

Contr Mat fornecido.xlsm

Contr Mat fornecido.xlsm

[Visitante]

Fala, Luiz.

Na planilha "Lista Geral" as células das colunas 'I:W' que recebem os dados do código estão bloqueadas por isso o código trava. Para o código não travar desbloqueie as células antes de proteger a planilha ou se você quiser mantê-las bloqueadas acrescente no código CadastraMaterialV2 a segunda linha abaixo.
 

...

... 

With Sheets("Lista Geral")
     .Protect UserInterFaceOnly:=True

...

...

 

Curiosidade - qual a finalidade das fórmulas na planilha "Lista Geral" pois elas serão sobrepostas pelos dados da planilha "Forn Mat" ... (?)

 

[Luiz Antônio de Almeida]

Oi Osvaldo!

Bom, quando eu inseri as formulas, a ideia é que quando eu fosse digitar na célula B5 da Planilha Forn Mat um NI que já tivesse recebido material, imediatamente aparecesse na tabela as quantidades...mas, com a macro, realmente, não faz mais sentido...

Obrigado pelas dicas Osvaldo! E, desculpe-me por perturbá-lo tanto... rs