Crack VBA excel password

Step 1:

To crack the password of an VBA Macro / coding first download an HEX Editor, its available from the site http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm

Step 2:

Close the workbook and open the workbook file in the hex editor.
Find the string "DPB" and change it to "DPx" (x is in small letter).
Save the file.

Step 3:

Open the workbook and click OK until the workbook is open (one or more dialogs are displayed describing various problems with the VBA project).

Press ALT+F11, choose the menu command Tools->VBAProject Properties, navigate to the Protection tab, and change the password but do not remove it (note the new password). Save, close,

Step 4:

Re-open the workbook. Press ALT+F11 and enter the new password.

Choose Tools->VBAProject Properties, navigate to the Protection tab, and remove the password. Save the workbook.

Now open the same workbook, You can use the password that you already given.

Aggregate multiple rows of data into 1 column and insert into multiple columns

There will be scenario where we want to extract multiple rows data into one column and may insert column data for multiple columns in a single or multiple columns. 
This can be done using listagg and substr functions using oracle.

Below SQL will extract multiple rows of compensation in a single column, each value separated by delimiter comma (,).

 SELECT COUNT(*) c
,comp.emplid
,comp.effdt
,COMP.EMPL_RCD
,COMP.EFFSEQ
, listagg (comp.comp_ratecd||','||comp.comprate, ',') WITHIN
GROUP (
ORDER BY comp.comp_ratecd) comprates
FROM PS_COMPENSATION COMP
, PS_COMP_RATECD_TBL B
WHERE COMP.COMP_RATECD= B.COMP_RATECD
AND B.EFF_STATUS = 'A'
AND B.COMP_BASE_PAY_SW ='N'
AND B.EFFDT=(
SELECT MAX(B1.EFFDT)
FROM PS_COMP_RATECD_TBL B1
WHERE B.COMP_RATECD=B1.COMP_RATECD
AND b1.effdt <=COMP.EFFDT)
GROUP BY comp.emplid,comp.effdt,COMP.EMPL_RCD,COMP.EFFSEQ

And using substr and instr we can split one column data to multiple columns.
Below sql will show how multiple rows of data are extracted into 1 column and insert into multiple columns (10 columns).
SELECT A.EMPLID 
 ,A.EFFDT 
 ,a.EMPL_RCD 
 ,A.EFFSEQ 
 , NVL((CASE WHEN A.C >= '1' THEN substr(a.comprates  ,1 
 ,instr(a.comprates ,','  ,1  ,1)-1) ELSE '' END)  ,' ') COL1 
 , NVL((CASE WHEN A.C=0 THEN '' 
WHEN A.C=1 THEN substr(a.comprates 
 ,instr(a.comprates ,','  ,1  ,1)+1 
 ,LENGTH(a.comprates)-instr(a.comprates  ,','  ,1  ,1)+1)
 WHEN A.C > '1' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,1)+1  ,instr(a.comprates  ,','  ,1  ,2)-(instr(a.comprates ,','  ,1  ,1)+1)) END)  ,0) COL2 
 , NVL((CASE WHEN A.C<=1 THEN '' 
WHEN A.C >= '2' THEN substr(a.comprates 
 ,instr(a.comprates ,','  ,1  ,2)+1  ,instr(a.comprates  ,','  ,1  ,3)-(instr(a.comprates ,',' ,1 
 ,2)+1)) END)  ,' ') COL3 
 , NVL((CASE WHEN A.C=0 THEN '' 
WHEN A.C=1 THEN '' WHEN A.C ='2' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,3)+1  ,LENGTH(a.comprates)-instr(a.comprates  ,','  ,1 
 ,3)+1) 
WHEN A.C > '2' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,3)+1 
 ,instr(a.comprates  ,','  ,1  ,4)-(instr(a.comprates  ,','  ,1  ,3)+1)) END)  ,0) COL4 
 , NVL((CASE 
WHEN A.C<=2 THEN '' WHEN A.C >= '3' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,4)+1  ,instr(a.comprates  ,','  ,1  ,5)-(instr(a.comprates  ,','  ,1 
 ,4)+1)) END)  ,' ') COL5 
 , NVL((CASE 
WHEN A.C=0 THEN '' WHEN A.C=1 THEN '' WHEN A.C=2 THEN '' WHEN A.C ='3' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,5)+1  ,LENGTH(a.comprates)-instr(a.comprates 
 ,','  ,1  ,5)+1) 
