Existe uma ferramenta / técnica para atualizar vários hiperlinks internos dos arquivos do MS Excel para apontar para um novo servidor?

Question

Estamos consolidando vários compartilhamentos de arquivos em um único e há milhares de arquivos do MS Excel (sem mencionar o Access e o Word) que possuem uma variedade de links dentro de outras planilhas. Alguns deles são referenciados em relação à localização do arquivo e outros contêm o caminho completo da pasta de trabalho referenciada (por exemplo, I: /PUBLIC/username/file.xls).

Verificamos nossos servidores com alguns programas locais para detectar os arquivos com links ativos, mas gostaríamos de evitar escrever nosso próprio código para fazer uma atualização em massa para alterar os links, se possível.

Existe alguma ferramenta ou técnica que alguém usou para migrar um grande número de arquivos como este? Meu googling encontrado ExcelPipe ( link ), mas o seu site e minha incapacidade de encontrar quaisquer comentários do produto não me deixa repleto de confiança.

    

Answer 1

Existem muitas ferramentas disponíveis:

• O IceTeaReplacer (parece ser freeware - um usuário observa em outro post "parece não funcionar em arquivos .xlsm.")
• PowerGREP , uma ótima ferramenta.
• LinkTek
• Encontre Links Quebrados para o Excel
• ReplaceMagic Excel Version

E muitos outros. Faça um backup e teste-o.

Eu escrevi um script VBA que lista todos os arquivos e seus links em uma determinada pasta, pode ser útil para uma tarefa como essa.

    

Answer 2

Passei muito tempo com isso, mas acho que finalmente consegui uma solução :)

Faça o download do script do PowerShell e execute-o. Ele pede uma pasta. Escolha uma pasta com quantos arquivos do Excel você quiser. Em seguida, você precisa inserir sua string de pesquisa antiga e sua nova string de substituição. Em seguida, ele substituirá todos os hiperlinks em todos os arquivos selecionados usando sua pesquisa & substitua strings.

→

Como funciona em pouco tempo

1. Solicitar pasta
2. Solicitar uma string de pesquisa antiga
3. Solicitar nova string de substituição
4. Faz um loop por todos os arquivos XLS, XLSX, XLSB ou qualquer que seja a expressão regex .*XLS? corresponde
5. Fazer um loop em todas as folhas da pasta de trabalho atual
6. Fazer um loop em todos os hiperlinks da planilha atual
7. Tente substituir o endereço do hiperlink (não diferencia maiúsculas de minúsculas)
8. Salve a pasta de trabalho atual
9. Fechar tudo se terminar

Código

$object = New-Object -comObject Shell.Application  
$folder = $object.BrowseForFolder(0, 'Select the folder', 0)    
$oldname = Read-Host 'Enter your old string to search for'
$newname = Read-Host 'Enter your new string'

if (!$folder -or !$oldname -or !$newname) {exit} 

$excel = New-Object -comObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

foreach ($file in Get-ChildItem -literalPath $folder.self.Path*.xls?) {
    $workbook = $excel.Workbooks.Open($file.name)    
    foreach ($sheet in $workbook.Sheets) {                    

        foreach ($cell in $sheet.Hyperlinks) {
            $cell.Address = $cell.Address -ireplace $oldname, $newname                        
        }             
    }
    $workbook.Save()
    $workbook.Close()
}
$excel.quit()    

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 

Por favor, teste-o