Occasionally encounter error 5890 and 5884 with excel com-object

fc2005

New Member
Dear fellows,

I have created a .p to to create an Excel Com-object to read and reconcile data on the workbook, I then created window Schedule Job to execute this .p daily. For most of the time, it runs successfully without exception. However, occasionally it throws the errors 5890 and 5884. At first, I suspected the issue was related to the network instability issue(as the Excel file is originally stored on a File Server), I then revised the code to create a local temporary copy and read that copy instead. However, this did not resolve the issue. The Window Scheduler is also investigated and no other job with schedule time close to this is found.

May I have your advice for the issue please? any advice will be highly appreciated.

Thanks.


The following is the part of the code
Code:
/********************************************************************************/
PROCEDURE GetData:
DEFINE INPUT PARAMETER lBDay       AS LOGICAL NO-UNDO.
DEFINE INPUT PARAMETER dFileDate   AS DATE NO-UNDO.
DEFINE OUTPUT PARAMETER iCnt       AS INTEGER INITIAL 0  NO-UNDO.
DEFINE OUTPUT PARAMETER cFileMiss  AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER cFileExtra AS CHARACTER NO-UNDO.

DEFINE VARIABLE cInPath            AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFilePrefix        AS CHARACTER NO-UNDO.
DEFINE VARIABLE cInXLS             AS CHARACTER NO-UNDO.
DEFINE VARIABLE cKeyword           AS CHARACTER NO-UNDO.
DEFINE VARIABLE cCellValue         AS CHARACTER NO-UNDO.
DEFINE VARIABLE cReceivetime       AS CHARACTER NO-UNDO.   
DEFINE VARIABLE cNumOfDoc          AS CHARACTER NO-UNDO.
DEFINE VARIABLE cTempFile          AS CHARACTER NO-UNDO.

DEFINE VARIABLE lEmptyCellFound    AS LOGICAL INITIAL FALSE NO-UNDO. 
DEFINE VARIABLE chExcelApplication AS COM-HANDLE.
DEFINE VARIABLE chWorkbook         AS COM-HANDLE.
DEFINE VARIABLE iRow               AS INTEGER NO-UNDO.
DEFINE VARIABLE iCol               AS INTEGER NO-UNDO.
DEFINE VARIABLE iRetry             AS INTEGER INITIAL 0 NO-UNDO.

ASSIGN CInPath = getENVDtl("","XXX","DMO-Related-Report") 
       cFilePrefix = getENVDtl("","XXX","DMO-Related-Report")
       cKeyword = getENVDtl("","XXX","DMO-Related-Report") 
       cInXLS = cInPath + DateFormat(dFileDate,"YYYYMM") + "\" + cFilePrefix + DateFormat(dFileDate,"DDMMYYYY") + ".xls"
       cTempFile = SESSION:TEMP-DIRECTORY + SUBSTRING(cInXLS, R-INDEX(cInXLS,"\") + 1).

IF SEARCH(cInXLS) <> ? THEN
DO:
    IF lBDay THEN
    DO:
        DO WHILE iRetry < 3:
            ASSIGN iRetry = iRetry + 1.
            OS-COPY VALUE(cInXLS) VALUE(cTempFile).
            PAUSE 5.
            IF SEARCH(cTempFile) <> ? THEN
            DO:
                CREATE "Excel.Application" chExcelApplication.
                chExcelApplication:Visible = TRUE.
                chExcelApplication:screenupdating = TRUE.
                chExcelApplication:displayAlerts = FALSE.

                ASSIGN chWorkbook = chExcelApplication:Workbooks:OPEN(cTempFile).

                ASSIGN iCol = chExcelApplication:Range("A:Z"):Find(cKeyword,,,,,,,,):COLUMN
                       iRow = chExcelApplication:Range("A:Z"):Find(cKeyword,,,,,,,,):ROW.
    
                ASSIGN iRow = iRow + 1.
               
                DO WHILE lEmptyCellFound = FALSE:
                   ASSIGN iRow = iRow + 1
                          cCellValue = chExcelApplication:Cells(iRow,iCol):VALUE
                          cReceivetime = chExcelApplication:Cells(iRow, iCol + 2):VALUE
                          cNumOfDoc = chExcelApplication:Cells(iRow, iCol + 3):VALUE.
                   IF (cCellValue = "" OR cCellValue = ?) AND (cReceivetime = "" OR cReceivetime = ?) AND (cNumOfDoc= "" OR cNumOfDoc = ?) THEN
                   DO:
                        ASSIGN lEmptyCellFound = TRUE.
                   END.
                   ELSE IF NOT (cCellValue = "" OR cCellValue = ?) THEN DO:
                        ASSIGN iCnt = iCnt + 1.
                        CREATE tt_Toppan.
                        ASSIGN tt_Toppan.cBarcode = cCellValue
                               tt_Toppan.cReceivetime = SUBSTRING(cReceivetime, 1,  INDEX(cReceivetime, ".") - 1 )
                               tt_Toppan.iNumOfDoc = INTEGER(cNumOfDoc).
                   END.
                END.
                   
                chWorkbook:CLOSE.

                RELEASE OBJECT chWorkbook NO-ERROR.
                chExcelApplication:QUIT().
                RELEASE OBJECT chExcelApplication NO-ERROR.
               
                OS-DELETE VALUE(cTempFile). 
               
                LEAVE.
            END.
        END.
    END.
    ELSE DO:
       ASSIGN  cFileExtra =  cFilePrefix + DateFormat(dFileDate,"DDMMYYYY") + ".xls".
    END.
END.
ELSE DO:
    IF lBDay THEN
    DO:
        ASSIGN  cFileMiss =  cFilePrefix + DateFormat(dFileDate,"DDMMYYYY") + ".xls".
    END.
END.


END PROCEDURE.

/********************************************************************************/

Error Screen Captured
dds.png
 
Last edited by a moderator:

Cecil

19+ years progress programming and still learning.
Try using VALID-OBJECT() function before releasing the object.

if valid-object(chExcelApplication) then RELEASE OBJECT chExcelApplication.

 

Bounty

New Member
In the error message I see 'GetToppanData', that is the name of the procedure where the error occurs. The code snippet is procedure 'GetData'.
 

fc2005

New Member
In the error message I see 'GetToppanData', that is the name of the procedure where the error occurs. The code snippet is procedure 'GetData'.
Thanks for your advice Bounty, indeed, I renamed the procedure on the pseudo code as 'GetData' just for a sake of demonstration. The actual procedure I am calling in the code is 'GetToppanData'.
 
Top