Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

RMPS - How to fill Excel cells from a record.

olivier_muff
Explorer

‎04-25-2006 11:01 PM

0 Kudos

Hello all. Here I post the first time with one general and one specific question.

1) Should there be a RM or RMPS Forum or does it all fall under Programming?

2) Under Records Management (Specifically RMPS), we have WORD and EXCEL document templates. When these templates are used with a specific record, we want them to be populated with attributes from the record. This works fine for WORD templates (using Test Form Fields, Bookmarks and linked fields). However, I cannot get it to work with EXCEL. Can someone tell me how to do this? What do I need to do in EXCEL? I have tried providing Names for the cells and setting these cells to linked but the cells do not fill.

Thank you,

Olivier

4 REPLIES 4

former_member181966
Active Contributor

‎04-26-2006 12:04 AM

0 Kudos

welcome to SDN,

Please see the following program .. SAP to Excel USING OLE .

REPORT Y_EXCEL_MULTIPLE.

parameters: p_fname like RLGRAP-FILENAME

default 'C:\temp\testNN.xls'.

data: fname like p_fname,

kn like sy-repid.

data: cnt type i value 0.

data: sheetname(10) value 'TEST ',c_row type i,

scnt type i,

val(20), wb(2).

parameters: p_exvis as checkbox default 'X',

p_workbk(2) type p default '01',

p_wsheet(2) type p default '01'.

CONSTANTS: OK TYPE I VALUE 0.

INCLUDE OLE2INCL.

DATA: EXCEL TYPE OLE2_OBJECT,

WORKBOOK TYPE OLE2_OBJECT,

SHEET TYPE OLE2_OBJECT,

CELL TYPE OLE2_OBJECT,

CELL1 TYPE OLE2_OBJECT,

COLUMN TYPE OLE2_OBJECT,

RANGE TYPE OLE2_OBJECT,

BORDERS TYPE OLE2_OBJECT,

button TYPE OLE2_OBJECT,

int TYPE OLE2_OBJECT,

FONT TYPE OLE2_OBJECT,

ROW TYPE OLE2_OBJECT.

data: application type ole2_object,

book type ole2_object,

books type ole2_object.

data: ole_book TYPE ole2_object.

do p_workbk times.

move p_fname to fname.

unpack sy-index to wb.

replace 'NN' with wb into fname.

*

perform create_EXCEL.

  • create sheets and save

perform sheet.

perform save_book.

enddo.

write: ' Done'.

----

  • FORM create_excel *

----

  • ........ *

----

form create_excel.

CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

if sy-subrc ne 0.

write: / 'No EXCEL creation possible'.

stop.

endif.

set property of EXCEL 'DisplayAlerts' = 0.

CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK .

  • Put Excel in background

if p_exvis eq 'X'.

SET PROPERTY OF EXCEL 'VISIBLE' = 1.

else.

SET PROPERTY OF EXCEL 'VISIBLE' = 0.

endif.

  • Create worksheet

set property of excel 'SheetsInNewWorkbook' = 1.

call method of workbook 'ADD'.

endform.

----

  • FORM save_book *

----

  • ........ *

----

form save_book.

get property of excel 'ActiveSheet' = sheet.

free object sheet.

free object workbook.

GET PROPERTY OF EXCEL 'ActiveWorkbook' = WORKBOOK.

call method of workbook 'SAVEAS' exporting #1 = p_fname #2 = 1.

call method of workbook 'CLOSE'.

call method of excel 'QUIT'.

free object sheet.

free object workbook.

free object excel.

endform.

----

  • FORM sheet *

----

  • ........ *

----

form sheet.

do p_wsheet times.

unpack sy-index to sheetname+5(2).

if sy-index gt 1.

CALL METHOD OF EXCEL 'WORKSHEETS' = sheet.

call method of sheet 'ADD'.

free object sheet.

endif.

scnt = sy-index.

call method of excel 'WORKSHEETS' = SHEET EXPORTING #1 = scnt.

call method of sheet 'ACTIVATE'.

SET PROPERTY OF SHEET 'NAME' = sheetname.

free object sheet. "OK

