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.
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
