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

Compile_Error

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

Trust_Center_SettingsTrust_Center_AddInsSettingsTrust_Center_ActiveXSettings

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

ExcelToCI_HomePage

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

Profile_Tab

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

Process_Scheduler_Tab

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.

Enable_Developer_Tab

Ensure that the Developer tab appears now and click on it

Step 4 : Click on the Visual Basic icon

Visual_Basic_Button

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

StagingAndSubmission

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

Compile_ExcelToCI

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

XML6_References

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

DOMDocument_VB

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

Code_Modified

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

 

 

Advertisements

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.

14 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?

  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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s