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




About Manikandan Surendren

A PeopleSoft Techno-functional Consultant. An engineering graduate in Computer Science. Technology Enthusiast. Movie freak. Gadget geek.
This entry was posted in Code, Operating System, Oracle, PeopleSoft, Software and tagged , , , , , , , . Bookmark the permalink.

32 Responses to Excel to CI utility not working in Excel 2016

  1. DoyJr says:

    Hi Many, This helped a lot. But question is. when the customer has a lower version of ms office excel, would this be an issue?

    • Mani 2.0 says:

      Hi DoyJr,
      Thanks for commenting! If the customer is on a certified lower version of Excel, then, it shouldn’t be a problem. Issue would be with non-certified versions of Excel. Also, if at an organization level, there is a mismatch between the XML libraries already set in excel and the one required by ExcelToCI, then this issue would appear as well.

      Please do let me know, if you still have questions.

  2. Pande Dyer says:

    Tremendous help — Thank You!!!!

  3. Federico Betoldi says:

    Thank you so much !!! Now I am having a problem about timeout for IN_MST_ITM_XLS Component Interface. But you really helped me solving this problem. Thanks to share your solution !!! 🙂

  4. Federico Betoldi says:

    I could solved the problem of timeout. It was the protocol setted as https instead of http. Thank you a lot !!!

  5. Ryan says:

    I wasn’t getting the error you posted at the start, but I am getting the “438: Object doesn’t support this property or method”. When I click Debug, it highlights: Sheets(sSheet2).btnProtocol.Clear

    I’ve been looking through a bunch of troubleshooting docs and thoughts from the community in general but nothing has been helping. I’ve run the ExcelToCI.vbs and the ReLangMcro.xla and re-run the init macro. Everything results in the 438 run time error.

    Any thoughts you have would be hugely appreciated

    • Manikandan Surendren says:

      Hi Ryan – is it possible for you to upload the file that you’re using somewhere? I’ll use that to replicate and get this issue resolved.

      • Ryan says:

        Hello! I don’t have a particular place i can upload to. I can email the template (it’s blank with no identifying info) if you could point me in the right direction? I will note that the two other colleagues of mine that use excel to CI have no problem using the template. It seems I am the only one getting the error. Could there be old system settings causing the issue. My system is 4 years old and I am using MS Offiec 2013 products

      • Manikandan Surendren says:

        Hi – please see if you can e-mail the template to smani043 [at] gmail

        Are your colleagues using MS Office 2013 too? And what version of PeopleTools are you on?

    • Andy says:

      Hi Ryan,

      Looks like you were having the same issue that we were. Please see my comment/reply below for the resolution that fixed the issue for us. And hope it helps!

  6. Kavitha says:

    Hi I followed all your steps and now am getting an error like “Invalid XML Returned” , Can you please help me to rectify this error too

  7. Sandra says:

    Thank you so much!!!! It solved my issue 😉

  8. Kuldeep Kumar says:

    And I started getting this Error.
    Error occured in routine sendSoapRequest_SubmitToDB:
    Number: 429
    Description ActiuveX component can’t create object

  9. Andy says:

    Hello Mani,

    Hope you are well. have you been able to resolve Ryan’s issue above commented above? We are having the same issue; we recently upgraded to windows 10 (excel 2016) and are on PT 8.54.21.

    Oracle suggested the same thing as you have outlined in this walkthrough above and it still points to the “438: Object doesn’t support this property or method” error. When I just go in blank VBA editor and start typing the sheets() object, I used the “List Properties/Methods” tool and it did not have the .btnprotocol as a property/method. Is this custom? And where would it be stored?

    Thanks for your assistance!

    Best regards

  10. Alexei Tetenov says:

    For people with MyOracleSupport (MOS) access, check out “E-ExcelToCI: Getting Compile Error on DOMDocument Reference Using ExcelToCI On Windows 8, 10, and 2012 Server (Doc ID 1524363.1)”
    At the bottom of the page, there are 4 attachments.

  11. Nan Vodde says:

    We are trying to test Excel to CI utility on Office/Excel 2016 / Windows 10 desktops and received the initial error addressed in this post. I followed your instructions and successfully got past the ‘Compile error: User-defined type not defined.’

    However, now I am getting this error:

    Error occurred in routine sendSOAPRequest_SubmitToDB:
    Number: 429
    Description: ActiveX component can’t create object.

    Any ideas on what I should check next?

  12. Martin Diez says:

    Thank you! It worked perfectly.

  13. Tom Huntsman says:

    I am windows 10 excel 2016 and PT 8.55. I downloaded the file from Oracle support — PT8.55_Win8_2012ExcelToCI_SpreadsheetAndMacro.zip – but now I get compile Error Variable not defined. the following fields are highlighted : worksheet — Activate
    Private Sub Worksheet Activate()
    Application.CommandBars(sToolBarDataInpoutActions).Visible = True

    would you have any clue what I need to do here?

    Tom Huntsman

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s