Excel Automation

James_P_Sullivan

Hey,

I have a serious problem.

I have an excel file, which I initiate with dates.
When this is done, I compare these dates with dates from navision.

Now, sometimes there is a major crash in navision.
This happens when navision is trying to get a date from the excel file.
The weird thing is that it only happens sometimes.

Now, my question is if there is someone who knows what could be the problem. I included the code below:

FOR i := 9 TO 39 DO BEGIN
  tempDummy := cduExcel.fctGetCellValue('D' + FORMAT(i));
  CLEAR(tempDate);
  EVALUATE(tempDate, tempDummy);
  IF (DATE2DMY(tempDate, 1) = DATE2DMY("Posting Date", 1)) AND
      (DATE2DMY(tempDate, 2) = DATE2DMY("Posting Date", 2)) AND
      (DATE2DMY(tempDate, 3) = DATE2DMY("Posting Date", 3)) THEN 
    BEGIN
      fctPrintValue(WTCode, Quantity, i);
    END;
END;

Luc_VanDyck

Are you sure there is always a date filled in in cells D9..D39?
Maybe it's something in the function cduExcel.fctGetCellValue, that there is a problem with empty cells?

James_P_Sullivan

I checked that.
And that's not the problem

But I think I solved the problem.
To retrieve the date from the excel file, it has to get it from a template sheet. And afterwards, return it to another sheet.

The problem was that excel was switching from one sheet to the other, and it seemes like navision couldn't handle that.

So, now I don't let excel switch between the sheets, but just retrieve the value from the one sheet, without excel letting activate the sheet.

It seems to work now, so I think that was the problem.

James_P_Sullivan

Seems it is not yet completely solved...

It works most of the time.
But sometimes it still gives an fatal error.
The strange thing is that the error is for navision, and not for excel.
And another strange thing is that it happens on different times, the first job, somewhere in the middle, the last...
It is very strange...

If anyone has an idea what the problem could be, please tell me, because it's killing me...

janpieter

Whay do you mean by fatal error. Does navision Crash?

If not, what is the error code and description.

If navision crashes i would look first if the datatypes in excel and Navision matches each other ..

What datatype do you use? Variants?

James_P_Sullivan

Well, the problem is gone now.
I worked around it.
But, it is still not possible to get data from excel.

I use a function that reads the range.value from excel.
This returns a variant, but I parse it with the format function.
So, the type I get is text.

And yes, navision crashes...
It's like when you can send the report to microsoft.