Retreive raw formula for cell that references external workbook

Topics: Developer Forum
Oct 2, 2012 at 8:05 PM

I have an excel sheet that contains a formula which references an external workbook.  Obviously, the formula cannot be computed in absence of the external reference but I would like to retrieve the underlying raw formula string.   When I call getValue() on the cell, all I get is "#ref" in return.   Is there a way to retrieve the underlying formula?  This seems like a very simple task but I can't seem to find any solution in the documentation.

Appreciate any advice, thanks.

Oct 2, 2012 at 9:01 PM

getValue() should return the raw formula: exceptions might be if this was an BIFF (.xls) file that used functions from the analysis toolpack. If a #ref is being returned instead, then it suggests a problem with parsing that formula when reading it from the file.

Oct 3, 2012 at 6:53 PM

Thanks for the quick reply, Mark.  You are correct in that the #REF seems to be an issue with BIFF formatted files but I've created files sans the Analysis Toolpack addon and it still occurs.  Here's an example of what I'm seeing:  I've created two blank Excel workbooks, Book1, Book2.  Cell "A1" in Book2 references cell "A1" in Book1. Very simple external reference example.  I save both workbooks in the following formats: BIFF(xls), 2007XML(xlsx), 2003XML(xml).  The results of calling getValue("A1") on Book2 results in the following results respectively:

BIFF(xls):  =#REF!

2007XML(xlsx):  =[1]Sheet1!$A$1

2003XML(xml): '\Users\Mike\Desktop\[Book1.xml]Sheet1'!A1

It appears that the only format that truly returns the actual formula with the external reference string is the 2003XML format.  Sans the ability to open a BIFF file and save it as a 2003XML file there doesn't seem to be any way for me to grab the underlying formula out of a BIFF file.  Is there any work around you can think of?  Thanks.

Oct 3, 2012 at 9:02 PM

I'd need to look at the underlying BIFF formula parser to try and determine exactly what it's doing in the case of a formula referencing an external workbook. while we clearly can't evaluate that formula, we should still be able to pick up the raw value.

Oct 3, 2012 at 9:03 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.