Tuesday, June 23, 2015

PeopleSoft Journal Import MSXML fix

Reactions: 

PeopleSoft Journal Import MSXML fix

Problem

The Macro distributed by PeopleSoft for Journal Import fails to work on Windows Vista, 7, and 8/8.1.  With Windows 7 and Vista the solution was simple.  Copy the MSXML.dll file from the C:\Windows\System32 folder from a Windows XP machine and place it in the C:\Windows\System32 and/or C:\Windows\SysWOW folder on the Vista/7 machine.  This may have also worked on Windows 8/8.1 x86 but it does not work on Windows 8/8.1 x64.  For 8/8.1 there are a few modifications to the JRNLMCRO.xla file that are needed. (this was done for Windows 8/8.1 running Excel 2013)

Solution

You have to modify the JRNLMCRO.xla file to include the Microsoft XML v6.0 dll and also modify two lines within the macro code.

Prerequisites

1.       Temporarily Trust Macros in Excel

2.       Ensure the Developer Tab is enabled in Excel

a.       File -> Options -> Customize Ribbon -> (Check) Developer Tab

Process

Open JRNLMCRO.xla

1.       Click Developer Tab

a.       Click Visual Basic

                                                               i.      Highlight Jrnlmcro from the Tree view

                                                             ii.      Click Tools Menu

1.       Click References

a.       Find and check Microsoft XML, v6.0 from the Available References list

b.      Click OK

                                                           iii.      Expand Modules from the Tree view

                                                           iv.      Double Click mImportControl

1.       Within the Code window for mImportControl search for MSXML2.DOMDocument

2.       Replace the two instances of MSXML2.DOMDocument with MSXML2.DOMDocument60

                                                             v.      Save the macro, exit Excel and try to run your Journal Import normally.