save as not working with excel + openedge 10.2b

[kasundha]

i used an excel files to view reports(DASHBOARD) data to system users. 11 excel files are generating by this report. currently i have to save each report separately in everyday. i just want to automate the report and send these reports to relevant user. so i want to save those files in different location and i used this code to do that. but it is not working

chWorkbook:SaveAs("c:\temp\sample.xlsx",,,,,,,).

Error:-
Invalid component-handle referenced while processing method/statement: SaveAs.

DASHBOARD C:\OpenEdge\WRK\p46708_MIS2.ab (5884)

excel version : 2007
openedge version: 10.2B

 

[Osborne]

It has been a while since I used Excel component handles and could be totally wrong on this, but I think I recall that starting with Excel 2007 you had to use a number for the SaveAs parameters which I think is 51 for standard xlsx files:

IF VALID-HANDLE(chWorkbook) THEN DO:
   chWorkbook:SaveAs("c:\temp\sample.xlsx",51,,,,,) NO-ERROR.
   IF ERROR-STATUS:GET-MESSAGE(1) MATCHES "*SaveAs*" THEN
      MESSAGE "There was an error trying to save the workbook." VIEW-AS ALERT-BOX ERROR.
END.

If this does not solve then ensure the exact path is correct, the directory being saved to allows writes and that a workbook with that exact name is not already open in Excel.

 

[kasundha]

Thank you very much. your code is working for newly opened work books only. hear im using excel file as a template.

/**** example code****/
/*------------------------------------------------------------------------------
  Purpose:    
  Parameters:  <none>
  Notes:      
------------------------------------------------------------------------------*/
DEFINE VARIABLE chExcel     AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorksheet AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook  AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ROW_NO      AS INTEGER    NO-UNDO INIT 3.
DEFINE VARIABLE COL_ROW_NO AS INTEGER    NO-UNDO INIT 28.
DEFINE VARIABLE DIF_ROW_NO AS INTEGER    NO-UNDO INIT 53.
DEFINE VARIABLE LAST_MONTH_ARREARS AS DECIMAL    NO-UNDO.

CREATE "excel.application" chExcel.

chExcel:Workbooks:Open("D:\Opensoft\GL\DOC\COLLECTION SUMMARY BUCKET WISE.xlsx").

RUN getMonth(INPUT MONTH(mSYSDATE), OUTPUT mMonth).


/*ARREARS REPORT HEADING*/
chExcel:Range(chExcel:Cells(1,1),chExcel:Cells(1,5)):HorizontalAlignment = -4108.
chExcel:Range(chExcel:Cells(1,1),chExcel:Cells(1,5)):VerticalAlignment = -4108.
chExcel:Range(chExcel:Cells(1,1),chExcel:Cells(1,5)):Merge.
chExcel:Range(chExcel:Cells(1,1),chExcel:Cells(1,5)):VALUE = "BRANCH WISE COLLECTION ANALYSYS REPORT AS AT :- " +  STRING(mSYSDATE,"99/99/9999").

/* MONTH END BALANCES */
ASSIGN grp1Arr = 0 grp2Arr = 0 grp3Arr = 0 grp4Arr = 0 grp5Arr = 0 TOT-Current = 0 grp6Arr = 0 grp7Arr = 0 grp8Arr = 0 grp11Arr = 0.