perform fill_sheet.

*

CALL METHOD OF EXCEL 'Columns' = COLUMN.

CALL METHOD OF COLUMN 'Autofit'.

free object COLUMN.

*

  • call method of sheet 'BUTTON' = button.

  • call method of button 'ADD'.

  • set property of button 'fmButtonStyle' = 0.

  • exporting #1 = '96.75' #2 = '372' #3 = '123.75' #4 = '12'.

  • set property of button 'Characters' = 'ButtonTest'.

free object button.

free object font.

free object int.

free object cell.

free object: cell1.

free object range.

free object borders.

free object: column, row.

enddo.

free object font.

free object int.

free object cell.

free object cell1.

free object range.

free object borders.

free object column.

free object row.

free object sheet.

endform.

----

  • FORM border *

----

  • ........ *

----

  • --> we *

----

form border using we.

*left

call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

set property of borders 'LineStyle' = '1'.

set property of borders 'WEIGHT' = we. "4=max

free object borders.

  • right

call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

set property of borders 'LineStyle' = '2'.

set property of borders 'WEIGHT' = we.

free object borders.

  • top

call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

set property of borders 'LineStyle' = '3'.

set property of borders 'WEIGHT' = we.

free object borders.

  • bottom

call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

set property of borders 'LineStyle' = '4'.

set property of borders 'WEIGHT' = we.

  • set property of borders 'ColorIndex' = 'xlAutomatic'.

free object borders.

endform.

----

  • FORM border2 *

----

  • ........ *

----

  • --> we *

----

form border2 using we.

*left

call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

set property of borders 'LineStyle' = '5'.

set property of borders 'WEIGHT' = we. "4=max

free object borders.

  • right

call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

set property of borders 'LineStyle' = '6'.

set property of borders 'WEIGHT' = we.

free object borders.

  • top

call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

set property of borders 'LineStyle' = '7'.

set property of borders 'WEIGHT' = we.

free object borders.

  • bottom

call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

set property of borders 'LineStyle' = '8'.

set property of borders 'WEIGHT' = we.

  • set property of borders 'ColorIndex' = 'xlAutomatic'.

free object borders.

endform.

----

  • FORM border3 *

----

  • ........ *

----

  • --> we *

----

form border3 using we.

*left

call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

set property of borders 'LineStyle' = '9'.

set property of borders 'WEIGHT' = we. "4=max

free object borders.

  • right

call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

set property of borders 'LineStyle' = '10'.

set property of borders 'WEIGHT' = we.

free object borders.

  • top

call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

set property of borders 'LineStyle' = '11'.

set property of borders 'WEIGHT' = we.

free object borders.

  • bottom

call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

set property of borders 'LineStyle' = '12'.

set property of borders 'WEIGHT' = we.

  • set property of borders 'ColorIndex' = 'xlAutomatic'.

free object borders.

endform.

----

  • FORM fill_cell *

----

  • ........ *

----

  • --> color *

  • --> pattern *

----

form fill_cell using color pattern.

call method of cell 'INTERIOR' = int.

set property of int 'ColorIndex' = color.

set property of int 'Pattern' = pattern.

free object int.

endform.

----

  • FORM font *

----

  • ........ *

----

  • --> bold *

  • --> size *

----

form font using bold size.

call method of CELL 'FONT' = font.

set property of font 'BOLD' = bold.

set property of font 'SIZE' = size.

free object font.

endform.

----

  • FORM fill_sheet *

----

  • ........ *

----

form fill_sheet.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'A1'.

perform font using 1 '14'.

SET PROPERTY OF CELL 'VALUE' = 'Counter'.

perform fill_cell using '15' '1'.

perform border using '2'.

free object cell.

val = 'Workbook-Count'.

move wb to val+16.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'B1'.

SET PROPERTY OF CELL 'VALUE' = val.

perform fill_cell using '14' '1'.

perform border using '4'.

free object cell.

val = 'Sheet-Count'.

unpack sy-index to val+12.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'C1'.

SET PROPERTY OF CELL 'VALUE' = val.

perform fill_cell using '12' '1'.

perform border using '4'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E3'.

