Excel Consultar Acces pelo Excel

[marcelo costa Jr]

Boa Tarde,

 

sou novo por aqui, então por favor se já houver algo relacionado me auxilie pois não encontrei exatamente o que preciso.

 

Tenho uma planilha onde eu faço todo o controle dos veículos da empresa, e toda minha base de dados está no Acces. ( para onde quero migrar )

tenho um ProcV em umas das células onde ele consulta uma planilha onde estão esses dados. Pela placa ele me retorna quem é o motorista, e o CPF do mesmo.

Eu preciso saber agora COMO EU FAÇO ESSE PROCV IR BUSCAR LÁ NO ACCES? Para eu poder apagar essa base que está em outra planilha excel.

[Eder Neumann]

crie uma aba para consulta onde o excel irá importar a tabela com os dados do access (vá em dados/obter dados no excel) configure a consulta (de preferência mantenha somente os valores excenciais para a sua formula), ajuste a formula e oculte a aba.

[marcelo costa Jr]

28 minutos atrás, Eder Neumann disse:

crie uma aba para consulta onde o excel irá importar a tabela com os dados do access (vá em dados/obter dados no excel) configure a consulta (de preferência mantenha somente os valores excenciais para a sua formula), ajuste a formula e oculte a aba.

vai ser o mesmo procv só que dentro da mesma planilha? Mas e a conexão com o Acces ?

[Basole]

@marcelo costa Jr segue exemplo basico de busca de dados dentro de banco de dados access

 

Sub PROCV_IN_TABELA_ACCESS()
Dim sql         As String
Dim db          As Object
Dim rs          As Object
Dim Path        As String
Dim s_Placa     As String

Set db = VBA.CreateObject("ADODB.Connection")
Set rs = VBA.CreateObject("ADODB.Recordset")

Path = ThisWorkbook.Path & "\AQUI NOME DO BANCO DE DADOS ACCESS E A EXTENSAO"
s_Placa = "Aqui_dados_da_Placa" 'ou a celula que contem a placa
  
  db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";"
           
      On Error GoTo trat_err
      
       sql = "SELECT * "
       sql = sql & "FROM [NOME_DA_SUA_TABELA] "
       sql = sql & "WHERE [CAMPO_PLACA]='" & s_Placa & "'"
              
       rs.Open sql, db, 3, 3
       
       
    While Not rs.EOF
     
       MsgBox rs![NOME DO CAMPO MOTORISTA] & VBA.vbNewLine & _
              rs![NOME DO CAMPO CPF]
         
         rs.MoveNext
    Wend


trat_err:

 db.Close: Set db = Nothing
  Set rs = Nothing

End Sub

* OBS: Alteracoes que devem ser feitas no codigo de acordo com os seus dados:

  

Path = ThisWorkbook.Path & "\AQUI NOME DO BANCO DE DADOS ACCESS E A EXTENSAO"

     * coloque o endereco do seu banco, nome e extensao

Altere de acordo com os dados da tabela do access: [NOME_DA_SUA_TABELA], [CAMPO_PLACA], [NOME DO CAMPO MOTORISTA], [NOME DO CAMPO CPF]

          

 

 

[marcelo costa Jr]

7 minutos atrás, Basole disse:

@marcelo costa Jr segue exemplo basico de busca de dados dentro de banco de dados access

 

Sub PROCV_IN_TABELA_ACCESS()
Dim sql         As String
Dim db          As Object
Dim rs          As Object
Dim Path        As String
Dim s_Placa     As String

Set db = VBA.CreateObject("ADODB.Connection")
Set rs = VBA.CreateObject("ADODB.Recordset")

Path = ThisWorkbook.Path & "\AQUI NOME DO BANCO DE DADOS ACCESS E A EXTENSAO"
s_Placa = "Aqui_dados_da_Placa" 'ou a celula que contem a placa
  
  db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";"
           
      On Error GoTo trat_err
      
       sql = "SELECT * "
       sql = sql & "FROM [NOME_DA_SUA_TABELA] "
       sql = sql & "WHERE [CAMPO_PLACA]='" & s_Placa & "'"
              
       rs.Open sql, db, 3, 3
       
       
    While Not rs.EOF
     
       MsgBox rs![NOME DO CAMPO MOTORISTA] & VBA.vbNewLine & _
              rs![NOME DO CAMPO CPF]
         
         rs.MoveNext
    Wend


