OLE ABAP Code with Password Protected Excel Sheet

Objective

• Password Store in ‘Y’ Table for the Respective User in Encrypted format .
• To Export Excel Sheet with Password Protected(i.e. Decrypted format) both Cell and Worksheet.

• Upload some User ID’s and it’s respective Password into Custom “Y” table.

Screen Shot # 0

Screen Shot # 1

User Name               : SMRUTI

Encrypted Password: QUJBUEA0MA==

Decrypted Password: ABAP@40

• In your Custom Upload Program use below code for Encryption of Password , Which will store in above ‘Y’ Table.

  • Using CALL METHOD CL_HTTP_UTILITY=>IF_HTTP_UTILITY~ENCODE_BASE64 Encrypt the password text

Screen Shot # 2

Note:

PASSWORD    : ABAP@40            ( Decrypted Password )

PASSWORD1  : QUJBUEA0MA== (Encrypted Password )

• Now OLE ABAP Code using OLE2_OBJECT.

Screen Shot # 3

Screen Shot # 4

Screen Shot # 5

Create Object for Excel using EXCEL.APPLICATION .

Screen Shot # 6

Screen Shot # 7

Screen Shot # 8

Screen Shot # 9

Using  CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT to Clipboard.

Screen Shot # 10

Using  CALL METHOD CL_HTTP_UTILITY=>IF_HTTP_UTILITY~DECODE_BASE64 decrypt the encrypted text (i.e. password) .

Screen Shot # 11

• Now Output

         Enter your Excel File Name with Path and User Name as a below Screen Shot.

        

        Note: If User Name in Lower Case then Click on Check box

Screen Shot #12

After pressing Execute button or F8 Key you got Exported data( i.e. Password Protected ) in Excel Sheet , look as below Screen Shot .

Screen Shot # 13

Now Check is your Worksheet, if you want to edit the Cell in work sheet then one password pop window show to ask for password Check below Screen Shot.

Here I want to edit the Division as Per below Screen Shot , then you see one Password Pop-up window appear for asking password , Until Correct Password you unable to edit the data

Screen Shot # 14

Now I am try to enter wrong password see below screen shot.

Screen Shot # 15

Reason of this below OLE ABAP Code line number 131 to 136 .

Screen Shot # 16

Then I Enter Correct Password now Check below screen shot and now I am able to edit only where already data are available, mean within Range (i.e. A1:D11)

Screen Shot # 17

Suppose you want add One row in Exported Excel Sheet that also protected by Password, Check below Screen Shot.

Screen Shot # 18

Now you Un-Protect the Sheet using Un-protect Sheet in Review (i.e. here I using MS-Excel 2010)

Screen Shot # 19

After pressing above Unprotected Sheet icon, then you pass password for Unprotected the Sheet.

Screen Shot # 20

Reason of below OLE ABAP Code , Call below method “PROTECT” for Password

Screen Shot # 21

Screen Shot # 22

Note : Here i am using both Cell and Worksheet using one Password (i.e. fetch Encrypted Password from “Y” Table then Decrypt before assign to Excel Sheet ), you can take different password as per your requirement .

Source Code Available in Wiki link SAP Community Network Wiki – Code Gallery – ABAP – OLE2 Code with Dynamic Password Protected usin…

Regard’s

Smruti