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.
I apparently solved the issue. Inspired by Hans Passant, I Dim
ed 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.