trat_err:

 db.Close: Set db = Nothing
  Set rs = Nothing

End Sub

* OBS: Alteracoes que devem ser feitas no codigo de acordo com os seus dados:

  

Path = ThisWorkbook.Path & "\AQUI NOME DO BANCO DE DADOS ACCESS E A EXTENSAO"

     * coloque o endereco do seu banco, nome e extensao

Altere de acordo com os dados da tabela do access: [NOME_DA_SUA_TABELA], [CAMPO_PLACA], [NOME DO CAMPO MOTORISTA], [NOME DO CAMPO CPF]

          

 

 

[CAMPO_PLACA], [NOME DO CAMPO MOTORISTA], [NOME DO CAMPO CPF] =  coluna  B, C & D. Só coloco B, C, D ?

[Eder Neumann]

4 minutos atrás, marcelo costa Jr disse:

vai ser o mesmo procv só que dentro da mesma planilha? Mas e a conexão com o Acces ?

 

33 minutos atrás, Eder Neumann disse:

vá em dados/obter dados

dai selecione dados do access e encontre o arquivo do seu BD

[Basole]

@marcelo costa Jr voce tem que alterar no codigo,  os nomes dos campos, de acordo com os nomes dos campos da tabela do access. 

 

* Se tiver dificuldades, anexe seus arquivos ou exemplos aqui.!

[marcelo costa Jr]

@Basole

7 minutos atrás, Eder Neumann disse:

 

dai selecione dados do access e encontre o arquivo do seu BD

E MINHA FÓRMULA QUE HOJE ESTÁ

 =SEERRO(PROCV(@B$8:B$67;'C:\Users\luizjunior\Desktop\[dados.xlsx]Dados'!$A$2:B$10900;2;FALSO);"-")

 ...

VAI FICAR COMO A CONSULTA ?

 

adicionado 9 minutos depois

@Basole

[Basole]

18 minutos atrás, marcelo costa Jr disse:

E FÓRMULA QUE HOJE ESTÁ

 =SEERRO(PROCV(@B$8:B$67;'C:\Users\luizjunior\Desktop\[dados.xlsx]Dados'!$A$2:B$10900;2;FALSO);"-")

 VAI FICAR COMO A CONSULTA ?

 

@marcelo costa Jr de acordo com a imagem do seu banco de dados access, fica desta forma a consulta:

 

Sub PROCV_IN_TABELA_ACCESS()
Dim sql         As String
Dim db          As Object
Dim rs          As Object
Dim Path        As String
Dim s_Placa     As String

Set db = VBA.CreateObject("ADODB.Connection")
Set rs = VBA.CreateObject("ADODB.Recordset")

