Deleting Rows in ExcelToCI
By Chris Malek | Wed, Oct 13, 2010
I recently had a situation where I needed to remove a security role from a large amount of users. While I could have done this with SQL doing a delete something like this.
DELETE FROM PSROLEUSER WHERE ROLENAME = 'ROLE TO REMOVE' AND ROLEUSER = 'THE_USER';
This would not trigger the integration broker messages that publish out to all the subscribing databases. So I was going to use the ExcelTOCI to remove the role. I had actually never done a delete before with ExcelToCI and I realized that the delivered ExceltoCI macros are not coded for a row delete. However, the excelTOCI just submits SOAP messages that end up getting parsed by the SOAPtoCI application package which looks for a “CiNodeAction” attribute and it will process a row delete. You can see the code in the SOAPTOCI:SOAPTOCI application class.
You just have to modify the VBA Macros that actually create the SOAP XML that get submitted to the Weblib.
- While you are in your ExceltoCI template go to Tools | Macros | Visual Basic Editor
- Open the StagingAndSubmission Module
- In the WriteCollectionHeader function do the following
You will see a case statement “Select Case lScrollLevel”
Change the “Case 1” statement
sXmlLine = sIndent & "<" & sXmlTag & ">"
sXmlLine = sIndent & "<" & sXmlTag & " CINodeAction=""DELETE"">"
This worked for the USER_PROFILE component interface because the ROLEUSER table shows up at level1. If you need to delete a level 2 or 3 row then you need to basically make the same change on those Case statements.
This was a quick and dirty solution and you would probably want to make sure you did not give this modified macro to anyone because you could do some damage in the database.