Dúvida com PROCV  [Resolvido]

Visual Basic for Aplication e macros no Excel.
Regras do fórum
Sua dúvida foi respondida? Marque como RESOLVIDO em seus tópicos, usando o botão com marca verde. Imagem

Dúvida com PROCV

Mensagempor xlarruda » Ter Mar 13, 2018 4:10 pm

Pessoal tenho essa função simples que faz um procv buscando dados na mesma planilha.
O problema é que não estou conseguindo fazer ela puxar os dados de um outro arquivo. Isso é possível?


Detalhe: Toda as vezes que tento referenciar outro arquivo excel, aparece a mensagem: "Sbuscrito fora do intervalo"

Código: Selecionar todos
Public Function busca_dados(rg As Range)
busca_dados= Application.WorksheetFunction.VLookup(rg, Plan1.Range("E1:F3"), 2, 0)
End Function


Desde já, agradeço...
Você não está autorizado a ver ou baixar esse anexo.
___________________________________________________________________________________________
Se a resposta atendeu sua dúvida, por favor, clique no Gostei e marque o Tópico como RESOLVIDO.

Att.

André Arruda
Avatar do usuário
xlarruda
Membro
Membro
 
Mensagens: 687
Registrado em: Qua Out 04, 2017 9:41 am
Localização: Cuiabá-MT
Has thanked: 92 times
Have thanks: 273 times

{ SO_SELECT }

Re: Dúvida com PROCV

Mensagempor Mauro Coutinho » Ter Mar 13, 2018 4:44 pm

Não cheguei a ver seu modelo, download está bloqueado hoje, mas acho que deve estar falando de dois arquivos separados, se for isto de uma olhada neste exemplo que fiz ja ha algum tempo para uma duvida aqui do forum mesmo.

VLOOKP_OutraPasta_mvc1.rar


[]s
Você não está autorizado a ver ou baixar esse anexo.
Mauro Coutinho - Moderador
São José dos Pinhais-Pr


Se a resposta foi últil para você, por gentileza, clique na mãozinha ao lado direito da sua tela. No canto superior.

For this post the author Mauro Coutinho thanked:
xlarruda (Ter Mar 13, 2018 4:54 pm)
Avatar do usuário
Mauro Coutinho
Ninja do Excel
Ninja do Excel
 
Mensagens: 3934
Registrado em: Sex Mar 26, 2010 9:24 pm
Localização: São José dos Pinhais - Pr
Has thanked: 181 times
Have thanks: 1447 times

Re: Dúvida com PROCV

Mensagempor xlarruda » Ter Mar 13, 2018 4:51 pm

Colega Mauro Coutinho Obrigado pelo retorno!
Seria praticamente isso. Porém ao invés de uma Sub queria trazê-la como uma Function. onde eu apenas referenciava uma célula e ele retornava o valor procurado.

Isso porque iria ultilizá-la como um Suplemento para que os demais colaboradores da empresa também pudessem usá-la.

Tipo

A B
1 =achar(A1)
___________________________________________________________________________________________
Se a resposta atendeu sua dúvida, por favor, clique no Gostei e marque o Tópico como RESOLVIDO.

Att.

André Arruda
Avatar do usuário
xlarruda
Membro
Membro
 
Mensagens: 687
Registrado em: Qua Out 04, 2017 9:41 am
Localização: Cuiabá-MT
Has thanked: 92 times
Have thanks: 273 times

Re: Dúvida com PROCV

Mensagempor Mauro Coutinho » Ter Mar 13, 2018 11:23 pm

Arruda, até onde eu sei, que me corrijam os experts, não é possivel fazer da forma que pretende, teria de ter o outro arquivo aberto, se não me engano este assunto já chegou a ser discutido em alguns foruns, estamos falando em construir uma UDF (Função Definida pelo Usuário), o office teve várias atualizações, mas pelo que já li,na questão do VBA, não teve nenhuma, mas de uma pesquisada no Google por "udf vlookup another workbook without open excel vba" e encontrara vários posts sobre o assunto, quem sabe consegue alguma informação a mais.

Este foi criado em 2008:
Possible to have UDF vlookup from closed workbook?
https://www.mrexcel.com/forum/excel-que ... kbook.html

[]s
Mauro Coutinho - Moderador
São José dos Pinhais-Pr


Se a resposta foi últil para você, por gentileza, clique na mãozinha ao lado direito da sua tela. No canto superior.

For this post the author Mauro Coutinho thanked:
xlarruda (Qua Mar 14, 2018 8:40 am)
Avatar do usuário
Mauro Coutinho
Ninja do Excel
Ninja do Excel
 
Mensagens: 3934
Registrado em: Sex Mar 26, 2010 9:24 pm
Localização: São José dos Pinhais - Pr
Has thanked: 181 times
Have thanks: 1447 times

