Clone Users ExcelToCI

Clone Users ExcelToCI

At times, you might need to mass create a number of users with the same attributes. PeopleSoft provides the ability to copy a user profile from an existing user using PeopleTools > Security > User Profiles > Copy User Profiles (the USER_SAVEAS component). This will copy the security, and optionally the type ID information from a base user in the system as a new user.

This article expands on this functionality by:

  • Creating a component interface (CI) based on the USER_SAVEAS component and then
  • Using ExcelToCI to clone a number of users with the same attributes as the base user.

Creating the Component Interface

In application designer, select File > New > Component Interface. This will based off the USER_SAVEAS component. There are few things you will want to tidy up in this component interface, mainly to make it easier to use:

  • Change GETKEYS, OPRID to BaseUserID (double-click on OPRID and change the name).
  • Remove PROPERTIES, OPRID_0 (right-click, delete)
  • Change PROPERTIES, OPRID to NewUserID (double-click on OPRID and change the name)

Here's how the component interface should look when you are done:

user-saveas-component-interface.png

I've left the component interface name as USER_SAVEAS but use conventions that are appropriate for your organisation.

ExcelToCI

ExcelToCI is essentially an Excel spreadsheet with VBA macros that allow you to connect to a PeopleSoft system through a component interface.

The ExcelToCI spreadsheet and macro files are located on your file server under PS_HOME\excel. The two files you want (which you can copy to your machine) are ExcelToCI.xls and ExcelToCI.vbs.

Once you have copied these files locally, open ExcelToCI.xls. Make sure you enable macros if you are prompted as these need to be enabled for ExcelToCI to work.

There are 5 tabs (down the bottom) which work like a wizard:

  • Step 1: Set up your connection information
  • Step 2: Create a new template based on your component interface
  • Step 3: You specify the data input you want to load
  • Step 4: You stage your data for submission
  • Step 5: You submit your data, and if there are any errors, post the results for review

Connection Information

In the Connection Information tab, the key things you will need to specify are:

  • Web server machine name
  • Protocol
  • HTTP port
  • Your Local Node

For more information about this part, please see this article by ERPAssociates which goes through how to get this information.

Set the action as UPDATEDATA. This is the action you will need to use in this case.

Template

Switch to the template tab. In Excel 2007, click on the Add-Ins menu item, and then New Template. You will be prompted to login.

  • Your PeopleSoft User ID (e.g. PS)
  • Your PeopleSoft User ID password (e.g. PS)
  • The name of your component interface, e.g. USER_SAVEAS
exceltoci-usersaveas-connection.png

Hopefully the connection works, and your component interface template is loaded. If not, go back to the connection information to troubleshoot.

Once your template has loaded, you will need to do two more things:

  • Choose Select All Input Cells from the Add Ins Menu
  • Rename UserID field of the first column to BaseUserID (to match the property) and UserID field of the second column to NewUserID (to match the property). This will help you figure out which column is which in the data input. The BaseUserID is the user that exists in the system that you are cloning. The NewUserID is the new user to be created (cloned).

This is how your spreadsheet template should look:

exceltoci-usersaveas-template.png

Once you are happy, select New Data Input from the add ins.

Data Input

Use the Data Input tab to enter the users you want to create. A few points to note:
* BaseUserID is your existing user you are cloning from
* NewUserID is the new user ID you are creating
* Operator password is in plaintext and will be encrypted
* Description is the description of the user
* Confirm password is the same plaintext password
* Copy ID type information is Y or N. Y will copy the employee ID, email address etc

Remember that you have the power of Excel's formulas and auto fill to generate your input data.

An example screenshot of a data load:

exceltoci-usersaveas-datainput.png

Once you are happy with your data, press the stage data for submission button.

Staging and Submission

Staging and submission allows you to submit the data to your PeopleSoft system and check for any errors. Before you submit however, switch back to the Connect Information tab. Here, make sure the action is set to UPDATEDATA.

If you leave this as UPDATE, you will get the error Invalid User ID. (48,14) even if you use valid users.

Press the Submit Data button on the staging and submission tab to submit your data and create the User IDs. You will be asked to login to the system again - make sure the user you use for this has appropriate PeopleSoft security.

Hopefully you get OK statuses for your IDs and they are created successfully. If not, the following troubleshooting section might be helpful.

Troubleshooting

Some help with common errors you might receive:

  • No rows exists for specified keys. {USER_SAVEAS} (91,50)

This indicates that the base user you are trying to copy from does not exist in the system.

  • Warning replace existing User ID definition for <User ID>

This indicates the user you are cloning is already in the system.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License