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
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! 🙂