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
/********************************************************************************/
Error Screen Captured
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
Last edited by a moderator: