Excel Export Formula "VLOOKUP"

Lgoo
Lgoo Member Posts: 45
edited 2014-09-04 in NAV Three Tier
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 :)

Comments

  • Rob_Hansen
    Rob_Hansen Member Posts: 296
    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?
    Rob Hansen
    http://www.epimatic.com
  • Lgoo
    Lgoo Member Posts: 45
    edited 2014-05-12
    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
    geordie Member Posts: 655
    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.
    * Daniele Rebussi * | * Rebu NAV Diary *
  • Rob_Hansen
    Rob_Hansen Member Posts: 296
    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.
    Rob Hansen
    http://www.epimatic.com
  • Lgoo
    Lgoo Member Posts: 45
    @geordie
    Yes i am using Excel in German Version, but i triyed also the Englisch formula "VLOOKUP" with the same error.
  • Lgoo
    Lgoo Member Posts: 45
    somebody has an idea?
  • CodeGenius
    CodeGenius Member Posts: 40
    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
    Gerard Robbertsen

    http://www.codegeniusstudio.com
    http://www.facebook.com/CodeGeniusStudio

Categories