Conteúdo
A comunicação é um recurso muito útil no Excel. Afinal, muitas vezes os usuários precisam usar informações de outros arquivos. Mas em algumas situações, eles podem fazer mais mal do que bem. Afinal, por exemplo, se você enviar esses arquivos por correio, os links não funcionarão. Hoje falaremos com mais detalhes sobre o que fazer para evitar tal problema.
O que são relacionamentos no Excel
Os relacionamentos no Excel são frequentemente usados em conjunto com funções como VPRpara obter informações de outra pasta de trabalho. Pode assumir a forma de um link especial que contém o endereço não apenas da célula, mas também do livro no qual os dados estão localizados. Como resultado, esse link se parece com isso: =PROCV(A2;'[Vendas 2018.xlsx]Relatório'!$A:$F;4;0). Ou, para uma representação mais simples, represente o endereço da seguinte forma: ='[Vendas 2018.xlsx]Relatório'!$A1. Vamos analisar cada um dos elementos de link desse tipo:
- [Vendas 2018.xlsx]. Este fragmento contém um link para o arquivo do qual você deseja obter informações. Também é chamado de fonte.
- Fotos. Usamos o seguinte nome, mas este não é o nome que deveria ser. Este bloco contém o nome da planilha na qual você precisa encontrar informações.
- $A:$F e $A1 – o endereço de uma célula ou intervalo contendo dados contidos neste documento.
Na verdade, o processo de criação de um link para um documento externo é chamado de vinculação. Após registrarmos o endereço da célula contida em outro arquivo, o conteúdo da aba “Dados” muda. Ou seja, o botão “Alterar conexões” fica ativo, com a ajuda do qual o usuário pode editar as conexões existentes.
A essência do problema
Como regra, não surgem dificuldades adicionais para usar os links. Mesmo que surja uma situação em que as células mudem, todos os links são atualizados automaticamente. Mas se você já renomear a própria pasta de trabalho ou movê-la para um endereço diferente, o Excel se tornará impotente. Portanto, ele produz a seguinte mensagem.
Aqui, o usuário tem duas opções possíveis de como agir nessa situação. Ele pode clicar em “Continuar” e as alterações não serão atualizadas, ou pode clicar no botão “Alterar associações”, com o qual poderá atualizá-las manualmente. Após clicarmos neste botão, aparecerá uma janela adicional na qual será possível alterar os links, indicando onde está localizado o arquivo correto no momento e como se chama.
Além disso, você pode editar links através do botão correspondente localizado na aba “Dados”. O usuário também pode descobrir que a conexão foi interrompida pelo erro #LINK, que aparece quando o Excel não consegue acessar informações localizadas em um endereço específico devido ao fato do próprio endereço ser inválido.
Como desvincular no excel
Um dos métodos mais simples para resolver a situação descrita acima, caso você não consiga atualizar o local do arquivo vinculado, é excluir o próprio link. Isso é especialmente fácil de fazer se o documento contiver apenas um link. Para fazer isso, você deve executar a seguinte sequência de etapas:
- Abra o menu “Dados”.
- Encontramos a seção “Conexões” e lá – a opção “Alterar conexões”.
- Depois disso, clique em “Desvincular”.
Se você pretende enviar este livro para outra pessoa, é altamente recomendável que você o faça com antecedência. Afinal, após a exclusão dos links, todos os valores que estiverem contidos em outro documento serão carregados automaticamente no arquivo, utilizados em fórmulas, e ao invés do endereço da célula, as informações nas células correspondentes serão simplesmente transformadas em valores .
Como desvincular todos os livros
Mas se o número de links se tornar muito grande, excluí-los manualmente pode levar muito tempo. Para resolver esse problema de uma só vez, você pode usar uma macro especial. Está no complemento VBA-Excel. Você precisa ativá-lo e ir para a guia com o mesmo nome. Haverá uma seção "Links", na qual precisamos clicar no botão "Quebrar todos os links".
Código VBA
Se não for possível ativar este add-on, você mesmo pode criar uma macro. Para fazer isso, abra o editor Visual Basic pressionando as teclas Alt + F11 e escreva as seguintes linhas no campo de entrada de código.
Sub DesvincularWorkBooks()
Escurecer WbLinks
Escuro e tão longo
Selecione Case MsgBox(“Todas as referências a outros livros serão removidas deste arquivo, e as fórmulas referentes a outros livros serão substituídas por valores.” & vbCrLf & “Tem certeza de que deseja continuar?”, 36, “Desvincular?” )
Caso 7′ Não
Exit Sub
End Select
WbLinks = ActiveWorkbook.LinkSources(Tipo:=xlLinkTypeExcelLinks)
Se não estiver vazio (WbLinks), então
Para i = 1 Para UBound(WbLinks)
ActiveWorkbook.BreakLink Nome:=WbLinks(i), Tipo:=xlLinkTypeExcelLinks
Próximo
outro
MsgBox “Não há links para outros livros neste arquivo.”, 64, “Links para outros livros”
Se acabar
End Sub
Como desempate apenas no intervalo selecionado
De vez em quando, o número de links é muito grande, e o usuário tem medo de que, após excluir um deles, não seja possível devolver tudo de volta se algum for supérfluo. Mas este é um problema que é fácil de evitar. Para fazer isso, você precisa selecionar o intervalo no qual excluir links e excluí-los. Para fazer isso, você deve executar a seguinte sequência de ações:
- Selecione o conjunto de dados que precisa ser modificado.
- Instale o complemento VBA-Excel e vá para a guia apropriada.
- Em seguida, encontramos o menu “Links” e clicamos no botão “Quebrar links nos intervalos selecionados”.
Depois disso, todos os links no conjunto de células selecionado serão excluídos.
O que fazer se os laços não forem quebrados
Todos os itens acima soam bem, mas na prática sempre há algumas nuances. Por exemplo, pode haver uma situação em que os laços não são quebrados. Nesse caso, ainda aparece uma caixa de diálogo informando que não é possível atualizar os links automaticamente. O que fazer nessa situação?
- Primeiro, você precisa verificar se alguma informação está contida nos intervalos nomeados. Para fazer isso, pressione a combinação de teclas Ctrl + F3 ou abra a guia “Fórmulas” – “Gerenciador de nomes”. Se o nome do arquivo estiver cheio, basta editá-lo ou removê-lo completamente. Antes de excluir intervalos nomeados, você precisa copiar o arquivo para algum outro local para poder retornar à versão original se as etapas erradas forem executadas.
- Se você não conseguir resolver o problema removendo nomes, verifique a formatação condicional. As células em outra tabela podem ser referenciadas em regras de formatação condicional. Para fazer isso, encontre o item correspondente na guia "Início" e clique no botão "Gerenciamento de arquivos".
Normalmente, o Excel não oferece a capacidade de fornecer o endereço de outras pastas de trabalho na formatação condicional, mas você o faz se fizer referência a um intervalo nomeado com uma referência a outro arquivo. Normalmente, mesmo após a remoção do link, o link permanece. Não há problema em remover esse link, porque o link na verdade não está funcionando. Portanto, nada de ruim acontecerá se você removê-lo.
Você também pode usar a função “Data Check” para descobrir se existem links desnecessários. Os links geralmente permanecem se o tipo de validação de dados “Lista” for usado. Mas o que fazer se houver muitas células? É realmente necessário verificar cada um deles sequencialmente? Claro que não. Afinal, vai demorar muito. Portanto, você precisa usar um código especial para salvá-lo significativamente.
Opção explícita
'—————————————————————————————
' Autor: The_Prist(Shcherbakov Dmitry)
' Desenvolvimento profissional de aplicativos para MS Office de qualquer complexidade
' Realização de treinamentos em MS Excel
' https://www.excel-vba.ru
' [e-mail protegido]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' Propósito:
'—————————————————————————————
Sub FindErrLink()
'precisamos olhar no link Data -Change links para o arquivo de origem
'e coloque as palavras-chave aqui em minúsculas (parte do nome do arquivo)
'asterisco apenas substitui qualquer número de caracteres para que você não precise se preocupar com o nome exato
Const sToFndLink$ = “*vendas 2018*”
Dim rr como intervalo, rc como intervalo, rres como intervalo, s$
'define todas as células com validação de dados
On Error Resume Next
Definir rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Se rr não é nada, então
MsgBox “Não há células com validação de dados na planilha ativa”, vbInformation, “www.excel-vba.ru”
Exit Sub
Se acabar
Em erro GoTo 0
'verifica cada célula para links
Para cada rc em rr
'apenas no caso, nós pulamos erros - isso também pode acontecer
'mas nossas conexões devem ser sem eles e eles definitivamente serão encontrados
s = «»
On Error Resume Next
s = rc.Validação.Formula1
Em erro GoTo 0
'found - coletamos tudo em um intervalo separado
Se LCase(s) gostarem de sToFndLink Então
Se rres não é nada, então
Definir res = rc
outro
Definir rres = União(rc, rres)
Se acabar
Se acabar
Próximo
'se houver uma conexão, selecione todas as células com tais verificações de dados
Se não rres não é nada então
rres.Selecionar
' rres.Interior.Color = vbRed 'se você quiser destacar com cor
Se acabar
End Sub
É necessário fazer um módulo padrão no editor de macros, e então inserir esse texto lá. Depois disso, chame a janela de macro usando a combinação de teclas Alt + F8 e, em seguida, selecione nossa macro e clique no botão “Executar”. Há algumas coisas a serem lembradas ao usar este código:
- Antes de pesquisar um link que não seja mais relevante, você deve primeiro determinar a aparência do link por meio do qual ele foi criado. Para fazer isso, vá para o menu “Dados” e encontre o item “Alterar links” lá. Depois disso, você precisa ver o nome do arquivo e especificá-lo entre aspas. Por exemplo, assim: Const sToFndLink$ = “*vendas 2018*”
- É possível escrever o nome não por completo, mas simplesmente substituir caracteres desnecessários por um asterisco. E entre aspas, escreva o nome do arquivo em letras pequenas. Nesse caso, o Excel encontrará todos os arquivos que contenham essa string no final.
- Este código só pode verificar links na planilha que está ativa no momento.
- Com esta macro, você só pode selecionar as células que encontrou. Você tem que excluir tudo manualmente. Isso é uma vantagem, porque você pode verificar tudo novamente.
- Você também pode destacar as células em uma cor especial. Para fazer isso, remova o apóstrofo antes desta linha. rres.Interior.Color = vbVermelho
Normalmente, depois de concluir as etapas descritas nas instruções acima, não deve haver mais conexões desnecessárias. Mas se houver alguns deles no documento e você não conseguir removê-los por um motivo ou outro (um exemplo típico é a segurança dos dados em uma planilha), você poderá usar uma sequência diferente de ações. Esta instrução é válida apenas para as versões 2007 e superiores.
- Criamos uma cópia de backup do documento.
- Abra este documento usando o arquivador. Você pode usar qualquer um que suporte o formato ZIP, mas o WinRar também funcionará, assim como o embutido no Windows.
- No arquivo que aparece, você precisa encontrar a pasta xl e abrir externalLinks.
- Esta pasta contém todos os links externos, cada um dos quais corresponde a um arquivo no formato externalLink1.xml. Todos eles são apenas numerados e, portanto, o usuário não tem a oportunidade de entender que tipo de conexão é essa. Para entender que tipo de conexão, você precisa abrir a pasta _rels e vê-la lá.
- Depois disso, removemos todos os links ou links específicos, com base no que aprendemos no arquivo externalLinkX.xml.rels.
- Depois disso, abrimos nosso arquivo usando o Excel. Haverá informações sobre um erro como "Erro em parte do conteúdo do livro". Nós damos consentimento. Depois disso, outra caixa de diálogo aparecerá. Nós o fechamos.
Depois disso, todos os links devem ser removidos.