WHEN A.C > '3' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,5)+1  ,instr(a.comprates  ,','  ,1  ,6)-(instr(a.comprates  ,','  ,1  ,5)+1)) END)  ,0) COL6 
 , NVL((CASE WHEN A.C<=3 THEN '' 
WHEN A.C >= '4' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,6)+1  ,instr(a.comprates  ,','  ,1  ,7)-(instr(a.comprates  ,','  ,1 
 ,6)+1)) END)  ,' ') COL7 
 , NVL((CASE WHEN A.C=0 THEN '' WHEN A.C=1 THEN '' WHEN A.C=2 THEN '' WHEN A.C=3 THEN '' WHEN A.C ='4' THEN substr(a.comprates 
 ,instr(a.comprates ,','  ,1  ,7)+1  ,LENGTH(a.comprates)-instr(a.comprates  ,','  ,1  ,7)+1) 
WHEN A.C > '4' THEN substr(a.comprates  ,instr(a.comprates  ,','  ,1  ,7)+1  ,instr(a.comprates 
 ,','  ,1  ,8)-(instr(a.comprates  ,','  ,1  ,7)+1)) END)  ,0) COL8 
 , NVL((CASE WHEN A.C<=4 THEN '' WHEN A.C >= '5' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,8)+1  ,instr(a.comprates  ,','  ,1  ,9)-(instr(a.comprates  ,','  ,1  ,8)+1)) END)  ,' ') COL9 
 , NVL((CASE WHEN A.C=0 THEN '' WHEN A.C=1 THEN '' WHEN A.C=2 THEN '' WHEN A.C=3 THEN '' WHEN A.C=4 THEN '' WHEN A.C >= '5' THEN substr(a.comprates 
 ,instr(a.comprates  ,','  ,1  ,9)+1  ,9) END)  ,0) COL10 
  FROM ( 
 SELECT COUNT(*) c 
 ,comp.emplid 
 ,comp.effdt 
 ,COMP.EMPL_RCD 
 ,COMP.EFFSEQ 
 , listagg (comp.comp_ratecd||','||comp.comprate 
 , ',') WITHIN 
  GROUP ( 
  ORDER BY comp.comp_ratecd) comprates 
  FROM PS_COMPENSATION COMP 
  , PS_COMP_RATECD_TBL B 
 WHERE COMP.COMP_RATECD= B.COMP_RATECD 
   AND B.EFF_STATUS = 'A' 
   AND B.COMP_BASE_PAY_SW ='N' 
   AND B.EFFDT=( 
 SELECT MAX(B1.EFFDT) 
  FROM PS_COMP_RATECD_TBL B1 
 WHERE B.COMP_RATECD=B1.COMP_RATECD 
   AND b1.effdt <=COMP.EFFDT) 
  GROUP BY comp.emplid,comp.effdt,COMP.EMPL_RCD,COMP.EFFSEQ) A, PS_xx_stage_tbl STG 
 WHERE a.emplid=STG.EMPLID 
   AND a.EFFDT=STG.EFFDT 
   AND A.empl_rcd = STG.empl_rcd 
   AND A.effseq = STG.effseq  

Generating xls file in PeopleSoft using OLE Automation

Generating xls or formatted excel file is tedious with SQR.

But we can generate them using Peoplecode with OLE Automation.

We need to create a Excel object and then we can use almost all VBA commands in PeopleCode with Peoplesoft notations, Remember vbconstants like vbblue will not be used here.

Also Make sure you are running this code in windows server with excel installed.

