☰

Adicionar ou substituir texto em hiperlinks

tag-icon • tag-icon microsoft-excel macros find-and-replace vbscript
Adicionar ou substituir texto em hiperlinks

Estou muito confuso depois de tentar explorar como isso pode ser feito. É muito simples, mas ainda não consigo encontrar uma solução.

O que eu gostaria de saber é o seguinte. Tenho três documentos separados, todos vinculados até certo ponto; os dados são inseridos a partir de um documento (1. entrada mestre) e resumidos nas três planilhas.

Cada documento é apresentado de forma idêntica, os únicos fatores variáveis ​​são as datas e para a entrada mestre, temos valores numéricos.

Em forma de ilustração fica assim:

a. 1 Entrada mestre --> (alimenta em) 2,3 b. 2 --> (alimenta) 3 e 4 c. 3 -> (alimenta) 4

O que eu gostaria de saber é que nos documentos 2,3 e 4... os dados mudam a cada ano. Tenho muitos 'hiperlinks' da planilha como exemplo:

2015 =" \Receita Diária\2015[Receita 2015.xls]Resumo do Ano'!$C$160"

2016 =" \Receita Diária\2016[Receita 2016.xls]Resumo do Ano'!$C$160"

2017 =" \Receita diária\2017[Receita 2017.xls]Resumo do ano'!$C$160"

Do exposto acima, a única variável que preciso alterar é o ano, existe algum código/macro etc. que eu possa usar para substituir oANOdentro do hiperlink. Considere em mente que o caminho do arquivo serásemprepermanece o mesmo.

Estou plenamente consciente doencontrar e substituirfunção, no entanto, desejo torná-la amigável e fácil.

Por favor me ajude, obrigado!

Responder1

Em primeiro lugar, você continua falando sobre hiperlinks. Acho que você quer dizer fórmulas que fazem referência a outros arquivos. Esses não são hiperlinks. Aqui está a solução se o que você realmente quis dizer foram hiperlinks:

Para responder à sua pergunta real: Sim, existe uma macro para isso. Claro. Acho que o que você precisa saber é o Worksheet.Hyperlinksobjeto. Você pode percorrer cada hiperlink em cada planilha e substituir o endereço pelo que desejar. Aqui está um exemplo de trecho:

Sub ChangeHyperlinks()
    Dim ws As Worksheet
    Dim hyp As Hyperlink
    For Each hyp In ws.Hyperlinks
        hyp.Address = Replace(hyp.Address, "2015", "2016")
    Next
End Sub

Você terá que modificá-lo se não quiser mudartodohiperlink adicionando instruções condicionais como If InStr(1, hyp.Address, "\Daily Revenue\2015") Then. Você pode usar o Workbook_Openevento para ser executado sempre que o arquivo for aberto, pegar o ano certo com base no nome do arquivo e, em seguida, executar a substituição dos hiperlinks. Sim, isso iria disparartodomomento em que o arquivo é aberto, mesmo que já esteja correto. Dependendo de quantos hiperlinks você tem, isso pode ser bom ou pode paralisar tudo.

E aqui está um trecho de código se o que você realmente quis dizer foram fórmulas. Novamente, você terá que adaptá-lo mais às suas necessidades, mas isso deve ajudá-lo a começar.

Private Sub Workbook_Open()
    Dim i As Integer
    Dim srcArray As Variant
    srcArray = ThisWorkbook.LinkSources(xlLinkTypeExcelLinks)
    For i = 1 To UBound(srcArray)
        Debug.Print srcArray(i)
        ThisWorkbook.ChangeLink srcArray(i), Replace(srcArray(i), "2015", "2016")
    Next
End Sub

Além disso, se você quis dizer fórmulas, sua pergunta é uma duplicata deStackOverflow.

Responder2

Existem algumas ambigüidades em sua pergunta. Esta resposta pressupõe que você esteja falando sobre referências de células em outras pastas de trabalho, conforme ilustrado em seus exemplos, em vez de hiperlinks, e que a tarefa é configurar as referências ajustáveis ​​por ano, em vez de reescrever todas as fórmulas a cada ano.

Primeiro, você precisa de uma referência de ano em algum lugar. Não está claro quando ou de que maneira você deseja que o ano mude (automaticamente em 1º de janeiro de cada ano? Em uma data de sua escolha? Você deseja alterar o ano a qualquer momento?). Então, basearei isso em uma solução genérica. Escolha uma célula para conter a referência do ano. No meu exemplo, usarei a célula Z1da planilha que contém as referências de células no seu exemplo. Se você quiser alterar o ano à vontade ou em uma data de sua escolha, basta inserir Z1o ano de quatro dígitos. Se você quiser que o ano mude automaticamente em 1º de janeiro, use =YEAR(TODAY()).

Então, em vez de codificar o ano em cada fórmula, concatene uma referência Z1e use a função INDIRETA para converter a sequência de texto em uma referência de célula. Por exemplo:

          '\Daily Revenue\2015[Revenue 2015.xls]Year Summary'!$C$160   becomes
INDIRECT("'\Daily Revenue\"&Z1&"[Revenue "&Z1&".xls]Year Summary'!$C$160")

Com uma planilha existente, você precisaria fazer uma alteração única para modificar as referências das células. Depois disso, você poderá alterar o ano sempre que quiser, alterando a Z1entrada.

informação relacionada