Re: Dúvida com PROCV

Mensagempor xlarruda » Qua Mar 14, 2018 8:43 am

Colega Mauro Coutinho, mais uma vez muito obrigado pela sua resposta!

Realmente pesquisei , e não encontrei nenhuma forma de realizar isso de maneira satisfatória. O mais perto que cheguei foi inserindo meus dados na planilha "PERSONAL.xlsb". (funciona, mas , não é exatamente o que eu precisava).


Mesmo assim, muito obrigado pela sua atenção!

Abrç!
___________________________________________________________________________________________
Se a resposta atendeu sua dúvida, por favor, clique no Gostei e marque o Tópico como RESOLVIDO.

Att.

André Arruda
Avatar do usuário
xlarruda
Membro
Membro
 
Mensagens: 687
Registrado em: Qua Out 04, 2017 9:41 am
Localização: Cuiabá-MT
Has thanked: 92 times
Have thanks: 273 times

Re: Dúvida com PROCV

Mensagempor EdsonBR » Qua Mar 14, 2018 1:50 pm

Colega XLArruda boa tarde

Pelo que entendi, vc quer manter uma tabela fixa em um local, que poderia ser numa planilha do próprio suplemento da UDF e usá-la como base da pesquisa, sendo ela o argumento Matriz Tabela da ProcV, sendo que sua UDF rodaria em qualquer pasta de trabalho é isso?

Se for isso é perfeitamente possível (inseri aqui como teste no meu suplemento, que inclusive está em rede e rodou sem problemas):

Estando a tabela já na planilha do suplemento (supondo A1:B4), crie a UDF num módulo no Suplemento e referencie o intervalo qualificando corretamente o Workbook do suplemento. Exemplo:

Código: Selecionar todos
Public Function busca_dados(rg As Range)
  Dim rgTabela As Range: Set rgTabela = ThisWorkbook.Worksheets(1).Range("A1:B4")
  busca_dados = Application.WorksheetFunction.VLookup(rg, rgTabela, 2, 0)
  Set rgTabela = Nothing
End Function


Aí é só salvar e instalar seu suplemento e usar a UDF busca_dados normalmente.
Imagem

For this post the author EdsonBR thanked: 2
Mauro Coutinho (Seg Mar 19, 2018 11:02 pm) • xlarruda (Qua Mar 14, 2018 3:12 pm)
Avatar do usuário
EdsonBR
Membro
Membro
 
Mensagens: 514
Registrado em: Qui Nov 05, 2015 11:43 pm
Localização: Joinville, SC
Has thanked: 100 times
Have thanks: 309 times

Re: Dúvida com PROCV

Mensagempor xlarruda » Qua Mar 14, 2018 3:11 pm

Olá colega EdsonBR é quase isso.

Pois eu até consegui fazer como você está falando. Muito Obrigado! Deu certo. porém apenas meu computador tem acesso a essa fórmula.
Minha necessidade era de usar como base de dados, um arquivo em pasta compartilhada, para que assim, meu dois colegas de setor também possam usar essa UDF no excel instalado em seus computadores.
Nesse caso, sempre que eu atualizasse o banco de dados (o arquivo da pasta compartilhada) , bastava eles inserirem a fórmula normalmente em suas planilhas que iria funcionar.

Resumindo : A UDF deveria fazer referência ao arquivo que está na pasta compartilhada (banco de dados). Desse forma eu instalaria o suplemento no excel de todos os funcionários do setor. Isso faria com que , sempre que o BD fosse atualizado a função também funcionaria com dados "atualizados" no computador de todos.

Não sei se fui claro.. Abrç!
___________________________________________________________________________________________
Se a resposta atendeu sua dúvida, por favor, clique no Gostei e marque o Tópico como RESOLVIDO.

Att.

André Arruda
Avatar do usuário
xlarruda
Membro
Membro
 
Mensagens: 687
Registrado em: Qua Out 04, 2017 9:41 am
Localização: Cuiabá-MT
Has thanked: 92 times
Have thanks: 273 times

Re: Dúvida com PROCV  [Resolvido]

Mensagempor EdsonBR » Qui Mar 15, 2018 3:20 pm

xlarruda escreveu:... porém apenas meu computador tem acesso a essa fórmula.


Não se o suplemento também estiver em rede, como tenho aqui na empresa. Aí necessitaria 1 suplemento comum para todas as máquinas.

xlarruda escreveu:Minha necessidade era de usar como base de dados, um arquivo em pasta compartilhada, para que assim, meu dois colegas de setor também possam usar essa UDF no excel instalado em seus computadores...
...A UDF deveria fazer referência ao arquivo que está na pasta compartilhada (banco de dados). Desse forma eu instalaria o suplemento no excel de todos os funcionários do setor. Isso faria com que , sempre que o BD fosse atualizado a função também funcionaria com dados "atualizados" no computador de todos.


