Adicionando ou substituindo texto em hiperlinks

Question

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

O que eu gostaria de saber é o seguinte. Eu tenho três documentos separados, todos ligados em algum grau; os dados são inseridos de um documento (1. entrada mestre) e resumidos nas três planilhas.

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

Em um formulário de ilustração, é assim:

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

O que eu gostaria de saber é, nos documentos 2, 3 e 4 .... os dados mudam a cada ano. Eu tenho muitos 'hyperlinks' 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 acima, a única variável que eu preciso mudar é o ano, existe algum código / macro, etc. que eu possa usar para substituir o YEAR dentro do hyperlink. Lembre-se de que o caminho do arquivo sempre permanecerá o mesmo.

Estou totalmente ciente da função localizar e substituir , mas desejo tornar esse usuário amigável e fácil.

Por favor me ajude, obrigado!

    

Answer 1

Antes de mais nada, você continua falando sobre hiperlinks. Eu acho que o que você quer dizer são fórmulas que fazem referência a outros arquivos. Aqueles não são hiperlinks. Aqui está a solução se o que você realmente quis dizer foi hiperlinks:

Para responder à sua pergunta real: Sim, há uma macro para isso. Certo. Eu acho que o que você precisa saber é o objeto Worksheet.Hyperlinks . Você pode percorrer cada hiperlink em cada planilha e substituir o endereço com o que quiser. 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 alterar todos os hyperlinks adicionando instruções condicionais como If InStr(1, hyp.Address, "\Daily Revenue15") Then . Você pode usar o evento Workbook_Open para ser executado toda vez que o arquivo for aberto, capturar o ano certo com base no nome do arquivo e, em seguida, executar a substituição dos hiperlinks. Sim, isso acionaria a cada hora em que o arquivo é aberto, mesmo que já esteja correto. Dependendo de quantos hiperlinks você tiver, isso pode ser bom ou pode acabar com tudo.

E aqui está um trecho de código se o que você realmente quis dizer foi fórmulas. Novamente, você terá que adaptá-lo mais às suas necessidades, mas isso deve 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 cópia de StackOverflow .

    

Answer 2

Existem algumas ambiguidades na sua pergunta. Essa 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 como ajustáveis por ano, em vez de reescrever todas as fórmulas a cada ano. / p>

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ê quer a capacidade de alterar o ano a qualquer momento?). Então, vou basear isso em uma solução genérica. Escolha uma célula para manter a referência do ano. No meu exemplo, usarei a célula Z1 da planilha que contém as referências de célula em seu exemplo. Se você quiser alterar o ano à vontade ou em uma data de sua escolha, basta digitar Z1 no ano de quatro dígitos. Se você quiser que o ano mude automaticamente em 1º de janeiro, use =YEAR(TODAY()) .

Em seguida, em vez de codificar o ano em cada fórmula, concatene uma referência a Z1 e use a função INDIRECT para converter a cadeia de texto em uma referência de célula. Por exemplo:

          '\Daily Revenue15[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 de célula. Depois disso, você pode alterar o ano sempre que quiser alterando a entrada Z1 .