title: Detect Excel WorkBook Links
body: <p><a href="http://www.linkfixerplus.com" rel="nofollow">http://www.linkfixerplus.com</a> 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.</p>
  
  <p>This is the bare bone code I've written so far (more like psuedo code at this stage - targeting .Net 2 so forget LINQ):</p>
  
  <pre><code>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&lt;LinkInfo&gt; 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 &lt;= values.GetUpperBound(0); row++)
                          {
                              for (int col = 1; col &lt;= 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("=") &amp;&amp; cellFormula.Contains("!") &amp;&amp; cellFormula.Contains("[") &amp;&amp; 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;
  }
  </code></pre>
  
  <p><strong>This works perfectly when the XLS files have links to other workbooks expressed this way:</strong></p>
  
  <p>=[a.xlsx]Sheet1!$A$1</p>
  
  <p>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:</p>
  
  <p>=Excel.Sheet.12|'C:\Temp\a.xlsx'!'!Sheet2!R6C3'</p>
  
  <p>and this:</p>
  
  <p>=EMBED("Excel.Sheet.12","")</p>
  
  <p>The line of code detecting the formula isn't going to work for the above two:</p>
  
  <pre><code>if (cellFormula.StartsWith("=") &amp;&amp; cellFormula.Contains("!") &amp;&amp; cellFormula.Contains("[") &amp;&amp; cellFormula.Contains("]"))
  </code></pre>
  
  <p><strong>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?</strong></p>
  
  <p>The other product I found that can do it is <a href="http://www.2haveit.com/listdetail.php?id=90005" rel="nofollow">http://www.2haveit.com/listdetail.php?id=90005</a> 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.  </p>
  
  <p>Edit: I need this as a winform app not Excel Add-In</p>
  
  <p>I found out there is a method in the object model:
  <a href="http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.linksources(v=vs.80).aspx" rel="nofollow">Workbook.LinkSources Method</a> however it fails to detect this link as well:</p>
  
  <p>=Excel.Sheet.12|'C:\Temp\a.xlsx'!'!Sheet2!R6C3'</p>