This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I think the Directory tool is what you're looking for - you just select a folder and it will output the file names and paths, as well as several other data points, for every file in that folder. Does that get you what you need?
Do the files all have the same layout? The process will need to read each file in and have the option checked to bring in file name as a field, and then filter down to rows with hyperlinks. You may be able to use a dynamic input to stack all of the files if they have the same format. Otherwise, you will need to use a macro in order to get around the schema differences.
yesterday I already wanted to answer your question because a nice solution popped to my mind, but sadly my license expired exactly at that moment, such that I had to wait for my partner manager to assign me a fresh one.
Now, if you're dealing with xlsx-files, we can make use of the fact that they are an "open" format, I checked the structure which is just zipped xml files, and quickly spotted the place where the hyperlinks are referenced.
Therefore, this approach will fetch you all links that are clickable - even if they appear as text in the xlsx-file.
Filtering out inter-document links is left for you to do.
The nested batch-macro becomes actually quite simple, with some regex-fu:
I just tried to use my macro on the same files, saved with an ".xlsm"-Extension, I only had to change the filepattern in the directory tool, but apart from that, all hyperlinks were detected. See the output below.
Do you want to provide a minimal example file, such that I can check whether there are any complications?
Again: This approach will not work on Excel 97-2003 .xls-fileformats.