Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

We are consolidating numerous file shares into one and there are thousands of MS Excel (not to mention Access and Word) files that have a variety of links within to other sheets. Some of them are referenced relative to the file's location and others have contain the entire path of the referenced workbook (i.e. I:/PUBLIC/username/file.xls).

We have scanned our servers with some homegrown programs to detect the files with working links, but would like to avoid writing our own code to do a bulk update to change the links if possible.

Are there any tools or techniques anyone has used to migrate large numbers of files like this? My googling found ExcelPipe (http://www.datamystic.com/excelpipe.html) but their website and my inability to find any reviews of the product does not leave me brimming with confidence.

share|improve this question
    
I can sympathize with the pain that must be associated with trying to do something like this, but I think the most direct route is to hammer out a VBA or C# program to do it... –  allquixotic Nov 20 '12 at 20:49
    
That's what signs are thus far pointing to... we've found some pretty solid Java libraries that have got us to this point. –  mwersch Nov 20 '12 at 20:56
    
You're referring to POI, right? Yeah. Probably a lot faster than using Excel Automation, though be wary of format mismatches when modifying and saving existing documents... POI doesn't always save it back out 100% the same as it imported it... –  allquixotic Nov 20 '12 at 21:30
    
Yup, jexcelapi and POI have been tools of choice so far. Those are the precise kinds of concerns we have in tackling the updates through our own hackery. –  mwersch Nov 20 '12 at 22:12
    
Well, you could do it with the Excel Automation COM API, but it'd be about 20 times slower (take 20 times as long to execute), but the tradeoff is guaranteed 100% file format integrity by using Excel itself to do the transformations. –  allquixotic Nov 21 '12 at 19:42

2 Answers 2

There are many tools available:

And many others. Do a backup and test it out.

I wrote a VBA script that lists all files and its links in a given folder, might be useful for a task like this.

share|improve this answer

I spent far too much time with this, but I think I finally got a solution :)

Download the Powershell script and run it. It asks you for a folder. Choose a folder with as many Excel files in it as you want. Next, you have to enter your old search string and your new replacement string. It will then replace all hyperlinks in all selected files using your search & replace strings.

enter image description hereenter image description here

How it works in short

  1. Ask for folder
  2. Ask for old search string
  3. Ask for new replacement string
  4. Loop through every XLS, XLSX, XLSB file or whatever the regex expression .*XLS? matches
  5. Loop through every sheet in the current workbook
  6. Loop through every hyperlink in the current sheet
  7. Try to replace the hyperlink address (case insensitive)
  8. Save the current workbook
  9. Close everything if finished

Code

$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() 

Please test it

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.