Excel Export Formula "VLOOKUP"

Lgoo

hello,

i am upgrading a report from 2009 R2 to 2013. This reports opens an excisting excel file with two sheets and creates one new sheet.
On the new sheet i am trying to add a formula like this: =VLOOKUP(A2;Table1!A2:B260;2;0).
But after the report is completed and it opens the file on the Client is crashes with following error:
hresult 0x800a03ec

this function was running fine on 2009 R2...

somebody has an idea?

thanks for help

Rob_Hansen

Just yesterday I hit the exact same issue on a report using the Excel buffer in NAV 2013 R2, and i'm not using VLOOKUPS. If I open the excel file (by debugging and seeing the location of the temp file, then opening it directly) I get the error:

Excel found undreadable content in 'Book1.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

When I proceed to open the file, I get the following message regarding repairs that were made:

Removed Records: Formula from /xl/worksheets/sheet.xml part

So...is there a problem with using any formula here? Does anyone have any insights into this one?

Lgoo

I get the same error at opening excel.
which formula are you using?

My Code works fine with the SUM function but not with vlookup
This is my Code:

gExcelBuffer.INIT;
exlBuffer.VALIDATE("Row No.",pRow);
exlBuffer.VALIDATE("Column No.",pColumn);
exlBuffer.Bold := pBold;
exlBuffer.Italic := pItalic;
exlBuffer.Underline := pUnderline;
IF pCellType = pCellType::Formula THEN  BEGIN
  exlBuffer.SetFormula(FORMAT('SVERWEIS(C6;A2:B278;2)')); // or 'VLOOKUP(C6;A2:B278;2)'
  exlBuffer.NumberFormat := 'General'; // This is important for formulars
END ELSE BEGIN
  exlBuffer."Cell Value as Text" := pCellValue;
  exlBuffer."Cell Type" := pCellType;
END;
exlBuffer.INSERT(TRUE);

geordie

0x800A03EC (or -2146827284) means NAME_NOT_FOUND: are you using Excel in German language? Just a guess because I saw you are setting the formula with German naming.

Rob_Hansen

I took another look at the report i'm migrating, and it's actually not using formulas. In the previous version we were feeding numeric values into the excel buffer using the Formula field and that worked fine...apparently in NAV 2013 that trips things up. Once I switched to passing the values into the Cell Value as Text field, things work fine.

Lgoo

@geordie
Yes i am using Excel in German Version, but i triyed also the Englisch formula "VLOOKUP" with the same error.

Lgoo

somebody has an idea?

CodeGenius

Hi,

I suggest you try using a comma (,) instead of the semi-colon (;)

So change this

=VLOOKUP(A2;Table1!A2:B260;2;0)

Into this

=VLOOKUP(A2,Table1!A2:B260,2,0)

I hope this helps you out.

Gerard Robbertsen
http://www.codegeniusstudio.com
https://www.facebook.com/CodeGeniusStudio