Update-ExcelHyperlinksAddress (A script to update spreadsheets that link to files that get moved via COM)

This is a piece of crap script that I am mad I needed to make. Why? because I hate how slow COM is. Actually, this script only takes about .5-8s per file (which is real nice compared to my dealings with COM in the past). I’m running on 7000 files in this folder for our finance department currently.

This script is used when you have excel files that link to documents on your network, but you need to move those documents to a different server (cause you don’t have DFSR namespaces setup) and update the excel files to point to the new location. It requires that you have excel installed on whatever machine runs this script. It will output a log of files that looks like:

'1677_093015.xlsx': Updated 0 links '1680_093015.xlsx': Updated 20 links '1682_093015.xlsx': Updated 5 links '1683_093015.xlsx': Updated 16 links '1700-1705_093015.xlsx': Updated 0 links '1710-1715_093015.xlsx': Updated 0 links 

You can easily make it output full path instead of just name (change .Name to .FullName). I figure that it will help some chap in the future. Probably named /u/Szeraax

If you know how to do this via ImportExcel, please enlighten me. I look around for about 3.5 minutes and gave up.

https://gist.github.com/Szeraax/ddbaeb316d98c121961a1555500df442

submitted by /u/Szeraax
[link] [comments]

Leave a Reply