VLOOKUP formula errors

Question

Hi,

Does anyone have an working example of using VLOOKUP through XlsIO?
I am trying a simple lookup of full names in an external worksheet, based on mnemonics from my worksheet like this:
range = worksheet.Range[rowNum, colNum];
formula = @"VLOOKUP(R1C1,''D:\[CustomerMaster.xls]Sheet1''!$C:$D,2,FALSE)";
range.FormulaR1C1 = formula;
I get an error to this effect:
Can''t parse formula: ''D:\[CustomerMaster.xls]Sheet1''!$C:$D
Excel Binary workbook was not saved. Object can not be disposed. Save workbook or set property ThrowNotSavedOnDestoy to false.

Unhandled Exception: Syncfusion.XlsIO.ExcelWorkbookNotSavedException: Excel Binary workbook was not saved. Object can not be disposed. Save workbook or set property ThrowNotSavedOnDestoy to false.
   at Syncfusion.XlsIO.ExcelEngine.Dispose()
   at Syncfusion.XlsIO.ExcelEngine.Finalize()

Thanks for any suggestions,
Leo

Administrator · Answer

Hi Leo,

Use the following code for the VLookup Function in XlsIO.

//Setting the VLOOKUP Formula.
sheet.Range["A28"].Formula="VLOOKUP(A12,A10:B26,2,FALSE)";

Here is the sample for your reference:Vlookup.zip

Please let me if you need any further assistance.


BestRegards,

Bharath

Administrator · Answer

Hi Bharath,

Your example works as it is, but fails when trying to lookup values from an external file rather than from whitin the current workbook.
I simply saved the generated Sample.xls as External.xls and changed the formula in your example like this:
//sheet.Range["A28"].Formula="VLOOKUP(A12,A10:B26,2,FALSE)";
sheet.Range["A28"].Formula=@"=VLOOKUP(A12,''C:\Temp\vlookup\Vlookup\bin\Debug\[External.xls]Sheet1''!$A$10:$B$26,2,FALSE)";

When the execution reaches the above line, I get the following error:
An unhandled exception of type ''System.ArgumentOutOfRangeException'' occurred in syncfusion.xlsio.base.dll

Additional information: Length cannot be less than zero.

Any idea? Am I not specifying the formula the way XlsIO wants it?

Thanks,
Leo

Administrator · Answer

Hi Leo,
 
Sorry for the delay in getting back to you.
 
Currently we dont support link to the external sheet. so I have created a feature request with the development team.
 
http://www.syncfusion.com/support/features/xlsio/Default.aspx?ToDo=view&questId=929
 
I will update you as soon as an estimate is available.
 
Thanks,
 
Bharath