How can i execute Matlab m.file in Excel VBA?

Hello!

I am working on a project, which the input data are from an excel workbook and then the result calculating by matlab are returned in another excel workbook. The m.file run correctly in Matlab and also return the values back in excel.

Now i am facing the problem to execute Matlab m.file from excel VBA without opening Matlab. I have looked up these related solutions on this forum and others, Spreadsheet Link EX i won't consider because my calculation in Matlab is 4-dimensions. I apply the solution from Johan (<http://www.mathworks.com/matlabcentral/answers/8684-running-a-specific-m-file-fig-from-excel)>.

However, until now there is still a problem. When i execute the excel macro to execute Matlab Application, the message 'Excel is waiting for another application to complete an OLE action.' comes out all the time. Afterwards i turn off the alert display in excel, then the error message 'Runtime Error 2147023170 (800706be): Automation Error The Remote Procedure Call Failed' appears.

The abstract of my m.file is:

 enableservice('AutomationServer',true)
 % Input
 t1 = xlsread(filename,sheet,xlRange);
 ...
 % Calculation
 ...
 % Output
 C = xlswrite(filename,sheet,xlRange);

The macro code in excel is like:

Sub runMatlab()
Dim hMatlab As Object
Dim sDir As String, cdsDir As String, s1 As String
Set hMatlab = CreateObject("Matlab.Application")
s1 = "'"
sDir = s1 & ActiveWorkbook.Path & s1
cdsDir ="cd(" & sDir & ")"
hMatlab.Execute(cdsDir)
Application.DisplayAlerts = False
hMatlab.Execute("Calculation")
Application.DisplayAlerts = True
ThisWorkbook.Close SaveChanges:=True   
End Sub

Could someone give me a hint how to solve this problem? I will be really appreciate!

Answer 1

[Udaya Mallampati]

Hi Lun,

You can put a breakpoint in the VBA code and find out the line which causes this problem. If the call to "CreateObject" fails, then try registering MATLAB as COM server with the following procedure:

• Shut down all MATLAB and Excel instances.
• Open a command prompt, and using cd, change to the bin\win32 subfolder of the MATLAB installation folder.
• Type the command:

 .\matlab /regserver

• When the MATLAB session starts, close it.

If the call to "matlabobj.Excecute" fails, then replace the calculation with a simple function call and see if it works. I executed the code at my end by replacing the calculation with a call to "magic()" function and found that it gives the expected result.

Hope this helps!

Answer 2

[Sean de Wolski]

You could use Spreadsheet Link EX which takes care of dealing with COM for you so that you can seamlessly move data back and forth, call MATLAB functions and extract any graphics if necessary:

http://www.mathworks.com/products/excellink/