Infragistics.Documents.Excel is somehow not recognizing an xlsx file that has columns referencing an external xlsm file in a formula. Before I dig in further to see what is wrong, I just want to confirm if this scenario is supported.
Thanks,
Hello Joseph,
I have tested loading the “LookupWorkbook.xlsx” file that I have provided to you in my most recent attachment with version 16.2.20162.2182, and I am still unable to see any exceptions occurring.
Have you tried loading this workbook on your end? Does it cause an exception when doing so? If so, what method overload are you using to load the Workbook.
If the exception does not occur, then would it be possible for you to please provide me with a pair of Excel workbooks that reference each other so that I may reproduce this issue locally on my end?
Please let me know if you have any other questions or concerns on this matter.
We are using version 16.2.20162.2182.
I have been investigating into the behavior you are seeing, and have put together a pair of workbooks, one being an .xlsm and the other an .xlsx and I have used a VLOOKUP function to reference the .xlsm from the .xlsx. In doing so, and trying to load this with the Infragistics for Windows Forms Excel Engine, I am unable to reproduce the behavior you are seeing.
I am attaching the 2 workbooks I have attempted this with – it will be helpful if you can try these workbooks on your end as well. It is also worth noting that I am using specific version 19.1.20191.150 of the Infragistics for Windows Forms 2019.1 toolset. If this version differs from yours, please let me know, as this could also be the root of this issue.
Excel Books.zip
Here is the exception;
Infragistics.Documents.Excel.FormulaParseException: A function in the formula has no closing parenthesis. Portion with error: VLOOKUP($D6,[1]
Here is a sample formula for one of the cells;
=IF($D6="","",VLOOKUP($D6,'C:\Users\...\Documents\Product.Group.Pricing\cedar.boards.xlsm'!margin_table[#Data],MATCH([@Q],'C:\Users\...\Documents\Product.Group.Pricing\cedar.boards.xlsm'!margin_table[#Headers],0),FALSE))
... - represents 40 chars redacted text
My team and I have done an initial review of this thread, and I am a little confused on what exactly is not being recognized in this case. Is the .xlsx file failing to load, or is the formula referencing the external .xlsm file not being evaluated? In either case, are you receiving any sort of exception when trying to do this? If so, what is the exception you are seeing?