topic Make excel sheet password protected through ABAP codeu0085 in Application Development and Automation Discussions

Make excel sheet password protected through ABAP codeu0085

Former Member — Thu, 03 Aug 2006 16:22:52 GMT

Hi Guys,

Hope you all are doin fine. I `m looking for the solution of my problem. Here’s the problem , I have a interface which send the excel spread sheet to app server. After that we FTP this file to another site . The other site guys open up the excel sheet and make it password protected and move it to another folder on same server. Now they want this automated . I want my interface program save the EXCEL file with password on application server. This password can be hard coded. In simple word<b> I want my excel sheet password protected</b> . How can I achive this task .Pl..help me guys

Cheers

Usman

Re: Make excel sheet password protected through ABAP codeu0085

RichHeilman — Thu, 03 Aug 2006 16:28:08 GMT

Are you already using OLE to create your excel file?

Regards,

Rich Heilman

Re: Make excel sheet password protected through ABAP codeu0085

Manohar2u — Thu, 03 Aug 2006 16:30:11 GMT

You can try with OLE. Try calling this OLE methods in ABAP. (Recorded VBA macro)

    ActiveWorkbook.SaveAs Filename:= _
        "C:Documents and SettingsManohar.ReddyDesktopBook1.xls", FileFormat:= _
        xlNormal, Password:="manohar", WriteResPassword:="manohar", _
        ReadOnlyRecommended:=False, CreateBackup:=False

Regds

Manohar

Re: Make excel sheet password protected through ABAP codeu0085

RichHeilman — Thu, 03 Aug 2006 16:37:16 GMT

That's right, all you need to do is pass the password when saving. Please check the sample program.



report zrich_0001.


include ole2incl.

data: e_sheet type ole2_object.
data: e_appl  type ole2_object.
data: e_work  type ole2_object.
data: e_cell  type ole2_object.
data: e_wbooklist type ole2_object.

data: field_value(30) type c.

parameters: p_file type localfile default 'C:Test.xls'.

start-of-selection.


* Start the application
  create object e_appl 'EXCEL.APPLICATION'.
  set property of e_appl 'VISIBLE' = 0.

* Open the file
  call method of e_appl 'WORKBOOKS' = e_wbooklist.
  get property of e_wbooklist 'Application' = e_appl .
  set property of e_appl 'SheetsInNewWorkbook' = 1 .
  call method of e_wbooklist 'Add' = e_work .
  get property of e_appl 'ActiveSheet' = e_sheet .
  set property of e_sheet 'Name' = 'Test' .





* Write data to the excel file
  do 20 times.

* Create the value
    field_value  = sy-index.
    shift field_value left deleting leading space.
    concatenate 'Cell' field_value into field_value separated by space.


* Position to specific cell  in  Column 1
    call method of e_appl 'Cells' = e_cell
           exporting
                #1 = sy-index
                #2 = 1.
* Set the value
    set property of e_cell 'Value' = field_value .


* Position to specific cell  in  Column 2
    call method of e_appl 'Cells' = e_cell
           exporting
                #1 = sy-index
                #2 = 2.
* Set the value
    set property of e_cell 'Value' = field_value .


* Position to specific cell  in  Column 3
    call method of e_appl 'Cells' = e_cell
           exporting
                #1 = sy-index
                #2 = 3.
* Set the value
    set property of e_cell 'Value' = field_value .

  enddo.



** Close the file
  get property of e_appl 'ActiveWorkbook' = e_work.
<b>  call method of e_work 'SAVEAS'</b>
        exporting
            #1 = p_file
            #2 = 1           "" Don't ask me when closing
<b>            #3 = 'rich'    "" Password
            #4 = 'rich'.     "" Reserved for Password</b>

  call method of e_work 'close'.

* Quit the file
  call method of  e_appl  'QUIT'.

* Free them up
  free object e_cell.
  free object e_sheet.
  free object e_work.
  free object e_wbooklist.
  free object e_appl.

Regards,

Rich Heilman

Re: Make excel sheet password protected through ABAP codeu0085

Former Member — Tue, 03 Oct 2006 05:31:54 GMT

Hi All,

I am Working on a report in which i have to download data from SAP to a Excel file and create different tabs based on certain condition ,I am using OLE to achieve it but the problem in the control tab of the report is I have to calculate the difference between the two columns which will be dynamically decided from the program .

i.e.

Magnitude column will be left empty for the users to key in the value and the Total column will be filled from the program now another column is created which will be the Difference column and the formula have to be set to calculate the difference between Magnitude and Total Column.

Request you to let me know how to set a formula in Excel using OLE.

Thanks in Advance,

Regards,

Akshat Jain