I'm working on tidying a complex spiderweb of inter-linked workbooks used in filing an annual report. To give a sense of the scale, its 160+ separate workbooks and there are more than 4000 cells containing a reference to one or more other workbooks.
I want to create a table of the individual references between workbooks, a la
and so on. The desired output would be at the level of cell-to-cell connections (unlike this method which provides workbook-to-workbook level granularity), has a separate entry for each source cell (unlike these methods which create a single entry for each destination cell and the formula in that cell, which may contain an any number of external references), and gives the full path of each connection, whether that's local or SharePoint.
So far, I've implemented the example of the second link above, so I have a list of destination cells and the formula in those cells. One option would be to take that and extract external references from each formula. Possibly by regex, but that's... clunky at best and there doesn't seem to be any consensus on how to create a regex that would reliably capture any external link.
Definitely open to implementing a solution in VBA (although I'm at a 'dabbling' level at best) or in something else if anyone can point me in the right direction.
Appreciate your help!
EDIT - apologies for the not-actually-links in the table. They were detected automatically I can't seem to get them not to display as links.