Path = "C:\Users\luizjunior\Documents\BD_placas.accdb"
s_Placa = "DADOS DA PLACA" '* ou a celula que contem a placa
  
  db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";"
           
      On Error GoTo trat_err
      
       sql = "SELECT * "
       sql = sql & "FROM [Dados] "
       sql = sql & "WHERE [PLACA]='" & s_Placa & "'"
              
       rs.Open sql, db, 3, 3
       
       
    While Not rs.EOF
     
       MsgBox rs![MOTORISTA] & VBA.vbNewLine & _
              rs![C#P#F# motorista]
         
         rs.MoveNext
    Wend


trat_err:

 db.Close: Set db = Nothing
  Set rs = Nothing

End Sub

 

[marcelo costa Jr]

@Basole @Basole @Basole Ai na imagem que enviei anteriormente, quando eu digito a placa no campo " CAVALO " aparece o Motorista e o Cpf do mesmo, onde tem a fórmula  =SEERRO(PROCV(@B$8:B$67;'C:\Users\luizjunior\Desktop\[dados.xlsx]Dados'!$A$2:B$10900;2;FALSO);"-")  no campo [ MOTORISTA /  CPF ] como ficaria essa fórmula agora com a consulta no Acces?

[Basole]

 

@marcelo costa Jr 

Nao sei o que significa campo CAVALO, pois na imagem nao vi nada relacionado.

 

Eu postei um exemplo simples de consulta ao banco access, mas se pretende adaptar a sua planilha, seu cenário, preciso que disponibilize exemplos dos seus arquivos para tentarmos adaptar a sua situacao, pois o exemplo de codigo generico, nao atende a todas as demandas.

 

De quaquer forma ajustei o codigo a uma funcao (udf)

 

* Para testar cole o codigo abaixo em um modulo  e em qualquer célula coloque, por exemplo =PROCV_ACCESS(A1)

 

Function PROCV_ACCESS(rng As Range) As String
Dim sql         As String
Dim db          As Object
Dim rs          As Object
Dim Path        As String
Dim s_Placa     As String

Set db = VBA.CreateObject("ADODB.Connection")
Set rs = VBA.CreateObject("ADODB.Recordset")

Path = "C:\Users\luizjunior\Documents\BD_placas.accdb"

s_Placa = rng.Value2
  
  db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";"
           
      On Error GoTo trat_err
      
       sql = "SELECT * "
       sql = sql & "FROM [Dados] "
       sql = sql & "WHERE [PLACA]='" & s_Placa & "'"
              
       rs.Open sql, db, 3, 3
       
       
    While Not rs.EOF
     
       PROCV_ACCESS = rs![MOTORISTA] & "; " & _
              rs![C#P#F# motorista]
         
         rs.MoveNext
    Wend


trat_err:

 db.Close: Set db = Nothing
  Set rs = Nothing

End Function

 

 

 

 

[marcelo costa Jr]

@Basole  SEGUE;

 

https://drive.google.com/drive/folders/1p6rMzF2oasmJ5JFTXb0spd43-61Edqtd?usp=sharing

[Basole]

@marcelo costa Jr voce precisa compartilhar publicamente seus arquivos para qualquer pessoa possa ter acesso

[marcelo costa Jr]

@Basole https://drive.google.com/open?id=1p6rMzF2oasmJ5JFTXb0spd43-61Edqtd

 

agora vai !

[Basole]

@marcelo costa Jr sua tabela Dados.xlsx esta vinculada ao banco de dados BD_placas.accdb sendo assim nao tenho como acessar os dados.

 

Disponibilize o arquivo Dados.xlsx para eu tentar importar para o access.

 

 

[marcelo costa Jr]

7 minutos atrás, Basole disse:

@marcelo costa Jr sua tabela Dados.xlsx esta vinculada ao banco de dados BD_placas.accdb sendo assim nao tenho como acessar os dados.

 

Disponibilize o arquivo Dados.xlsx para eu tentar importar para o access.

 

 

o que está no Acces já esta importado .. mas de qualquer forma está feito, já upei!

[Basole]

@marcelo costa Jr apenas vinculado. Segue os arquivos ajustados a funcao 

 

Para retornar o motorista use =PROCV_ACCESS(Celula;FALSO);e para retornar o CPF PROCV_ACCESS(Celula;VERDADEIRO)

 

             

Spoiler

Function PROCV_ACCESS(rng As Range, Optional Cpf As Boolean) As String
Dim sql         As String
Dim db          As Object
Dim rs          As Object
Dim Path        As String
Dim s_Placa     As String

Set db = VBA.CreateObject("ADODB.Connection")
Set rs = VBA.CreateObject("ADODB.Recordset")

Path = "C:\Users\luizjunior\Documents\BD_placas.accdb"

s_Placa = rng.Value2
  
  db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";"
           
      On Error GoTo trat_err
      
       sql = "SELECT * "
       sql = sql & "FROM [Dados] "
       sql = sql & "WHERE [PLACA]='" & s_Placa & "'"
              
       rs.Open sql, db, 3, 3
       
       
    If Not rs.EOF Then
    
     If Cpf = False Then
       PROCV_ACCESS = rs![MOTORISTA]
     Else
       PROCV_ACCESS = rs![CPF motorista]
     End If
        
    End If

trat_err:

 db.Close: Set db = Nothing
  Set rs = Nothing

End Function

 

Segue os arquivos, e o BD com os dados importados: https://1drv.ms/u/s!AklQQunG_lmmg0sXEdNheA2Ugyjt?e=ncrJl9

 

[marcelo costa Jr]

14 horas atrás, Basole disse:

@marcelo costa Jr apenas vinculado. Segue os arquivos ajustados a funcao 

 

Para retornar o motorista use =PROCV_ACCESS(Celula;FALSO);e para retornar o CPF PROCV_ACCESS(Celula;VERDADEIRO)

 

             

  Mostrar conteúdo oculto

Function PROCV_ACCESS(rng As Range, Optional Cpf As Boolean) As String
Dim sql         As String
Dim db          As Object
Dim rs          As Object
Dim Path        As String
Dim s_Placa     As String

Set db = VBA.CreateObject("ADODB.Connection")
Set rs = VBA.CreateObject("ADODB.Recordset")

Path = "C:\Users\luizjunior\Documents\BD_placas.accdb"

s_Placa = rng.Value2
  
  db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";"
           
      On Error GoTo trat_err
      
       sql = "SELECT * "
       sql = sql & "FROM [Dados] "
       sql = sql & "WHERE [PLACA]='" & s_Placa & "'"
              
       rs.Open sql, db, 3, 3
       
       
    If Not rs.EOF Then
    
     If Cpf = False Then
       PROCV_ACCESS = rs![MOTORISTA]
     Else
       PROCV_ACCESS = rs![CPF motorista]
     End If
        
    End If

trat_err:

 db.Close: Set db = Nothing
  Set rs = Nothing

End Function

 

Segue os arquivos, e o BD com os dados importados: https://1drv.ms/u/s!AklQQunG_lmmg0sXEdNheA2Ugyjt?e=ncrJl9

 

FUNCIONOU! muito obrigado!

[michel.silva]

Me ajudou muito este exemplo, ficou uma questão.

 

É possivel puxar mais campos do banco de dados?

 Exemplo:

Em uma celula A1 eu digido o "codigo" e na B1 ele coloca a descrição na C1 o valor na D1 a quantidade...

 

Pode utilizar o mesmo código do problema anterior só sinaliza o que preciso adicionar.

 

Agradeço muito!!!

[Basole]

Aproveitando o exemplo acima, segue as adaptacoes:

 

     PROCV_ACCESS( Celula; Numero )

 

Use os parametros :

     1 para retornar a descricao

     2 para retornar o valor

     3 para retornar a quantidade

<code>

Function PROCV_ACCESS(rng As Range, Optional Cpf As Single) As String
Dim sql         As String
Dim db          As Object
Dim rs          As Object
Dim Path        As String
Dim s_Placa     As String

Set db = VBA.CreateObject("ADODB.Connection")
Set rs = VBA.CreateObject("ADODB.Recordset")

Path = "C:\Users\luizjunior\Documents\BD_placas.accdb"
s_Placa = rng.Value2
 
  db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";"
           
      On Error GoTo trat_err
      
       sql = "SELECT * "
       sql = sql & "FROM [Dados] "
       sql = sql & "WHERE [PLACA]='" & s_Placa & "'"
              
       rs.Open sql, db, 3, 3
       
       
    If Not rs.EOF Then
         
     Select Case Cpf
            Case Is = 1
              PROCV_ACCESS = rs![MOTORISTA]
            Case Is = 2
              PROCV_ACCESS = rs![CPF motorista]
            Case Is = 3
              PROCV_ACCESS = rs![Filial]
     End Select
   
   
    End If

trat_err:

 db.Close: Set db = Nothing
  Set rs = Nothing

End Function

</code>

[michel.silva]

Em 16/01/2021 às 01:08, Basole disse:

Aproveitando o exemplo acima, segue as adaptacoes:

 

     PROCV_ACCESS( Celula; Numero )

 

Use os parametros :

     1 para retornar a descricao

     2 para retornar o valor

     3 para retornar a quantidade

<code>

Function PROCV_ACCESS(rng As Range, Optional Cpf As Single) As String
Dim sql         As String
Dim db          As Object
Dim rs          As Object
Dim Path        As String
Dim s_Placa     As String

Set db = VBA.CreateObject("ADODB.Connection")
Set rs = VBA.CreateObject("ADODB.Recordset")

Path = "C:\Users\luizjunior\Documents\BD_placas.accdb"
s_Placa = rng.Value2
 
  db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";"
           
      On Error GoTo trat_err
      
       sql = "SELECT * "
       sql = sql & "FROM [Dados] "
       sql = sql & "WHERE [PLACA]='" & s_Placa & "'"
              
       rs.Open sql, db, 3, 3
       
       
    If Not rs.EOF Then
         
     Select Case Cpf
            Case Is = 1
              PROCV_ACCESS = rs![MOTORISTA]
            Case Is = 2
              PROCV_ACCESS = rs![CPF motorista]
            Case Is = 3
              PROCV_ACCESS = rs![Filial]
     End Select
   
   
    End If

trat_err:

 db.Close: Set db = Nothing
  Set rs = Nothing

End Function

</code>

Perfeito