Search code examples
vbadllcomactivex32bit-64bit

VBA CreateObject can’t create ActiveX component on 64Bit Windows


I have to write a program in Visual Studio 2013, but it doesn't work, so I started testing all the possibilities with Microsoft Excel and its developement playground. I started with the following version (provided by the company supplying the program I need to use):

Sub Main()

    Dim XServer As X2000.Server
    Dim XApp As X2000.Application
    Dim XSR As X2000.ScriptRoutines

    Set XServer = New X2000.Server
    Set XApp = XServer.AppConnection()    '<- This actually fires the application
    XApp.Visible = True

    Set XSR = XApp.ScriptCommands
    XSR.DATA_FILENAME = "C:\X2000\myFile.x"    ' <- Code blocks here

End Sub

This basically starts the application X2000.exe, connects my code to it, and then executes the DATA_FILENAME assignation routine, by loading myFile.x in the program. I need to start the application, because at that point its license is checked, and I don't have any other option to work with.

Now, in this version, the code was blocking at the last line by saying "A dll is missing" without any other information. I tried late-binding the ScriptRoutines object by doing the following:

Dim XServer As X2000.Server
Dim XApp As X2000.Application
Dim XSR As Object

Set XServer = New X2000.Server
Set XApp = XServer.AppConnection()
XApp.Visible = True

XSR = CreateObject("XApp.ScriptCommands")

This doesn't work as well, but did give me a piece of information more: "ActiveX control couldn’t load the object". I did a little research, and found out, through "Dependancy Walker", that X2000.exe was depending on MSVBVM60.DLL, which was missing in the system32 folder. Curious though, because my system is 64bit, Excel is too, and I tried the code on VS2013 by using a 32bit compatibility and still the code didn't work.
I installed the service pack from Microsoft, where this file was contained, only to find out, that system32 was left untouched... sysWoW64 instead had already a MSVBVM60.DLL, therefore I suspected that X2000.exe was written only to work with 32bit systems: I was wrong.

A colleague of mine could execute the same program written in MATLAB code:

function [Data,eng_or_met] = Read_X2000_File_BB_2(sFileName)

XServer = actxserver('X2000.server');
XApp = XServer.AppConnection;
XApp.Visible = 1;
objX2000 = XApp.ScriptCommands;
objX2000.data_filename = "C:\X2000\myFile.x";

delete(XServer);

This code works thanks to the actxserver function, which I believe to handle this compatibility problem.

How can I do the same with Excel/VS2013?

EDIT:

I tried with the code suggested by omegastripes:

Dim XSR As Object
Set XSR = CreateObjectx86("XApp.ScriptCommands") ' create ActiveX via x86 mshta host

being CreateObjectx86 the function linked here. I tried both the If / Else conditions and at the end the object is not created anyway, both with:

Set CreateObjectx86 = oWnd.CreateObjectx86(sProgID)

and

Set CreateObjectx86 = CreateObject(sProgID)

The last line is what gets executed if I don't change the code (therefore not #If Win64).

EDIT:

As per the first answer to this question, I also tried:

Dim XServer As Object
Dim XApp As X2000.Application
Dim XSR As X2000.Script

Set XServer = CreateObject("X2000.Server")
Set XApp = XServer.AppConnection()
XApp.Visible = True
Set XSR = X2000.ScriptCommands

Only to find out I get the same error.


Solution

  • I apparently solved the issue. Inspired by Hans Passant, I Dimed the XServer as the only object depending on the reference to X2000.exe:

    Dim XServer As X2000.Server
    Dim XApp As Object
    Dim XSR As Object
    
    Set XServer = New X2000.Server
    Set XApp = XServer.AppConnection
    XApp.Visible = True
    Set XSR = XApp.ScriptCommands
    
    XSR.DATA_FILENAME = "C:\foo.x" 
    

    DATA_FILENAME is actually a "ScriptCommand" that gets executed.

    Simple as that. Yet I have no idea why this works.