Detect Excel WorkBook Links

Question

http://www.linkfixerplus.com have a product to check workbook links and fix them, but its expensive and I've been asked to write one that will just check for Cross References (workbook links) and not worry if they are broken.

This is the bare bone code I've written so far (more like psuedo code at this stage - targeting .Net 2 so forget LINQ):

public class LinkInfo
{
    public string SheetName { get; set; }
    public string Cell { get; set; }
    public string CellValue { get; set; }
}

private bool ExcelFileHasLinks(string path,ref List<LinkInfo> linkInfoList)
{
    bool hasLinks = false;
    Workbook wb = null;
    try
    {
        wb = excel.Workbooks.Open(path);
            foreach (object possibleSheet in excel.Sheets)
            {
                var aSheet = possibleSheet as Worksheet;
                if (aSheet != null)
                {
                        //Get the values in the sheet
                        Range rng = aSheet.UsedRange;
                        object[,] values = null;
                        try
                        {
                            if (rng.Value2.GetType().IsArray)
                            {
                                values = (object[,])rng.FormulaArray;

                            }
                            else
                            {
                                values = new object[2, 2];
                                values[1, 1] = rng.FormulaArray;
                            }
                        }
                        catch
                        { }

                    if (values != null)
                    {
                        for (int row = 1; row <= values.GetUpperBound(0); row++)
                        {
                            for (int col = 1; col <= values.GetUpperBound(1); col++)
                            {
                                if (values[row, col] != null)
                                {
                                    //Check if this iis a link to another workbook
                                    string cellFormula = values[row, col].ToString().Trim();
                                    if (cellFormula.StartsWith("=") && cellFormula.Contains("!") && cellFormula.Contains("[") && cellFormula.Contains("]"))
                                    {
                                        hasLinks = true;
                                        linkInfoList.Add(new LinkInfo() { SheetName = aSheet.Name, Cell = ColumnNumberToName(col) + row, CellValue = cellFormula });
                                    }
                                }
                            }
                        }
                    }
                }
            }
    }
    catch (Exception)
    {
    }
    finally
    {
        if (wb != null) wb.Close();
    }

    return hasLinks;
}

This works perfectly when the XLS files have links to other workbooks expressed this way:

=[a.xlsx]Sheet1!$A$1

My problem is that there are literally hundreds of thousands of spreadsheets in the SAN and I am not allowed to view them due to sensitivity! So I was conducting tests to see if there are various syntaxes to do it and I found this:

=Excel.Sheet.12|'C:\Temp\a.xlsx'!'!Sheet2!R6C3'

and this:

=EMBED("Excel.Sheet.12","")

The line of code detecting the formula isn't going to work for the above two:

if (cellFormula.StartsWith("=") && cellFormula.Contains("!") && cellFormula.Contains("[") && cellFormula.Contains("]"))

My question is there any more syntaxs that I am not aware of and can anyone recommend elegant code to detect all the variations of workbook links?

The other product I found that can do it is http://www.2haveit.com/listdetail.php?id=90005 and its an Excel Add-In so it only works on one spreadsheet at a time - but they still must have covered there bases as I'm trying to do.

Edit: I need this as a winform app not Excel Add-In

I found out there is a method in the object model: Workbook.LinkSources Method however it fails to detect this link as well:

=Excel.Sheet.12|'C:\Temp\a.xlsx'!'!Sheet2!R6C3'

My [Mappit! addin](http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2613-Mappit-a-free-Excel-model-auditing-addin.html) detects and highlights workbooks links — brettdj, Jun 07 '12 at 02:27
your a champ! Can you please put it as an answer!! The entire article, cheers! ps please never link to that evil site again;) Hang on tho its an Excel add-in so I'd have to invoke it like this - http://stackoverflow.com/questions/10875278/vsto-add-ins-comaddins-and-requestcomaddinautomationservice and that isn't going to fly - thanks anyway. — Jeremy Thompson, Jun 07 '12 at 02:31
check out my second article, it provides the VBA for detecting a variety of links. You should be able to adapt this for C — brettdj, Jun 07 '12 at 02:40
Thanks it is much appreciated, would you have any idea why the LinkSources wont pick up this link: `=Excel.Sheet.12|'C:\Temp\a.xlsx'!'!Sheet2!R6C3'` — Jeremy Thompson, Jun 07 '12 at 02:46

score 1 · Answer 1 · answered Jun 07 '12 at 02:12

1

I found the object model way: Workbook.LinkSources Method

answered Jun 07 '12 at 02:12

Jeremy Thompson

61,933
36
195
321

brettdj · Accepted Answer · 2013-09-29T03:17:02.063

1

As requested:

My Mappit! addin detects and highlights workbooks links

Plus I am a huge fan of Bill Manville's FindLink tool

edited Sep 29 '13 at 03:17

answered Jun 07 '12 at 02:39

brettdj

54,857
16
114
177

ok, `=Excel.Sheet.12|'C:\Temp\a.xlsx'!'!Sheet2!R6C3'` is an object not a link. – Jeremy Thompson Jun 07 '12 at 03:21

Detect Excel WorkBook Links

2 Answers2