Import to Nav from Excel using Interop

CGaralde
CGaralde Member Posts: 19
edited 2015-10-28 in NAV Three Tier
Hi,


I have a scenario where I have an existing Excel automation where I import Excel data into Nav 5.0. Now that we have migrated to Nav 2013 R2, the same automation takes very slow. So I tried out using DotNet Interop, and got the following code through searching:
  XlApp := XlApp.ApplicationClass;
  
  XlWrkBk := XlApp.Workbooks.Open(FileName,0,TRUE,5,'','',TRUE,2,'\t',FALSE,FALSE,0,TRUE,1,0);
  
  XlWrkSht := XlWrkBk.Worksheets.Item(1);
  
  XlRange :=  XlWrkSht.Range('A1', 'A1');

Now I am stuck with the error on the XlRange line, showing the following:
Cannot serialize an instance of the following .NET Framework object: assembly mscorlib, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089,type System._ComObject.

This is the first time i am dabbling on this so any help is appreciated. Thanks.

PS. :smile:

I am basically trying to replicate the following in DotNet Interop
ExcelWorkbooks.Open(FileName);
ExcelWorkbook := ExcelApp.ActiveWorkbook;
ExcelSheets := ExcelWorkbook.Worksheets;
ExcelWorksheet := ExcelSheets.Item(1);
ExcelWorksheet.Range('H'+FORMAT(Row)).Value

Comments

  • Wisa123
    Wisa123 Member Posts: 309
    Hi, just guessing here but i had something similar happen before to me.

    Did you already try to not hardcode 'A1', but instead putting it into a text Variable and then passing the Variable?

    Regards
    Austrian NAV/BC Dev
  • CGaralde
    CGaralde Member Posts: 19
    Hi Wisa,

    After I changed it to just 'A1' or 'A1:B1' instead of 'A1','A1' then the error did not appear. Thanks for that. :smiley:

    Now, how do I get the value of the cell A1?
    XlWrkSht := XlWrkBk.Worksheets.Item(1);
MESSAGE(FORMAT(XlWrkSht.Name));

    This code outputs the correct sheet name of my excel file which tells me that XlWrkSht is working.
    XlRange :=  XlWrkSht.Range('A1');
MESSAGE(FORMAT(XlRange.Value));

    This code gives me the error:
    A DotNet variable has not been instantiate. Attempting to call Microsoft.Office.Interop.Excel.Range.Value

    Any ideas? Thanks.
  • rajavenkat74@gmail.com
    rajavenkat74@gmail.com Member Posts: 19
    edited 2015-12-10
    Hi CGaralde,

    I'm New to NAV sorry if i said wrong :'( , But xlRange.value might take input's , something like xlRange('A1').VAlue := Customer."No.";
    Do you got result ...I mean HAVE YOU Export to excel using only Dotnet fields,,,i'm trying to Merge cells in excel but unsucessful.. o:)

    Am too Having same Error but can't figureout wat to doo....
    Regards,
    Raja Venkat

    https://beginnersnav.blogspot.in/
  • amr_wafa
    amr_wafa Member Posts: 23
    Hi,

    Same problem here, it reaches to

    XlWrkSht := XlWrkBk.Worksheets.Item(1);
    MESSAGE(FORMAT(XlWrkSht.Name));

    this giving error:

    XlRange := XlWrkSht.Range('A1');
    MESSAGE(FORMAT(XlRange.Value));

    Also as simple as:

    XlRange1.Activate;

    gives same error

    Any help appreciated.
    Thanks.
  • amr_wafa
    amr_wafa Member Posts: 23
    OK ... After many many hours of try and error....the soultion is very simple.

    XlRange variable shoud RunOnClient = Yes

    That's it.

    Hope this helps.
  • CGaralde
    CGaralde Member Posts: 19
    Thanks amr_wafa.

    It sure does work. :smile:

    Now can someone please enlighten me with the actual syntax for the Range property of Worksheet.

    The following code gives me an error of "A DotNet variable has not been instantiated. Attempting to call Microsoft.Office.Interop.Excel.Range.FormulaR1C1....." 
    XlRange :=  XlWrkSht.Range('A1');
MESSAGE(FORMAT(XlRange.FormulaR1C1));

    Now if I change it to: 
    XlRange :=  XlWrkSht.Range('A1','A1');
MESSAGE(FORMAT(XlRange.FormulaR1C1));

    Take note of the second parameter in the range property, I get the correct output message.

    I thought the second parameter was optional but how come I get the error when I leave it out? Any thoughts?

    On the same note, the following code gives the output message NULL 
    XlRange :=  XlWrkSht.Range('A1','A1');
MESSAGE(FORMAT(XlRange.Value));
    but switching to XlRange.FormulaR1C1 gives me the correct output message.

    Thoughts?


Categories