Inserting Rows in Excel Spreadsheet with progress com handles

D

dougfl

New Member
I am having to insert rows in an existing Excel Spreadsheet between two rows that are already filled. I am at a loss as to how to do it with progress com handles.

Does anyone know? :confused:

Your help would be greatly appreciated.
 
dougfl said:
I am having to insert rows in an existing Excel Spreadsheet between two rows that are already filled. I am at a loss as to how to do it with progress com handles.

Does anyone know? :confused:

Your help would be greatly appreciated.
Click to expand...


Here's something i use i hope you can use it and use what you need from it.


Code: 
DEFINE VARIABLE vchExcel     AS COM-HANDLE   NO-UNDO.
DEFINE VARIABLE vchWorkBook  AS COM-HANDLE   NO-UNDO.
DEFINE VARIABLE vchWorkSheet AS COM-HANDLE   NO-UNDO.
DEFINE VARIABLE EmailBody    AS CHARACTER   NO-UNDO.
DEFINE VARIABLE EmailSubject AS CHARACTER   NO-UNDO.
DEFINE VARIABLE EmailTo      AS CHARACTER   NO-UNDO.
DEFINE VARIABLE EmailCC      AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cFileLoc AS CHARACTER   NO-UNDO.
DEFINE VARIABLE DispZip  AS CHARACTER   NO-UNDO.
DEFINE VARIABLE DispBDay AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cAnnv    AS CHARACTER   NO-UNDO.
DEFINE VARIABLE vRow     AS INTEGER     NO-UNDO.
CREATE "Excel.Application":U vchExcel.
ASSIGN 
    vchExcel:VISIBLE = FALSE
    vchWorkBook      = vchExcel:WorkBooks:ADD()
    vchWorksheet     = vchExcel:Sheets:ITEM(1)
    cFileLoc         = SESSION:TEMP-DIRECTORY + USERID(LDBNAME(1)) + string(TIME) + "EmployeeDump.xls".
ASSIGN
    vchExcel:Range("A1"):VALUE = "First Name"
    vchExcel:Range("B1"):VALUE = "Last Name"
    vchExcel:Range("C1"):VALUE = "Address"
    vchExcel:Range("D1"):VALUE = "City"
    vchExcel:Range("E1"):VALUE = "State"
    vchExcel:Range("F1"):VALUE = "Zip"
    vchExcel:Range("G1"):VALUE = "Date of Birth"
    vchExcel:Range("H1"):VALUE = "Date of Hire".
ASSIGN
    vchExcel:Range("A1"):FONT:BOLD = TRUE
    vchExcel:Range("B1"):FONT:BOLD = TRUE
    vchExcel:Range("C1"):FONT:BOLD = TRUE
    vchExcel:Range("D1"):FONT:BOLD = TRUE
    vchExcel:Range("E1"):FONT:BOLD = TRUE
    vchExcel:Range("F1"):FONT:BOLD = TRUE
    vchExcel:Range("G1"):FONT:BOLD = TRUE
    vchExcel:Range("H1"):FONT:BOLD = TRUE.

vRow = 1.
FOR EACH Employee NO-LOCK WHERE Employee.TemporaryYN = NO AND
                                Employee.Active = TRUE BY employee.LastName:
    IF LENGTH(Employee.Zip,"RAW") < 10 THEN
        DispZip = substring(REPLACE(Zip,"-",""),1,5).
    ELSE
        DispZip = Zip.
    ASSIGN
        vRow                                           = vRow + 1
        vchWorkSheet:Range("A":U + STRING(vRow)):VALUE = Employee.FirstName
        vchWorkSheet:Range("B":U + STRING(vRow)):VALUE = Employee.LastName
        vchWorkSheet:Range("C":U + STRING(vRow)):VALUE = Employee.Address1
        vchWorkSheet:Range("D":U + STRING(vRow)):VALUE = Employee.City
        vchWorkSheet:Range("E":U + STRING(vRow)):VALUE = Employee.State
        vchWorkSheet:Range("F":U + STRING(vRow)):VALUE = DispZip
        vchWorkSheet:Range("G":U + STRING(vRow)):VALUE = Employee.DateOfBirth
        vchWorkSheet:Range("H":U + STRING(vRow)):VALUE = Employee.HireDate.
END.
vchExcel:Cells:EntireColumn:AutoFit.
vchWorkBook:SaveAs(cFileLoc,,,,,,,).          

/*Email Part*/
EmailTo = "Durkin, Patty".
EmailCC = "Hughes, Heather".
ASSIGN 
    EmailSubject = "Employee Excel Export"
    EmailBody = "This was autogenerated From ProStaff ~n".    

RUN EmailClass.p(EmailTo,       /* TO string ";" Delimits */         
                 EmailCC,       /* CC string ";" Delimits */         
                 "",            /* BCC string ";" Delimits */        
                 EmailSubject,  /* Subject string */                 
                 EmailBody,     /* Body string  */                   
                 STRING(2),     /* Priority string 0-2 */            
                 FALSE,         /* Read Receipt Logical */           
                 FALSE,         /* Delivery Receipt Logical */       
                 cFileLoc,      /* Attachments sting ";" Delimits */ 
                 FALSE).        /* Safe Mode Logical */  
OS-DELETE VALUE(cFileLoc).
MESSAGE "Email was sent to" EmailTo 
    VIEW-AS ALERT-BOX INFO BUTTONS OK.
RELEASE OBJECT vchWorkSheet.
RELEASE OBJECT vchWorkBook.
RELEASE OBJECT vchExcel.
 
Dougfl, Dayv2005's post is a good example of com handles and Excel and if you wanted to insert a row, say between row 8 and 9, the code would be:-

vchWorksheet:Rows(9):Insert.
 
Out of interest which forum? Think it is always useful to know as many as you can and I only know three - this one, peg.com and comp.databases.progress.
 
Osborne said:
Out of interest which forum? Think it is always useful to know as many as you can and I only know three - this one, peg.com and comp.databases.progress.
Click to expand...


i use peg , his one, and psdn i will have to check out the comp one you listed never knew it existed.

thanks
 
You must log in or register to reply here.
Back
Top