Excel conundrum

[Mike Clark]

Progress 9.1E v2 GUI under windows

Hi
We have developed for one of our users a program that creates a .CSV file and then emails it (using SMTPmail) to their customers – this works fine.
What the user now wants is to have a ‘pure’ Excel .XLS file with set formats and column widths produced instead, and then auto emailed.
I know how to create the Excel file in Progress (I can even give it a ‘saveas’ file name) but this leaves the file open on the screen. (see part example below)
Is there any way, in Progress, to force the Excel file to ‘close’ without user intervention??
I have tried various CLOSE(), QUIT() statements but with no luck.

Any help would be gratefully accepted
Mike Clark


DEFINE VARIABLE chExcelApplication AS COM-HANDLE.
DEFINE VARIABLE chWorkbook AS COM-HANDLE.
DEFINE VARIABLE chWorksheet AS COM-HANDLE.
DEFINE VARIABLE chChart AS COM-HANDLE.
DEFINE VARIABLE chWorksheetRange AS COM-HANDLE.

DEFINE VARIABLE iColumn AS INTEGER INITIAL 2 NO-UNDO.
DEFINE VARIABLE cColumn AS CHARACTER NO-UNDO.
DEFINE VARIABLE cRange AS CHARACTER NO-UNDO.


DEFINE VARIABLE savename AS CHAR FORMAT "x(50)" INITIAL "testxl.xls" NO-UNDO.
DEFINE VARIABLE cashdesc AS CHAR FORMAT "x(40)" NO-UNDO.

OS-DELETE VALUE(savename).
PAUSE 0 NO-MESSAGE.
/*
To produce xl version
*/
/* create a new Excel Application object */
CREATE "Excel.Application" chExcelApplication.
/* launch Excel so it is not visible to the user */

chExcelApplication:Visible = FALSE.
chExcelApplication:ScreenUpdating = FALSE.

/* create a new Workbook */
chWorkbook = chExcelApplication:Workbooks:Add().
/* get the active Worksheet */
chWorkSheet = chExcelApplication:Sheets:Item(1).
/* set the column names for the Worksheet */

chWorkSheet:Columns("A"):ColumnWidth = 4. /* P/M */
chWorkSheet:Columns("B"):ColumnWidth = 40. /* Description */

/* rest of widths defined here */

/* chWorkSheet:Range("A1:I1"):Font:Bold = TRUE. */
chWorkSheet:Range("A1"):Value = "Z-Read Till".
chWorkSheet:Range("A2"):Value = "P/M".

/* rest of coulns defined here */


/* formats, currencies for columns etc
chWorksheet:COLUMNS(3):NumberFormat = "#,###,##0".
*/


DEF VAR xchar AS CHAR FORMAT "x(80)" NO-UNDO.
DEF VAR xx AS INT NO-UNDO.
FOR EACH cashup NO-LOCK:
FIND ana-codes WHERE ana-codes.ana-code = cashup.ana-code AND
ana-code.ana-type = "PM" NO-LOCK NO-ERROR.
IF AVAIL ana-codes THEN cashdesc = ana-codes.DESCRIPTION.
ELSE cashdesc = "UNKNOWN PAYMENT METHOD".

iColumn = iColumn + 1.
cColumn = STRING(iColumn).
cRange = "A" + cColumn.
chWorkSheet:Range(cRange):Value = Cashup.ana.
cRange = "B" + cColumn.
chWorkSheet:Range(cRange):Value = Cashdesc.
/* rest of spread sheet set up here */

END.


chExcelApplication:Visible = TRUE.
chExcelApplication:ScreenUpdating = TRUE.
chWorkSheet:Range("A2:A2"):Select().

IF savename NE "" THEN
chWorkbook:SaveAs(savename,-4143,,,,,,,TRUE).

/* release com-handles */
RELEASE OBJECT chExcelApplication.
RELEASE OBJECT chWorkbook.
RELEASE OBJECT chWorksheet.
/*
RELEASE OBJECT chWorksheetRange.
*/

 

[mwm]

