3

I have written a VBA macro to load and parse an XML file. This exact code has worked fine for awhile, but now it's no longer functioning. I've tested it on two different computers running Excel 2013 64-bit. The code it fails on is below:

Public Sub RunClashImport()
'This subroutine will import an XML clash report, parse it, and write it to our workbook
Dim iRow As Integer

'Get XML file
Dim xmlFile As MSXML2.DOMDocument60
Set xmlFile = New MSXML2.DOMDocument60

The code continues if I comment out the Set command. It doesn't throw an error, it crashes Excel entirely. The code above is where the macro starts, there is no code running before it. I have set a reference to v6.0 of MSXML

enter image description here

Bishop
  • 127
  • 2
  • 14
  • Have you tried unregistering/registering the dll? REGSVR32 MSXML6.DLL /U REGSVR32 MSXML6.DLL – Richard Morgan Feb 18 '14 at 16:18
  • how bout testing a computer that's not runnning 64 bit? on the off chance its something weird with 64 bit I'd check a 32 machine. I know there are some things that 64 bit does / does not support – user1759942 Feb 18 '14 at 20:47
  • @user1759942 It's rather the 64bit version of Office not Windows that causes the issue. –  Feb 19 '14 at 08:10
  • Can you confirm [**which Office version you're running?**](http://www.howtogeek.com/howto/24259/beginner-discover-if-youre-running-the-32-or-64-bit-version-of-office-2010/) –  Feb 19 '14 at 08:13
  • 1
    Did you tried changing to Microsoft XML v3.0? Anyway, I am running Excel 2010 64bit and your code works fine with both versions (6.0 and 3.0). – hstay Feb 19 '14 at 14:29
  • I tested the file on a 32bit version of Office 2013 and it ran correctly. The code was written with a 64bit version and worked for awhile, so something has obviously changed. I changed the reference to Microsoft XML v3.0 and it ran correctly on the 64bit version of Office. Since this has stopped working, I've switched both my machines to Windows 8.1 (from 7) and it still doesn't work. I have the same software installed on both, so I'm wondering if it is another program that is interfering somehow. – Bishop Feb 19 '14 at 19:30
  • @Bishop Did you already tried with `Set xmlFile = CreateObject("Msxml2.DOMDocument.6.0")` – wolfrevo Feb 25 '14 at 10:28
  • @Bishop drag and drop MSXML6.dll to C:\Windows\SysWOW64 and run this command in your command prompt: regsvr32.exe C:\Windows\SysWOW64\msxml6.dll. Tell me if it worked for you. – João Pinho Feb 25 '14 at 13:26

1 Answers1

1

For a 64 bit Windows OS you should put your msxml6.dll inside of C:\Windows\SysWOW64 and then register it from that location, by running this command in your command prompt regsvr32.exe C:\Windows\SysWOW64\msxml6.dll.

Make sure that you run you command prompt as Administrator.

João Pinho
  • 3,725
  • 1
  • 19
  • 29
  • msxml6.dll was already located in C:\Windows\SysWOW64. Re-registering did not correct the issue. – Bishop Feb 25 '14 at 18:43
  • 1
    @Bishop, right, but now you need to go to Excel, remove the reference to Microsoft XML, v6, perform Browse and point to the msxml6 in SysWOW64 and then select that, ensure that when selected it then says C:\Windows\SysWOW64\msxml6.dll. – João Pinho Feb 25 '14 at 21:03