Searching within a workbook query

[mikeyfear]

Morning.

Some time ago, Rylo helped me with a code to find all the external links in a directory, which was great. What I'm trying to do now, is find every worksheet that forms part of an external link within one workbook.

I did think I was getting quite adept at adapting samples and googling, but this one’s beaten me…

In essence, I want to search every cell within the workbook, that contains “[“ & “]" and not containing "ActiveWorkbook" (since some of the sheets are badly written and seem to have external type links to internal sheets).

I then need to find away to strip this back to sheet level. Something like the

Please Login or Register  to view this content.

line I used to strip paths down one level? (Where sPath is a string and = ActiveWorkbook.Path)

Then I can remove all duplicate values, and be left with a list of all worksheets which have links into my model. (I can do this part!)

The reason for this is I’m looking to make it all more auditable, and intend to turn all the tabs on worksheets that link to the master work book a certain colour (I think I can do that bit by various example). Then all the worksheets that link into that one a certain colour. And so on and so on. Eventually uncoloured sheets will be redundant, and can be stripped out, and the last coloured sheet should be the Raw data that feeds everything else! The methodology is I want all the sheets to only reference from 1 sheet down. I.e. master to provider to calculator to source. Not Master to calculator or source! Hence the updating proceedure becomes obvious and clear, i.e. update all green tabs, where green is the source data!

[mikeyfear]

I have half this code sorted now, I can use list of workbooks and worksheets, and do the tab colouring fine..

I just cant find a way to search for any .value containing[*] within each worksheet and list any values that do in column A.

Can anyone give me a few pointers?


Regards.