No caso de vc desejar ter essa base de dados num arquivo separado, digamos sua Planilha2.xlsx, há um complicador que é o fato de que esse arquivo já tem que estar aberto para a UDF poder utilizá-lo, como o amigo Mauro Coutinho bem apontou acima. Isto porque procedimentos tipo Function foram desenhados para ler algo e são limitados quando se trata de fazer algo. Métodos como Workbooks.Open são simplesmente ignorados, nem mesmo códigos de erro são gerados.

Resumindo, pra usar a UDF, a Planilha2.xlsx teria que estar já aberta.

Pra não ter que abri-la manualmente sempre que for usar a UDF, vc poderia abri-la sempre que iniciar o Excel e fechá-la ao encerrar o Excel. Para isso, no módulo de Pasta EstaPasta_de_Trabalho do arquivo PERSONAL.XLSB daria pra usar:

Código: Selecionar todos
Option Explicit
Private Sub Workbook_Open()
   Workbooks.Open Filename:="\\Servidor\PastaCompartilhada\Planilha2.xlsx" ', ReadOnly:=True
   Application.Windows("Planilha2.xlsx").Visible = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Workbooks("Planilha2.xlsx").Close
End Sub


E vc deixaria somente sua UDF no suplemento criado:

Código: Selecionar todos
Option Explicit
Dim wbTab As Workbook, rgTab As Range

Public Function busca_dados(rg As Range)
   If wbTab Is Nothing Then
     Set wbTab = Workbooks("Planilha2.xlsx")
     Set rgTab = wbTab.Worksheets("Plan1").Range("A1:B4")
   End If
   busca_dados = Application.WorksheetFunction.VLookup(rg, rgTab, 2, 0)
End Function


Obs.:
1) testei aqui e ficou funcional
2) seria bem importante fazer uma boa rotina de tratamento de erros no PERSONAL.XLSB prevendo situações onde o arquivo Planilha2.xlsx tenha sido aberto manualmente ou não ter sido fechado pelo código.
Imagem

For this post the author EdsonBR thanked: 2
Mauro Coutinho (Seg Mar 19, 2018 11:02 pm) • xlarruda (Qui Mar 15, 2018 3:54 pm)
Avatar do usuário
EdsonBR
Membro
Membro
 
Mensagens: 514
Registrado em: Qui Nov 05, 2015 11:43 pm
Localização: Joinville, SC
Has thanked: 100 times
Have thanks: 309 times

Re: Dúvida com PROCV

Mensagempor xlarruda » Qui Mar 15, 2018 3:59 pm

TOP! TOP! 1000x TOP!!!

Colega EdsonBR Muito Obrigado! É exatamente isso que eu queria.

Dessa forma eu apenas instalo o suplemento nos outros computadores, insiro o código na PERSONAL.xlsb E Pronto! problema RESOLVIDO!

Ficarei acompanhando essa questão dos erros, pois em algum momento imagino que irá acontecer.

Vou testar e qlqr coisa marco como Resolvido. Abrç!
___________________________________________________________________________________________
Se a resposta atendeu sua dúvida, por favor, clique no Gostei e marque o Tópico como RESOLVIDO.

Att.

André Arruda
Avatar do usuário
xlarruda
Membro
Membro
 
Mensagens: 687
Registrado em: Qua Out 04, 2017 9:41 am
Localização: Cuiabá-MT
Has thanked: 92 times
Have thanks: 273 times

Re: Dúvida com PROCV

Mensagempor xlarruda » Qui Mar 15, 2018 5:35 pm

Blz EdsonBr funcionou perfeitamente..

Apenas um detalhe que não resolvi:

Mesmo a planilha base estando ReadyOnly:= True (apenas leitura), ainda sim ao fechar qualquer arquivo aparece a mensagem:

"Deseja salvar as alterações feitas em "banco de dados"? sim / nao

Eu gostaria que ele fechasse direto, sem essa mensagem, até porque não fiz alteração nenhuma, apenas consultei os dados dela através da minha função.

Se conseguir achar uma solução para isso ficarei muito grato. Abrç!

o código:

Código: Selecionar todos
Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Workbooks("Planilha2.xlsx").Close
End Sub
___________________________________________________________________________________________
Se a resposta atendeu sua dúvida, por favor, clique no Gostei e marque o Tópico como RESOLVIDO.

Att.

André Arruda
Avatar do usuário
xlarruda
Membro
Membro
 
Mensagens: 687
Registrado em: Qua Out 04, 2017 9:41 am
Localização: Cuiabá-MT
Has thanked: 92 times
Have thanks: 273 times

Próximo

Voltar para VBA & Macros

Quem está online

Usuários navegando neste fórum: Google Adsense [Bot] e 2 visitantes