Local object &workApp;Local object &workBook;
Local object &workSheet1, &workSheet2, &workSheet3, &workSheet4;
try
 &workApp = CreateObject("COM", "Excel.Application"); 
 ObjectSetProperty(&workApp, "Visible", False);
 &workBook = &workApp.WorkBooks.Add(); 
 &workSheet1 = &workBook.WorkSheets(1); 
 &workSheet2 = &workBook.WorkSheets(2);  
 &workSheet3 = &workBook.WorkSheets(3); 
 &workSheet3.activate();  
 &workSheet4 = &workBook.Worksheets.Add(4);

 Rem - Name worksheets   
 &workSheet1.name = "Hires";  
 &workSheet2.name = "Terminations"; 
 &workSheet3.name = "Contract Changes"; 
 &workSheet4.name = "New";   

 Rem - Assign values in different worksheets  
 &workSheet1.Cells(1, 1).Value = "Sample1";  
 &workSheet1.Cells(1, 2).Value = "Heading2";    
 &workSheet1.rows(1).Font.Bold = True;  
 &workSheet1.rows(1).Font.colorindex = 5;    
 &workSheet1.rows(1).Interior.colorindex = 3;   
 &workSheet1.rows(1).Font.size = 8;

 &workSheet2.Cells(1, 1).Value = "Sample2 for big text";  
 &workSheet2.Cells(1, 2).Value = "Sample2 for big text, even big";  
 &workSheet2.rows(1).Font.size = 10; 
 &workSheet2.rows(1).Font.Name = "Verdana";  
 &workSheet2.Columns.AutoFit(); 
 &workSheet3.Cells(1, 1).Value = "Sample3"; 
 &workSheet4.Cells(1, 1).Value = "Sample4"; 
 SQLExec("select PRCSOUTPUTDIR from psprcsparms where prcsinstance= 
 (select max(prcsinstance) from psprcsrqst where prcsname='AENAME')", 
 &path); 
 &sFileName = &path | "\ExportData2Excel.xls";   
 &workApp.ActiveWorkBook.SaveAs(&sFileName, 18);
 &workApp.Quit();  
 MessageBox(0, "", 0, 0, "Information has been exported to an Excel file """ |  
 &sFileName | """.");  catch Exception &exp  
throw
 CreateException(1000, 22407, "Error saving MS Excel file." | &exp);
end-try;

Process Groups in PeopleSoft

The process groups are not stored in any setup table of its own.
They are stored in the table: PRCSDEFNGRP and PRCSJOBGRP where we store the process and job definitions.
If you take a look a look at the prompt for PRCSGRP field in those tables, it is a 'Prompt with no edit', so u can keep adding new Process groups on the fly, on process and job definition components (Page: Process Definition Options)

Significance of the process group:

They are linked with the user ID through a permission list. 

On the permission list setup, in the "Process tab", you can link the process groups.
This plays very important role in securing the processes and jobs in the system. Process groups to users are tied as below

Process Group ->Permission list -> Roles->User profiles

To find which permission lists have access to which process groups use the following query:

select * from PSAUTHPRCS order by CLASSID, PRCSGRP;

Below screens will show how the new process group is created and mapped through user profile.

Union / Minus operator in SQR

There are different scenarios where we will be using Union / Union all / Intersect / Minus operator in sqr.
below is the example of using union in sqr

begin-select 
field-a &fld-a 
field-b &fld-b 
from tbl1 
where ....
union 
select field-c, field-d from tbl2 where....
end-select

Delete PeopleSoft Query

There could be different reasons we would like to delete a query from the database. Below are the SQL that are needed to delete a query from database.

   DELETE FROM PSQRYDEFN WHERE QRYNAME='Queryname';
   DELETE FROM PSQRYSELECT WHERE QRYNAME='Queryname';
   DELETE FROM PSQRYRECORD WHERE QRYNAME='Queryname';
   DELETE FROM PSQRYFIELD WHERE QRYNAME='Queryname';

The macro cannot be found or has been disabled while accessing BI publisher in MS Word

Recently I have installed XML publisher in my local client workstation.

When I open Microsoft word and try to access Oracle BI publisher I got below error

From different google sources below are two most common errors and how should Word be setup correctly

Templates should correctly enabled

Even after above 2 actions my issue is not solved.

Solution to above issue is

Solution 1:

Search for MSComctlLib.exd in your local drive and rename all the existence, such as MSComctlLib.exd_bak.

usually the file will occur in %USERPROFILE%Application Data\Microsoft\Forms or C:\Users\ramu\AppData\Roaming\Microsoft\Forms

now re-open microsoft word, this will create new file of MSComctlLib.exd  and try accessing BI publisher.

if above soltion doesnot solve your issue. try below

Solution 2:

For 64-bit operating systems, type the following in command prompt (Run command prompt as administrator, otherwise you will get error code 0x80004005): 

Regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"

For 32-bit operating systems, type the following:

Regsvr32 "C:\Windows\System32\MSCOMCTL.OCX"

open the Microsoft word and access BI Publisher now.