Import to Nav from Excel using Interop

CGaralde

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.

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

Wisa123

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

CGaralde

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.

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

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..

Am too Having same Error but can't figureout wat to doo....

amr_wafa

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

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

Thanks amr_wafa.

It sure does work.

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?