Mike, I'm extremely interested in this because I just used the "Create Excel.Application" for the first time also. I'm having troubles getting the spreadsheet emailed from Unix but that's another story.

What's strange to me is why you're making Visible & ScreenUpdating TRUE just before the SaveAs. I think this is wrong. I would remove those lines & then use the Close method after the SaveAs.

HTH,
Matt

 

[Mike Clark]

Hi Matt,

The Visable/True bits were still in because this was the only way I could get it to work - after a fashion.

After taking them out and putting in the
chworkbook:CLOSE
after the SaveAs as you have suggested it now works.

So many thanks

Mike

 

[dayv2005]

im not sure if this will be a help here and what not but i did a app for our web based vehicles for sale. Once again I'm still new to progress and im only 20, so that shows you i have very little experience, but working on it.

I didn't create an xls file but i did do this in a csv file. You said you have the excel file created so that shouldn't matter than. I had it sent using a command line doing it in silent. If this isn't what you were looking for than im sorry. I just figured i'd make an attempt to help you out.

 OUTPUT CLOSE.

    scr-TTDisp:SCREEN-VALUE = "Sending File".

    IF tDebug:SCREEN-VALUE = "yes" THEN
        OS-COMMAND VALUE("n:\pro400\tt\sendTT.bat").
    ELSE
        OS-COMMAND SILENT VALUE("n:\pro400\tt\sendTT.bat").

    scr-TTDisp:SCREEN-VALUE = "".

then we hadit sent through a batch file.

 

[dayv2005]

Also another way we did it was like this

FOR EACH EmailParams WHERE EmailParams.user-id = OS-GETENV("winstationname") EXCLUSIVE-LOCK:
        DELETE EmailParams.
    END.

    FIND aimdata.User-File WHERE User-File.User-ID = USERID("aimdata") NO-LOCK.
   

    CREATE EmailParams.
    ASSIGN
        EmailParams.CC                  = 'dpipes@aimntls.com'
        /*EmailParams.CustLocation        = tCustLocID*/
        /*EmailParams.EmailTo             = */
        EmailParams.OutputDirectory     = 'n:\pro400\tc\' 
        /*EmailParams.Priority            = rs-priority:SCREEN-VALUE*/
        EmailParams.Recep               = /*'srt@mchsi.com'*/ 'dpipes@aimntls.com'
        EmailParams.Report              = 'tc.csv'
        EmailParams.Subject             = 'Aim Used Truck Inventory File'
        EmailParams.user-id             = OS-GETENV("winstationname")
        EmailParams.UserEmailAddress    = User-File.EmailAddress
        /*EmailParams.WEDate              = STRING(tWEDate)*/
        EmailParams.MessageText         = "This is our Used Truck Inventory File. If There's a problem uploading this 
                                           Please Contact David Pipes dpipes@aimntls.com thanks.".
    RELEASE EmailParams.

    DEFINE VARIABLE cCommandLine AS CHARACTER  NO-UNDO.

    IF PDBNAME(1) = "driverbdev" THEN
        ASSIGN cCommandLine = 'n:\PROGRESS\bin\prowin32.exe -p n:\pro400\dev\EmailBlockS30.p -pf dev.pf -rr -basekey "INI" -ini progress.ini'.    
    ELSE
        ASSIGN cCommandLine = 'n:\PROGRESS\bin\prowin32.exe -p n:\pro400\exec\EmailBlockS30 -pf live.pf -rr -basekey "INI" -ini progress.ini'.

    OS-COMMAND NO-CONSOLE VALUE(cCommandLine).

    FOR EACH EmailParams WHERE EmailParams.User-ID = OS-GETENV("winstationname") EXCLUSIVE-LOCK:
        DELETE EmailParams.
    END.

but we have our own sendemail.w that we wrote this will be a little different i think then what you are using


Here's out email block:

FIND EmailParams WHERE EmailParams.User-ID = OS-GETENV("winstationname") NO-LOCK.

