Como fazer o Excel usar caminhos relativos em links de pastas de trabalho externas

Question

Em nossa empresa, usamos várias pastas de trabalho do Excel vinculadas a outras pastas de trabalho do Excel.

Com nosso serviço, tudo bem, pois todos acessam uma unidade de rede compartilhada e os caminhos de todos os arquivos vinculados permanecem os mesmos.

No entanto, agora desejamos usar serviços de armazenamento em nuvem, por exemplo, Dropbox, mas enfrentamos problemas. Com todos os serviços que testamos, o caminho dos arquivos é diferente para cada máquina. Por exemplo, a mesma pasta em duas máquinas diferentes teria locais de arquivo conforme abaixo:

Máquina 1 Dropbox = C:[máquina 1]\Dropbox\Pasta Excel\Arquivo Excel

Máquina 2 Dropbox = C:[máquina 2]\Dropbox\Pasta Excel\Arquivo Excel

Quando isso acontece, você deve escolher a fonte dos links toda vez que abrir o arquivo em uma máquina diferente. Se você criar um arquivo com links para outros arquivos na máquina 1 e depois passar para a máquina 2, a máquina 2 não saberá onde os arquivos com 'máquina 1' no caminho estão armazenados. Esta é uma grande barreira para usarmos esse tipo de armazenamento, pois cada usuário teria que alterar cada arquivo cada vez que o abrisse.

Torna-se particularmente grave ao usar o PowerPivot e vincular a outros arquivos no modelo de dados, pois todas as fontes de dados do modelo de dados também precisam ser atualizadas.

Eu entendo por que isso acontece, mas existe uma maneira de contornar isso e fazer com que os arquivos sejam vinculados de forma que os links sejam relativos e não absolutos.

Estamos usando uma combinação do Office 2013 e 2016

Answer 1

Em determinadas circunstâncias, os links externos criados no Excel usarão caminhos relativos em vez de caminhos absolutos. Isso depende da localização das planilhas. A maneira mais fácil é armazenar os documentos Excel na mesma pasta.

Consulte este URL para obter mais informações (trecho abaixo):https://support.microsoft.com/en-us/kb/328440

• Se o arquivo vinculado e o arquivo de dados de origem não estiverem na mesma unidade, a letra da unidade será armazenada com um caminho para o arquivo e o nome do arquivo.
• Se o arquivo vinculado e o arquivo de dados de origem estiverem na mesma pasta, apenas o nome do arquivo será armazenado.
• Se o arquivo de dados de origem estiver localizado em uma pasta aninhada na mesma pasta raiz do arquivo vinculado, uma propriedade será armazenada para indicar a pasta raiz. Todas as partes do caminho compartilhadas não são armazenadas. Por exemplo, se o arquivo vinculado C:\Mydir\Linked.xls depender de C:\Mydir\Files\Source.xls, a única parte do caminho armazenada será \Files\Source.xls.
• Se o arquivo de dados de origem estiver uma pasta abaixo do arquivo vinculado, uma propriedade será armazenada para indicar isso. Por exemplo, o arquivo vinculado é C:\Mydir\Files\Myfile\Linked.xls e o arquivo de dados de origem é C:\Mydir\Files\Source.xls. O Excel armazena apenas \MyDir\Files\ .. \Source.xls. Nota Isso permite que um link seja mantido quando o arquivo vinculado é copiado para uma subpasta adicional da pasta onde o arquivo de origem está localizado. Por exemplo, o arquivo vinculado é C:\Mydir\Files\Myfiles1\Linked.xls e o arquivo de dados de origem é C:\Mydir\Files\Source.xls, o arquivo vinculado, Linked.xls, é copiado da pasta C:\Mydir\Files\Myfiles1 para uma pasta chamada C:\Mydir\Files\Myfiles2, e o link para C:\Mydir\Files\Source.xls é mantido.

Answer 2

Não sei se isso irá ajudá-lo na sua situação, mas ninguém mencionou ainda que existe uma configuração para dizer ao Excel para usar caminhos relativos versus absolutos. E se você realmente quiser usar caminhos relativos, infelizmente, a Microsoft tentará ser útil e os tornará relativos a esse usuário, então os links só funcionarão se você conseguir que os destinos sejam exatamente o mesmo caminho relativo para cada do utilizador. Essa foi a sugestão rejeitada de outra pessoa.

Answer 3

Portanto, é possível ter um caminho relativo em vez de um caminho absoluto, para que suas fontes de dados continuem funcionando desde que você mantenha os arquivos da fonte de dados no mesmo local da planilha que os utiliza.

A interface do usuário do Excel não parece ter como alterar o caminho.

Mas você pode editar o XLSX de uma maneira diferente da UI. Se você renomear seu arquivo XLSX para .zip, poderá extraí-lo "como se" fosse um arquivo zip (na verdade, a pasta de trabalho do Excel é um arquivo compactado). Em seguida, no arquivo zip extraído, abra o diretório xl e nele o arquivo "connections.xml"

Lá você encontrará o "sourceFile", você pode alterar isso e fazer com que ele se refira apenas ao nome do arquivo.

Em seguida, mova o "connections.xml" de volta para o arquivo Excel "zipado" e renomeie-o de volta para XLSX.

Observe que apenas compactar o diretório de volta e renomeá-lo para XLSX não funcionará, porque qualquer comando zip pode usar uma variação de algoritmos e opções de compactação que nem sempre serão compatíveis com o Microsoft Excel ...

Portanto, o melhor é mover o "connections.xml" de volta para o arquivo zip já existente (ou melhor ainda, editar o arquivo diretamente no arquivo zip, se o seu manipulador de arquivo zip permitir).

O arquivo ExcelFile.xlsx\xl\connections.xml será parecido com isto:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<connections xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="xr16" xmlns:xr16="http://schemas.microsoft.com/office/spreadsheetml/2017/revision16"><connection id="1" xr16:uid="{F9606253-9C57-4B65-839A-8DEF5AAEA9F7}" keepAlive="1" name="ThisWorkbookDataModel" description="Data Model" type="5" refreshedVersion="6" minRefreshableVersion="5" background="1"><dbPr connection="Data Model Connection" command="Model" commandType="1"/><olapPr sendLocale="1" rowDrillCount="1000"/><extLst><ext uri="{DE250136-89BD-433C-8126-D09CA5730AF9}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"><x15:connection id="" model="1"/></ext></extLst></connection><connection id="2" xr16:uid="{7FE915B8-2095-40EC-B6DC-A589A5A2D08D}" name="TimeTrackingUserEntryLog" type="103" refreshedVersion="6" minRefreshableVersion="5" refreshOnLoad="1" saveData="1"><extLst><ext uri="{DE250136-89BD-433C-8126-D09CA5730AF9}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"><x15:connection id="TimeTrackingUserEntryLog" autoDelete="1"><x15:textPr prompt="0" sourceFile="C:\Dropbox\Wonderful\LifeorLive\TimeTrackingUserEntryLog.csv" tab="0" comma="1"><textFields count="5"><textField type="YMD"/><textField/><textField type="text"/><textField type="text"/><textField type="text"/></textFields></x15:textPr><x15:modelTextPr headers="1"/></x15:connection></ext></extLst></connection></connections>

onde C:\Dropbox etc é o caminho para minha fonte de dados, que é um arquivo csv.

Você pode remover o caminho inteiro e deixar "datasourcefilename.csv" lá.

Espero que ajude! ^^

Answer 4

A solução de trabalho pode ser encontrada aqui:

https://excel.tv/how-to-create-a-relative-file-path-in-power-query/