perform border using '1'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E5'.

perform border using '2'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E7'.

perform border using '3'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E9'.

perform border using '4'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F3'.

perform border2 using '1'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F5'.

perform border2 using '2'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F7'.

perform border2 using '3'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F9'.

perform border2 using '4'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G3'.

perform border3 using '1'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G5'.

perform border3 using '2'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G7'.

perform border3 using '3'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G9'.

perform border3 using '4'.

free object cell.

val = 'ROW-Count'.

do 19 times.

c_row = sy-index + 1.

unpack c_row to val+12(4).

CALL METHOD OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 2.

SET PROPERTY OF CELL1 'VALUE' = val.

free object cell1.

CALL METHOD OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 4.

SET PROPERTY OF CELL1 'VALUE' = val.

free object cell1.

enddo.

endform.

----

  • You find SAP OLE programs under development Class 'SOLE' *

  • *

  • MSTAPPL Table Maintenance APPL *

  • RSOLEDOC Document list *

  • RSOLEIN0 OLE Load Type Information *

  • RSOLEINT Type Info Loaded *

  • RSOLETI0 OLE Object Browser *

  • RSOLETI1 OLE Object Browser *

  • RSOLETI2 OLE Object Browser *

  • RSOLETI3 F4 Help For OLE Objects *

  • RSOLETT1 OLE 2.0 Automation Demo Program *

  • *

  • Transactions: *

  • SOLE *

  • SOLO - List of OLE applcations with loaded type info *

  • *

  • *

  • You will find the decription of possible objects and methods in the *

  • windows help file for excel. *

----

may be this`ll give you some hint.

Thanks

Saquib

olivier_muff
Explorer
In response to former_member181966

‎04-26-2006 7:03 AM

0 Kudos

Thanks for the valuable input Saquib!

I understand much of it from my other IT experience but I am not completely sure how this fits in with Records Management (RM). We have a dozen Excel Templates developed by the customer. When the customer wants to create a document, he will select one of his templates and we will create the xls document and populate appropriate fields.

How do I modify the code to pull up the excel doc that the customer wants as the template?

How do I pass the document name to this function? or do I modify this section of code here?

----

  • FORM create_excel *

----

  • ........ *

----

form create_excel.

CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

if sy-subrc ne 0.

write: / 'No EXCEL creation possible'.

stop.

endif.

set property of EXCEL 'DisplayAlerts' = 0.

CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK .

  • Put Excel in background

if p_exvis eq 'X'.

SET PROPERTY OF EXCEL 'VISIBLE' = 1.

else.

SET PROPERTY OF EXCEL 'VISIBLE' = 0.

endif.

  • Create worksheet

set property of excel 'SheetsInNewWorkbook' = 1.

call method of workbook 'ADD'.

endform.

olivier_muff
Explorer
In response to former_member181966

‎08-20-2007 9:42 PM

0 Kudos

Time for cleanup and an answer my own question.

Records management for public service (RMPS) provides an exit (event code) that is triggered and used for filling form fields in Word Documents when a Word document is created (from a template that is registered in the RM system). Unfortunately, this exit is only activated when a Word document is created. If the document is another type, such as Excel, the exit is never triggered and there is no opportunity to fill fields in the Excel document. May be SAP will add this feature. If the exit would trigger, I could fill fields in an Excel sheet myself with ABAP code since the document is opened on creation in the standard RMPS view.

Hope this will help someone.

Olivier

Former Member

‎10-23-2007 2:50 PM

0 Kudos

Although all signs indicate that it is not possible to fill a Excel template with data from a case or document, I nevertheless rather easy managed to do exactly that. How ? Just create a template in the usual way, but choose Excel offcourse as the application.

To insert SAP data, put the cursor on the right cell, click in the menu on Insert / Name / Define. Input the name of the SAP case attribute (e.g. CASE_TYPE) and press OK.

That's all ! You don't have to go to File / Properties/ Custom tab to link the SAP attribute with the formfield.

Nevertheless, I could not find any proof that SAP really supports this. Not on OSS, SDN or SAPNET nor is this technique explained in SAP courses. So using this technique is at your own risk.