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,
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?
Please let me know if you have any other questions or concerns on this matter.
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
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
We are using version 16.2.20162.2182.
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?
I was able to load the file you attached but not my original file. This is the method I'm using to load the file;
Infragistics.Documents.Excel.Workbook.Load(<filename>, false)
How do you add/attach a file to the thread?
You can add files to the thread by using the Insert drop-down and selecting the “Insert Image/Video/File” option. You will be prompted with a dialog box asking for a source (From) and a File.
Note, you can only attach files that are 1MB or less to the Infragistics forums. If your files are larger than this, please send it in an e-mail to support@infragistics.com.
I have been able to download your workbooks and when loading the .xlsx, I am able to reproduce the FormulaParseException that you are referring to. This behavior is unexpected, and as such, I have asked our engineering teams to examine it further.
To ensure that this receives attention, I have logged it in our internal tracking systems with a development ID of 266244. I have also created a private support case for you that this issue will be linked to. This support case has an ID of CAS-203924-D6F9R5 and you can access it at https://es.infragistics.com/my-account/support-activity.
Sorry, our mail server is preventing me from sending the files. Can you download it from this location?
transfer.dmsi.com/.../cedar_board_price_import.zip
File is submitted to the support email.