Using ExcelTOCI to Delete a User Profile

Below is an example of how to use “ExcelTOCI” to delete user profiles in a PeopleTools database.

The screenshots below are using PeopleTools 8.52 and Excel 2007.

Security Settings

• Make sure you have a permission list that grants full access to the WEBLIB_SOAPTOCI web library
• Make sure you have a permission list that grants access to the DELETE_USER_PROFILE Component Interface.

Clone ExcelToCI2007.xlsm

• Go to your PS_HOME directory and open the following file.
• PS_HOME\excel\ExcelToCI2007.xlsm
• Do a “save As” to DeleteUserProfile-ExcelToCI.xlsm

Enable Macros in excel

Most installations of Excel will block Macros by default. ExcelToCI relies on Macros to submit information to the database. Here are two screenshots that show you how to do that.

Setup the Connection Information

• Login to the database with your web browser to the PeopleTools databse where you want to run the delete.
• Once you login, copy the URL to your clipboard and paste it into a text document.
  • Mine looks like this: https://demohrms.cedarhillsgroup.com/psp/hrdemo/EMPLOYEE/HRMS/h/?tab=DEFAULT
  • This URL has almost every piece of information you need to fill out the connection information. It will be very specific on your network. We will look at my demo URL for this article but you will NOT be able to use the values that I use here from my URL.
• Open the “Connection Information” worksheet and fill in the following values.
• Web Server Machine Name - demohrms.cedarhillsgroup.com (see URL)
• Protocol : https (see URL)
• http port : 443 (Default port for https)
• Portal : EMPLOYEE (see URL)
• PeopleSoft Site Name : hrdemo (see URL)
• Node : HRMS (see URL)
• chunking factor : 1
• Error Threshold : 4
• Submit Blanks as Input : No
• Action : Update Data

Double check your URL and the values you input. If these are not setup correctly, it will not work.

Setup a new Template

• Open the “Template” worksheet
• Click on the “Add-ins” ribbon tab and click on “New Template”
• Fill in your PeopleSoft user name and password and fill in the Component Interface name of DELETE_USER_PROFILE.
  
• If everything went well with the connection and your security, you should be left with a worksheet that looks like this.
  
  • Do not change anything on this sheet. Do not change any color or move any fields or do any highlighting.
  • This is a cached structure of the target component interface that the Excel Macros know how to interpret.
• Follow this screenshot to select the input cell.
  
• After you do this, the cell that you selected should be a slighlty different color.
  

Create a Data Input

• Open the “Template” worksheet
• Click on the “Add-ins” ribbon tab and click on “New Data Input”
• Click yes when prompted if you want to delete all data.
  

Input Data

• The step from above should have transfered you to the “Data Input” sheet
• Input all the user profiles that you want to delete in the white cells. Do not change any other cell values.
• Once you have entered all your data click on the “Stage Date for Submission” add-in button.

Submit the data

• The step from above should have transfered you to the “staging and submission” sheet.
  • Do not make any changes to anything on this sheet.
• Click the “Submit data” add-in button and enter your PeopleSoft username and password.
  

Since we setup a chunking factor of one, a request will be sent to PeopleSoft for each and ever user you staged. This can take some time. It may seem like Excel is caught in an endless loop. Take a coffee break and wait for it to complete.

Once the data is submitted or we hit too many errors based on our error threshold, the macros should stop processing you are left with a sheet that may look like this.

Take note of the yellow cells that the macro highlighted. In this case, the Component Interface just had some warnings but the records still saved. If you see red cells then there was a hard error. The excel-to-ci macros make use of Excel “cell comments” to show you the component interface messages. You can hover your mouse over the cell to show the comments. Any popups or warning messages that you would see online in the web browser will appear here. These can be warnings or errors.

In my example, I just submitted two use ids and they both had warnings. It turns out for the DELETE_USER_PROFILE, all users will get this warning because on the page there is an “Are you sure you really want to delete this user” message. They display in component interface but there is no way for a CI to actually respond to a yes/no question. So the process just skips over those and they are reported in a cell comment and they can be ignored for this process.

• You can now use the “Post Results” which will copy the status column and cell comments back to the data input sheet.
• It is common when using excel to CI that you may have to do several interations of inputing data on the “data input” sheet and staging it. If you had a clean run with no errors you just need to submit it once.