FOR EACH Arrears WHERE  NO-LOCK BREAK BY tt-BRCode:
     IF Arrears.tt-group-tag = "RLS" THEN /*SHORT FALLS CAN-DO("CA009894,CC009870,CK009882,CM009890,ID140817,IH004130,IH196077",Arrears.tt-FollowUpOfficer)*/
     DO:
         ASSIGN grp7Arr = grp7Arr + Arrears.tt-collection.
     END.
     ELSE IF Arrears.tt-group-tag = "LGL" THEN /*LEGAL ACTION*/
     DO:
         ASSIGN grp8Arr = grp8Arr + Arrears.tt-collection.
     END.
     ELSE IF Arrears.tt-group-tag = "REP" THEN /*REPOSSES Arrears.tt-FollowUpOfficer = "IY065500"*/
     DO:
         ASSIGN grp6Arr = grp6Arr + Arrears.tt-collection.
     END.
     ELSE
        DO:
           IF (tt-NoOfRentalsInArrears >= 0 AND tt-NoOfRentalsInArrears < 1) THEN
               ASSIGN grp1Arr = grp1Arr + Arrears.tt-collection.
           ELSE IF (tt-NoOfRentalsInArrears >= 1 AND tt-NoOfRentalsInArrears < 1.5) THEN
               ASSIGN grp11Arr = grp11Arr + Arrears.tt-collection.
           ELSE IF (tt-NoOfRentalsInArrears >= 1.5 AND tt-NoOfRentalsInArrears < 2) THEN
               ASSIGN grp2Arr = grp2Arr + Arrears.tt-collection.
           ELSE IF (tt-NoOfRentalsInArrears >= 2 AND tt-NoOfRentalsInArrears < 3) THEN
              ASSIGN grp3Arr = grp3Arr + Arrears.tt-collection.
           ELSE IF (tt-NoOfRentalsInArrears >= 3 AND tt-NoOfRentalsInArrears < 6) THEN
              ASSIGN grp4Arr = grp4Arr + Arrears.tt-collection.
           ELSE IF (tt-NoOfRentalsInArrears >= 6 AND tt-NoOfRentalsInArrears < 9) THEN
              ASSIGN grp5Arr = grp5Arr + Arrears.tt-collection.
           ELSE IF (tt-NoOfRentalsInArrears >= 9) THEN
              ASSIGN grp9Arr = grp9Arr + Arrears.tt-collection.
        END.

     IF LAST-OF(tt-BRCode) THEN
     DO:
        FIND FIRST branches WHERE branches.brncode = tt-BRCode NO-LOCK NO-ERROR.
        IF AVAILABLE(branches) THEN
        DO:
            ASSIGN chExcel:Cells(ROW_NO,1):VALUE        = corpdata.branches.descr.  /*ARREARS TABLE*/
        END.
        ELSE
        DO:
            ASSIGN chExcel:Cells(ROW_NO,1):VALUE        = "BRANCH NOT FOUND".  /*ARREARS TABLE*/
        END.

        ASSIGN chExcel:Cells(ROW_NO,2):VALUE = grp1Arr
               chExcel:Cells(ROW_NO,3):VALUE = grp11Arr
               chExcel:Cells(ROW_NO,4):VALUE = grp2Arr
               chExcel:Cells(ROW_NO,5):VALUE = grp3Arr 
               chExcel:Cells(ROW_NO,6):VALUE = grp4Arr 
               chExcel:Cells(ROW_NO,7):VALUE = grp5Arr
               chExcel:Cells(ROW_NO,8):VALUE = grp9Arr
               chExcel:Cells(ROW_NO,10):VALUE = grp6Arr 
               chExcel:Cells(ROW_NO,11):VALUE = grp7Arr 
               chExcel:Cells(ROW_NO,12):VALUE = grp8Arr.
              
               ASSIGN   ROW_NO     = ROW_NO + 1.

          ASSIGN grp1Arr = 0 grp2Arr = 0 grp3Arr = 0 grp4Arr = 0 grp5Arr = 0 TOT-Current = 0 grp6Arr = 0 grp7Arr = 0 grp8Arr = 0 grp9Arr = 0 grp11Arr = 0.
     END.
END.
/*END OF MONTH END BALANCES */


chExcel:visible = false.

IF VALID-HANDLE(vchWorkbook) THEN DO:
   vchWorkbook:SaveAs("E:\DailyReports\RecoveryDashboard.xlsx",51,,,,,) NO-ERROR.
   IF ERROR-STATUS:GET-MESSAGE(1) MATCHES "*SaveAs*" THEN
      MESSAGE "There was an error trying to save the workbook." VIEW-AS ALERT-BOX ERROR.
END.


RELEASE OBJECT chWorksheet NO-ERROR.
RELEASE OBJECT chWorkbook NO-ERROR.
RELEASE OBJECT chExcel NO-ERROR.
END PROCEDURE.
/*** end of code***/

there is no any error occurred. but save as option is not working.

 

[Cringer]

I've just scanned the code, but I think the issue is that you're not setting vchWorkbook anywhere. Is it a valid handle at the point of trying to save as? Shouldn't you be using a property of chExcel for the save as command?

 

[Osborne]

Cringer is correct, you are not setting chWorkbook anywhere. I think you are missing this:

ASSIGN chWorkbook = chExcel:Workbooks:Open("D:\Opensoft\GL\DOC\COLLECTION SUMMARY BUCKET WISE.xlsx") NO-ERROR.

 

[kasundha]

I've just scanned the code, but I think the issue is that you're not setting vchWorkbook anywhere. Is it a valid handle at the point of trying to save as? Shouldn't you be using a property of chExcel for the save as command?

got it. my mistake. thanks a lot

 

[kasundha]

Cringer is correct, you are not setting chWorkbook anywhere. I think you are missing this:

ASSIGN chWorkbook = chExcel:Workbooks:Open("D:\Opensoft\GL\DOC\COLLECTION SUMMARY BUCKET WISE.xlsx") NO-ERROR.

got it. my mistake. thanks a lot