Uso da Função PROCV para retornar o local do hiperlink de uma célula.

Question

Amigos,

O desafio é o seguinte: estou usando uma pasta de trabalho do Excel 2016 para me auxiliar em meus planos de estudo. Acontece que, numa planilha chamada "Quadro Disciplinas", eu tenho todos os dados relativos aos assuntos, relacionados via HIPERLINK com arquivos PDF (aulas), presentes em meu PC.

Esse é um exemplo do meu Quadro Disciplinas:

Assunto por disciplina	Link do arquivo em PDF (VIA HIPERLINK)
ACENTUAÇÃO	AULA_ACENTUAÇÃO (AQUI EU CONSIGO ACESSAR)

Nesse exemplo que coloquei, consigo acessar os arquivos via hiperlink que fui inserindo manualmente. O problema é que em outra planilha da pasta de trabalho existe o meu plano de aulas, no qual vou inserindo assuntos diversos, por dia de estudo. Dai, quero acessar as aulas diretamente pelo plano de aulas, sem ter que ir no quadro disciplinas para procurar a aula correspondente.

Daí, tive a ideia de colocar um PROCV para que esta fórmula me trouxesse o link do arquivo presente no quadro disciplinas e, assim, facilitar o acesso aos PDF's das aulas. Já tentei combinar a fórmula Hiperlink com o procv, o Excel me retoma o valor, mas quando clico no hiperlink resultante da fórmula o Excel não consegue acessar o link do arquivo e, consequentemente, não o abre.

Se não pela função HIPERLINK E PROCV, existe outra forma de chegar ao resultado pretendido?

Desde já, agradeço a atenção.

Answer 1

Então Ailton,

Supondo que os hiperlinks foram colocados como um objeto na célula e não com a função HIPERLINK, eu vou propor uma alternativa para você conseguir trazer o hiperlink funcional para qualquer célula.

1º) Pressione ALT+F11 para abrir o editor do Visual Basic, e insira um novo módulo no projeto da pasta de trabalho.

2º) No módulo inserido, copie e cole o seguinte código abaixo para construção de uma função definida pelo usuário (UDF) chamada LINK_HIPERLINK, que servirá para conseguirmos extrair o endereço de um hiperlink em uma célula.

Public Function LINK_HIPERLINK(rng As Range) As String

LINK_HIPERLINK = rng.Hyperlinks.Item(1).Address

End Function

3º) Pode fechar a janela do Visual Basic e então na planilha você desenvolverá a fórmula de busca envolvida com esta UDF LINK_HIPERLINK, porém você não pode utilizar o PROCV, mas deve utilizar o DESLOC+CORRESP ou ÍNDICE+CORRESP. Veja um exemplo na imagem:

Na imagem, tanto o HIPERLINK da célula B2 como o HIPERLINK criado por fórmula na célula F6 abrirão o arquivo pdf referenciado.

4º) Você deve salvar o arquivo habilitado para macro (.XLSM) para manter a funcionalidade da UDF criada.

Espero que te ajude.

Abraços!

Answer 2

Muito bom, Rafael!!

Como faço com ProcH?

Answer 3

Então Ailton,

Supondo que os hiperlinks foram colocados como um objeto na célula e não com a função HIPERLINK, eu vou propor uma alternativa para você conseguir trazer o hiperlink funcional para qualquer célula.

1º) Pressione ALT+F11 para abrir o editor do Visual Basic, e insira um novo módulo no projeto da pasta de trabalho.

2º) No módulo inserido, copie e cole o seguinte código abaixo para construção de uma função definida pelo usuário (UDF) chamada LINK_HIPERLINK, que servirá para conseguirmos extrair o endereço de um hiperlink em uma célula.

Public Function LINK_HIPERLINK(rng As Range) As String

LINK_HIPERLINK = rng.Hyperlinks.Item(1).Address

End Function

3º) Pode fechar a janela do Visual Basic e então na planilha você desenvolverá a fórmula de busca envolvida com esta UDF LINK_HIPERLINK, porém você não pode utilizar o PROCV, mas deve utilizar o DESLOC+CORRESP ou ÍNDICE+CORRESP. Veja um exemplo na imagem:

Na imagem, tanto o HIPERLINK da célula B2 como o HIPERLINK criado por fórmula na célula F6 abrirão o arquivo pdf referenciado.

4º) Você deve salvar o arquivo habilitado para macro (.XLSM) para manter a funcionalidade da UDF criada.

Espero que te ajude.

Abraços!

Olá, Rafael.

Entrei em contato com você via endereço eletrônico fornecido em seu blog.

Grato pela ajuda.

Answer 4

Dois pontos importantes para poder estudar uma solução:

1. Os hiperlinks foram adicionados por meio da função HIPERLINK ou através do botão "Inserir Hiperlink" ?
2. Você colocou um texto para exibição na célula, ou deixou o próprio endereço do pdf na célula?