Excel to CI utility not working in Excel 2016

The Excel to CI utility that comes with PeopleSoft is very useful when it comes to loading multiple rows of data from an Excel template onto PeopleSoft. This seems to have been working fine with Excel 2007/2010, but failed with the following error in Microsoft Excel 2016.

Compile error : User-defined type not defined

Here are the steps that I followed to get the data loaded to PeopleSoft. The primary root cause of this issue is the XML Parser version that defaults in Office 2016 and the DOMDocument code that is used in the VB Script – both have to match, else resulting in an error during compile time. Without further ado, here are the steps!

Here are the instance details for your reference:

Host OS: Windows 10 Home
PeopleTools 8.55.01
PeopleSoft HCM 9.2
Microsoft Excel 2016 32-bit version

Step 1 : Open an instance of Excel 2016 and ensure that the settings are the same as in the screenshot provided below:

You can access the same using File > Options > Trust Center and Click on Trust Center settings

Step 2 : Open the ExcelToCI2007.xlsm file from the PS_HOME directory.

If you have a Demo version installed on a Windows Host, you can find out the home directory using Configuration Manager.

Open an instance of Configuration Manager

Click on Profile tab and then on the Edit button

Click on the Process Scheduler tab and copy the path available under the PeopleSoft Home Directory path

Step 3 : Next, add the Developer tab to the ribbon

To enable the Developer tab, follow the steps provided below:

Select File > Options

In the Excel options window that opens, click on the Customize Ribbon option. On the right pane, check the Developer option and click OK.

Ensure that the Developer tab appears now and click on it

Step 4 : Click on the Visual Basic icon

Step 5 : In the Visual Basic editor that opens, open the StagingAndSubmission code

Step 6 : Select Debug > Compile ExcelToCI and you would get the error message now

To resolve the error, please follow the steps provided below:

Step 7 : First off, ensure you have the latest MS XML Parser enabled. To verify:

Select Tools > References from within the VB Editor

Ensure Microsoft XML, v6.0 is checked. Else, scroll through the list and select the same. Also, ensure that any other MS XML version like v3.0 is unchecked.

Step 8 : Clicking on OK would set the cursor to the following line in the VB Editor. After changing each occurrence, compile the code using Debug > Compile ExcelToCI to ensure you haven’t missed any occurrence

Replace every occurrence of DOMDocument with DOMDocument60

Replace every occurrence of ServerXMLHTTP with ServerXMLHTTP60

Replace every occurrence of xDoc As DOMDocument with xDoc As DOMDocument60

Replace every occurrence of xHTTP As ServerXMLHTTP with xHTTP As ServerXMLHTTP60

Replace every occurrence of New DOMDocument with New DOMDocument60

Long story short, add 60 to each DOMDocument name to make it compatible with MS XML 6.0

Step 9 : Re-compile the code once more and ensure there are no more errors. Close the VB Editor and proceed with loading data using ExcelToCI template. You’re all set!!

Please post your questions in the comment section, if you have any.

Hope this helps! 🙂