Excel Automation

James_P_Sullivan
James_P_Sullivan Member Posts: 5
edited 2004-11-04 in NAV/Navision Classic Client
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;

Comments

  • Luc_VanDyck
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    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?
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • James_P_Sullivan
    James_P_Sullivan Member Posts: 5
    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
    James_P_Sullivan Member Posts: 5
    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
    janpieter Member Posts: 298
    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?
    In a world without Borders or Fences, who needs Windows and Gates?
  • James_P_Sullivan
    James_P_Sullivan Member Posts: 5
    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.

Categories