DEFINE VARIABLE logon-name AS CHAR NO-UNDO.
DEFINE VARIABLE recip-name AS CHAR NO-UNDO.
/* DEFINE VARIABLE priority AS CHAR NO-UNDO. */
DEFINE VARIABLE message-text AS CHAR NO-UNDO. 
DEFINE VARIABLE attach-name AS CHAR NO-UNDO. 
DEFINE VARIABLE chSession AS COM-HANDLE. 
DEFINE VARIABLE chMessage AS COM-HANDLE. 
DEFINE VARIABLE chRecipient AS COM-HANDLE.
DEFINE VARIABLE chAttachment AS COM-HANDLE.
DEFINE VARIABLE chFiles     AS COM-HANDLE.
DEFINE VARIABLE numentries AS INTEGER NO-UNDO.
DEFINE VARIABLE X AS INTEGER NO-UNDO.

/*FIND CustLocation WHERE CustLocation.CustLocID = EmailParams.CustLocation NO-LOCK.*/

ASSIGN
     logon-name   = EmailParams.UserEmailAddress
     attach-name  = EmailParams.OutputDirectory + REPLACE(EmailParams.Report, " ", "")
     message-text = EmailParams.MessageText.
     /*priority   = EmailParams.Priority.*/

CREATE "MAPI.session" chSession. 
IF logon-name = "" THEN chSession:logon NO-ERROR. 
ELSE chSession:logon(logon-name, No, Yes, 0) NO-ERROR.   


chMessage = chSession:outbox:messages:add NO-ERROR.
chMessage:Subject = EmailParams.Subject NO-ERROR. 
chMessage:Type = "IPM.Note" NO-ERROR. 
chMessage:Text = message-text NO-ERROR. 
chMessage:Importance = IF priority = "Low" THEN 0 
ELSE IF priority = "High" THEN 2 
ELSE 1 NO-ERROR. 

/* Create multiple Recipient */ 
   

     numEntries = NUM-ENTRIES(EmailParams.Recep, ";").
        REPEAT X = 1 TO NUMENTRIES:
            chRecipient = chMessage:Recipients:Add NO-ERROR. 
            chRecipient:name = string(entry(x,EmailParams.Recep,";")) NO-ERROR. 
            chRecipient:Type = 1 NO-ERROR. 
            chRecipient:resolve NO-ERROR. 
END.
   
    numentries = NUM-ENTRIES(EmailParams.CC, ";").
        REPEAT X = 1 TO NUMENTRIES:
            chRecipient = chmessage:Recipients:ADD NO-ERROR.
            chRecipient:NAME = STRING(ENTRY(X,EmailParams.CC,";")) NO-ERROR.
            chRecipient:TYPE = 2 NO-ERROR.
            chRecipient:resolve NO-ERROR.
        END.

IF EmailParams.EmailTo = "C" THEN
DO:
    chRecipient = chMessage:Recipients:Add NO-ERROR. 
    chRecipient:name = "huston"NO-ERROR. 
    chRecipient:Type = 3 NO-ERROR. 
    chRecipient:resolve NO-ERROR. 
END.
    
    

ASSIGN 
    chMessage:TEXT = chMessage:TEXT + CHR(10)
    chFiles        = chMessage:Attachments:ADD()
    chFiles:NAME   = attach-name
    chFiles:SOURCE = attach-name.

/* Save and send message */ 
chMessage:Update NO-ERROR. 
chMessage:send(Yes, No, 0) NO-ERROR. 

IF attach-name <> "" THEN release object chAttachment NO-ERROR. 
release object chRecipient NO-ERROR. 
release object chMessage NO-ERROR. 
release object chSession NO-ERROR. 
RELEASE OBJECT chAttachment NO-ERROR.
RELEASE OBJECT chFiles NO-ERROR.


RELEASE EmailParams.

You might have to tweak it a bit but i hope that helps

 

[mwm]

Thanks anyway but I need to send the email through Unix, not Windows. I'm working on it using "uuencode". I think I have it solved.

 

[dayv2005]

Thanks anyway but I need to send the email through Unix, not Windows. I'm working on it using "uuencode". I think I have it solved.

Sorry for not reading into that, Any ways good luck with it