MATLAB Central Newsreader - excel matlab connection and execution

excel matlab connection and execution

Filip T?rnqvist — Tue, 28 Oct 2008 21:18:02 -0400

Hello!

We're trying to connect and run matlab from excel using a subroutine(executed via a commandbutton in excel) with the following visualbasic code:

Sub Exc_to_Mat()

Dim MatLab As Object
Dim result As String
Dim MReal() As Double
Dim MImag() As Double

Set MatLab = CreateObject("Matlab.Application")

Call MatLab.Execute("clear all")
Call MatLab.Execute("cd C:\My_Docs\Kurser_T\ProduktModellering_TMKT57\Excel_Matlab")
Call MatLab.Execute("test1([ " & ThisWorkbook.Worksheets("sheet1").Range("C5").Value _
& " " & ThisWorkbook.Worksheets("sheet1").Range("C6").Value _
& " " & ThisWorkbook.Worksheets("sheet1").Range("C7").Value & "])")
Call MatLab.Execute("ans")

Call MatLab.GetFullMatrix("ans", "base", MReal, MImag)

ThisWorkbook.Worksheets("sheet1").Range("Stress").Value = MReal

End Sub

Running the m-file called test1 which looks like this:

function Stress= test1(x);
Radius = x(1);
Thickness = x(2);
Pressure = x(3);
Stress = Pressure*Radius/Thickness;

This connection will work and run on a computer using the 2006b licens but not a computer using the 2007a what might be the problem? Have we forgotten to add references in excel in order to be able to run these types o files? Or have the possibility to run these types of codes been removed from the 2007 version?

Best regards
/Filip and Oskar

Re: excel matlab connection and execution

Ashish Uthama — Tue, 28 Oct 2008 22:11:14 -0400

Excel uses the registry to obtain the path/version of MATLAB to use for
this command:
> Set MatLab = CreateObject("Matlab.Application")
Only one version of MATLAB may be registered using this per computer.

To ensure that MATLAB is registered and is indeed in the registry for
Excel to find, do this:

1. Open the version of MATLAB you would like to use
2. type this in thecommand window:
matlab -regserver
3. Type 'exit' in the new MATLAB command window that opens.
4. Close the main MATLAB session
5. Try your excel app..

On Tue, 28 Oct 2008 17:18:02 -0400, Filip T?rnqvist
<filto338@student.liu.se> wrote:

> Hello!
>
> We're trying to connect and run matlab from excel using a
> subroutine(executed via a commandbutton in excel) with the following
> visualbasic code:
>
> Sub Exc_to_Mat()
>
> Dim MatLab As Object
> Dim result As String
> Dim MReal() As Double
> Dim MImag() As Double
> Set MatLab = CreateObject("Matlab.Application")
>
> Call MatLab.Execute("clear all")
> Call MatLab.Execute("cd
> C:\My_Docs\Kurser_T\ProduktModellering_TMKT57\Excel_Matlab")
> Call MatLab.Execute("test1([ " &
> ThisWorkbook.Worksheets("sheet1").Range("C5").Value _
> & " " & ThisWorkbook.Worksheets("sheet1").Range("C6").Value _
> & " " & ThisWorkbook.Worksheets("sheet1").Range("C7").Value & "])")
> Call MatLab.Execute("ans")
>
> Call MatLab.GetFullMatrix("ans", "base", MReal, MImag)
>
> ThisWorkbook.Worksheets("sheet1").Range("Stress").Value = MReal
>
> End Sub
>
> Running the m-file called test1 which looks like this:
>
> function Stress= test1(x);
> Radius = x(1);
> Thickness = x(2);
> Pressure = x(3);
> Stress = Pressure*Radius/Thickness;
>
> This connection will work and run on a computer using the 2006b licens
> but not a computer using the 2007a what might be the problem? Have we
> forgotten to add references in excel in order to be able to run these
> types o files? Or have the possibility to run these types of codes been
> removed from the 2007 version?
>
> Best regards
> /Filip and Oskar

Re: excel matlab connection and execution

Filip T?rnqvist — Wed, 29 Oct 2008 07:32:01 -0400

We just tried this and matlab refuses to accept the command
matlab -regserver in the main window. We only get a message saying undefined function or variable.

Best regards
/Filip and Oskar

"Ashish Uthama" <first.last@mathworks.com> wrote in message <op.ujq880pha5ziv5@uthamaa.dhcp.mathworks.com>...
> Excel uses the registry to obtain the path/version of MATLAB to use for
> this command:
> > Set MatLab = CreateObject("Matlab.Application")
> Only one version of MATLAB may be registered using this per computer.
>
> To ensure that MATLAB is registered and is indeed in the registry for
> Excel to find, do this:
>
> 1. Open the version of MATLAB you would like to use
> 2. type this in thecommand window:
> matlab -regserver
> 3. Type 'exit' in the new MATLAB command window that opens.
> 4. Close the main MATLAB session
> 5. Try your excel app..
>
>
> On Tue, 28 Oct 2008 17:18:02 -0400, Filip T?rnqvist
> <filto338@student.liu.se> wrote:
>
> > Hello!
> >
> > We're trying to connect and run matlab from excel using a
> > subroutine(executed via a commandbutton in excel) with the following
> > visualbasic code:
> >
> > Sub Exc_to_Mat()
> >
> > Dim MatLab As Object
> > Dim result As String
> > Dim MReal() As Double
> > Dim MImag() As Double
> > Set MatLab = CreateObject("Matlab.Application")
> >
> > Call MatLab.Execute("clear all")
> > Call MatLab.Execute("cd
> > C:\My_Docs\Kurser_T\ProduktModellering_TMKT57\Excel_Matlab")
> > Call MatLab.Execute("test1([ " &
> > ThisWorkbook.Worksheets("sheet1").Range("C5").Value _
> > & " " & ThisWorkbook.Worksheets("sheet1").Range("C6").Value _
> > & " " & ThisWorkbook.Worksheets("sheet1").Range("C7").Value & "])")
> > Call MatLab.Execute("ans")
> >
> > Call MatLab.GetFullMatrix("ans", "base", MReal, MImag)
> >
> > ThisWorkbook.Worksheets("sheet1").Range("Stress").Value = MReal
> >
> > End Sub
> >
> > Running the m-file called test1 which looks like this:
> >
> > function Stress= test1(x);
> > Radius = x(1);
> > Thickness = x(2);
> > Pressure = x(3);
> > Stress = Pressure*Radius/Thickness;
> >
> > This connection will work and run on a computer using the 2006b licens
> > but not a computer using the 2007a what might be the problem? Have we
> > forgotten to add references in excel in order to be able to run these
> > types o files? Or have the possibility to run these types of codes been
> > removed from the 2007 version?
> >
> > Best regards
> > /Filip and Oskar

Re: excel matlab connection and execution

Filip T?rnqvist — Wed, 29 Oct 2008 09:39:01 -0400

Do you mean the windows commandwindow or the matlab one?
If we run the VB code we only get 65535 as the result. Eventhough we do send something else.

Best regards
/Filip and Oskar

"Filip T?rnqvist" <filto338@student.liu.se> wrote in message <ge7via$ogo$1@fred.mathworks.com>...
> Hello!
>
> We're trying to connect and run matlab from excel using a subroutine(executed via a commandbutton in excel) with the following visualbasic code:
>
> Sub Exc_to_Mat()
>
> Dim MatLab As Object
> Dim result As String
> Dim MReal() As Double
> Dim MImag() As Double
>
> Set MatLab = CreateObject("Matlab.Application")
>
> Call MatLab.Execute("clear all")
> Call MatLab.Execute("cd C:\My_Docs\Kurser_T\ProduktModellering_TMKT57\Excel_Matlab")
> Call MatLab.Execute("test1([ " & ThisWorkbook.Worksheets("sheet1").Range("C5").Value _
> & " " & ThisWorkbook.Worksheets("sheet1").Range("C6").Value _
> & " " & ThisWorkbook.Worksheets("sheet1").Range("C7").Value & "])")
> Call MatLab.Execute("ans")
>
> Call MatLab.GetFullMatrix("ans", "base", MReal, MImag)
>
> ThisWorkbook.Worksheets("sheet1").Range("Stress").Value = MReal
>
> End Sub
>
> Running the m-file called test1 which looks like this:
>
> function Stress= test1(x);
> Radius = x(1);
> Thickness = x(2);
> Pressure = x(3);
> Stress = Pressure*Radius/Thickness;
>
> This connection will work and run on a computer using the 2006b licens but not a computer using the 2007a what might be the problem? Have we forgotten to add references in excel in order to be able to run these types o files? Or have the possibility to run these types of codes been removed from the 2007 version?
>
> Best regards
> /Filip and Oskar

Re: excel matlab connection and execution

Ashish Uthama — Wed, 29 Oct 2008 12:32:25 -0400

apologies
I forgot the '!'
try this in the MATLAB command window:
matlabprompt>!matlab -regserver

You can also look at (explains how to do this from the windows command
prompt):
http://www.mathworks.com/support/solutions/data/1-S09OT.html?solution=1-S09OT

On Wed, 29 Oct 2008 03:32:01 -0400, Filip T?rnqvist
<filto338@student.liu.se> wrote:

> We just tried this and matlab refuses to accept the command
> matlab -regserver in the main window. We only get a message saying
> undefined function or variable.
>
> Best regards
> /Filip and Oskar
>
> "Ashish Uthama" <first.last@mathworks.com> wrote in message
> <op.ujq880pha5ziv5@uthamaa.dhcp.mathworks.com>...
>> Excel uses the registry to obtain the path/version of MATLAB to use for
>> this command:
>> > Set MatLab = CreateObject("Matlab.Application")
>> Only one version of MATLAB may be registered using this per computer.
>>
>> To ensure that MATLAB is registered and is indeed in the registry for
>> Excel to find, do this:
>>
>> 1. Open the version of MATLAB you would like to use
>> 2. type this in thecommand window:
>> matlab -regserver
>> 3. Type 'exit' in the new MATLAB command window that opens.
>> 4. Close the main MATLAB session
>> 5. Try your excel app..
>>
>>
>> On Tue, 28 Oct 2008 17:18:02 -0400, Filip T?rnqvist
>> <filto338@student.liu.se> wrote:
>>
>> > Hello!
>> >
>> > We're trying to connect and run matlab from excel using a
>> > subroutine(executed via a commandbutton in excel) with the following
>> > visualbasic code:
>> >
>> > Sub Exc_to_Mat()
>> >
>> > Dim MatLab As Object
>> > Dim result As String
>> > Dim MReal() As Double
>> > Dim MImag() As Double
>> > Set MatLab = CreateObject("Matlab.Application")
>> >
>> > Call MatLab.Execute("clear all")
>> > Call MatLab.Execute("cd
>> > C:\My_Docs\Kurser_T\ProduktModellering_TMKT57\Excel_Matlab")
>> > Call MatLab.Execute("test1([ " &
>> > ThisWorkbook.Worksheets("sheet1").Range("C5").Value _
>> > & " " & ThisWorkbook.Worksheets("sheet1").Range("C6").Value _
>> > & " " & ThisWorkbook.Worksheets("sheet1").Range("C7").Value & "])")
>> > Call MatLab.Execute("ans")
>> >
>> > Call MatLab.GetFullMatrix("ans", "base", MReal, MImag)
>> >
>> > ThisWorkbook.Worksheets("sheet1").Range("Stress").Value = MReal
>> >
>> > End Sub
>> >
>> > Running the m-file called test1 which looks like this:
>> >
>> > function Stress= test1(x);
>> > Radius = x(1);
>> > Thickness = x(2);
>> > Pressure = x(3);
>> > Stress = Pressure*Radius/Thickness;
>> >
>> > This connection will work and run on a computer using the 2006b licens
>> > but not a computer using the 2007a what might be the problem? Have we
>> > forgotten to add references in excel in order to be able to run these
>> > types o files? Or have the possibility to run these types of codes
>> been
>> > removed from the 2007 version?
>> >
>> > Best regards
>> > /Filip and Oskar
>

Re: excel matlab connection and execution

Ben Hinchliffe — Wed, 29 Oct 2008 17:09:01 -0400

Hi Filip and Oskar,
You may want to try using an add-in for Excel and Word called Inference for MATLAB. Inference enables you to combine MATLAB commands, MATLAB software objects, data, and text within an Excel or Word document. This document can then be executed to generate a results document (report).
By using Inference, you can avoid the hassle of visualbasic and working with the Office object model.
You can download a free 120-day trial of Inference for MATLAB at www.InferenceForMATLAB.com
Hope this helps!
-Ben

"Filip T?rnqvist" <filto338@student.liu.se> wrote in message <ge9avl$8g2$1@fred.mathworks.com>...
> Do you mean the windows commandwindow or the matlab one?
> If we run the VB code we only get 65535 as the result. Eventhough we do send something else.
>
> Best regards
> /Filip and Oskar
>
> "Filip T?rnqvist" <filto338@student.liu.se> wrote in message <ge7via$ogo$1@fred.mathworks.com>...
> > Hello!
> >
> > We're trying to connect and run matlab from excel using a subroutine(executed via a commandbutton in excel) with the following visualbasic code:
> >
> > Sub Exc_to_Mat()
> >
> > Dim MatLab As Object
> > Dim result As String
> > Dim MReal() As Double
> > Dim MImag() As Double
> >
> > Set MatLab = CreateObject("Matlab.Application")
> >
> > Call MatLab.Execute("clear all")
> > Call MatLab.Execute("cd C:\My_Docs\Kurser_T\ProduktModellering_TMKT57\Excel_Matlab")
> > Call MatLab.Execute("test1([ " & ThisWorkbook.Worksheets("sheet1").Range("C5").Value _
> > & " " & ThisWorkbook.Worksheets("sheet1").Range("C6").Value _
> > & " " & ThisWorkbook.Worksheets("sheet1").Range("C7").Value & "])")
> > Call MatLab.Execute("ans")
> >
> > Call MatLab.GetFullMatrix("ans", "base", MReal, MImag)
> >
> > ThisWorkbook.Worksheets("sheet1").Range("Stress").Value = MReal
> >
> > End Sub
> >
> > Running the m-file called test1 which looks like this:
> >
> > function Stress= test1(x);
> > Radius = x(1);
> > Thickness = x(2);
> > Pressure = x(3);
> > Stress = Pressure*Radius/Thickness;
> >
> > This connection will work and run on a computer using the 2006b licens but not a computer using the 2007a what might be the problem? Have we forgotten to add references in excel in order to be able to run these types o files? Or have the possibility to run these types of codes been removed from the 2007 version?
> >
> > Best regards
> > /Filip and Oskar

Re: excel matlab connection and execution

Filip T?rnqvist — Wed, 29 Oct 2008 20:04:02 -0400

Hey
Thank you for the tip Ben. We'll have a look at thatprogram. Can we execute other m-files from excel with this interface? Since we have a GA-algorithm we'd like to run from excel we need this possibility.

"Ben Hinchliffe" <ben.hinchliffe@bluereference.com> wrote in message <gea5bd$aeo$1@fred.mathworks.com>...
> Hi Filip and Oskar,
> You may want to try using an add-in for Excel and Word called Inference for MATLAB. Inference enables you to combine MATLAB commands, MATLAB software objects, data, and text within an Excel or Word document. This document can then be executed to generate a results document (report).
> By using Inference, you can avoid the hassle of visualbasic and working with the Office object model.
> You can download a free 120-day trial of Inference for MATLAB at www.InferenceForMATLAB.com
> Hope this helps!
> -Ben
>
> "Filip T?rnqvist" <filto338@student.liu.se> wrote in message <ge9avl$8g2$1@fred.mathworks.com>...
> > Do you mean the windows commandwindow or the matlab one?
> > If we run the VB code we only get 65535 as the result. Eventhough we do send something else.
> >
> > Best regards
> > /Filip and Oskar
> >
> > "Filip T?rnqvist" <filto338@student.liu.se> wrote in message <ge7via$ogo$1@fred.mathworks.com>...
> > > Hello!
> > >
> > > We're trying to connect and run matlab from excel using a subroutine(executed via a commandbutton in excel) with the following visualbasic code:
> > >
> > > Sub Exc_to_Mat()
> > >
> > > Dim MatLab As Object
> > > Dim result As String
> > > Dim MReal() As Double
> > > Dim MImag() As Double
> > >
> > > Set MatLab = CreateObject("Matlab.Application")
> > >
> > > Call MatLab.Execute("clear all")
> > > Call MatLab.Execute("cd C:\My_Docs\Kurser_T\ProduktModellering_TMKT57\Excel_Matlab")
> > > Call MatLab.Execute("test1([ " & ThisWorkbook.Worksheets("sheet1").Range("C5").Value _
> > > & " " & ThisWorkbook.Worksheets("sheet1").Range("C6").Value _
> > > & " " & ThisWorkbook.Worksheets("sheet1").Range("C7").Value & "])")
> > > Call MatLab.Execute("ans")
> > >
> > > Call MatLab.GetFullMatrix("ans", "base", MReal, MImag)
> > >
> > > ThisWorkbook.Worksheets("sheet1").Range("Stress").Value = MReal
> > >
> > > End Sub
> > >
> > > Running the m-file called test1 which looks like this:
> > >
> > > function Stress= test1(x);
> > > Radius = x(1);
> > > Thickness = x(2);
> > > Pressure = x(3);
> > > Stress = Pressure*Radius/Thickness;
> > >
> > > This connection will work and run on a computer using the 2006b licens but not a computer using the 2007a what might be the problem? Have we forgotten to add references in excel in order to be able to run these types o files? Or have the possibility to run these types of codes been removed from the 2007 version?
> > >
> > > Best regards
> > > /Filip and Oskar

Re: excel matlab connection and execution

Filip T?rnqvist — Wed, 29 Oct 2008 20:08:01 -0400

I'm sorry to inform everyone that the problem still exist. We can run the matlab window from excel but it will only write 65535 in the specified excel position. Have we forgotten to activate some reference in vb or excel? Or have we missed smething else?

Thank you for trying to help us Ashish. If you have any more ideas on what might be wrong please tell us.

Best regards
/Filip and Oskar

"Ashish Uthama" <first.last@mathworks.com> wrote in message <op.ujsc4bxqa5ziv5@uthamaa.dhcp.mathworks.com>...
> apologies
> I forgot the '!'
> try this in the MATLAB command window:
> matlabprompt>!matlab -regserver
>
> You can also look at (explains how to do this from the windows command
> prompt):
> http://www.mathworks.com/support/solutions/data/1-S09OT.html?solution=1-S09OT
>
>
>
> On Wed, 29 Oct 2008 03:32:01 -0400, Filip T?rnqvist
> <filto338@student.liu.se> wrote:
>
> > We just tried this and matlab refuses to accept the command
> > matlab -regserver in the main window. We only get a message saying
> > undefined function or variable.
> >
> > Best regards
> > /Filip and Oskar
> >
> > "Ashish Uthama" <first.last@mathworks.com> wrote in message
> > <op.ujq880pha5ziv5@uthamaa.dhcp.mathworks.com>...
> >> Excel uses the registry to obtain the path/version of MATLAB to use for
> >> this command:
> >> > Set MatLab = CreateObject("Matlab.Application")
> >> Only one version of MATLAB may be registered using this per computer.
> >>
> >> To ensure that MATLAB is registered and is indeed in the registry for
> >> Excel to find, do this:
> >>
> >> 1. Open the version of MATLAB you would like to use
> >> 2. type this in thecommand window:
> >> matlab -regserver
> >> 3. Type 'exit' in the new MATLAB command window that opens.
> >> 4. Close the main MATLAB session
> >> 5. Try your excel app..
> >>
> >>
> >> On Tue, 28 Oct 2008 17:18:02 -0400, Filip T?rnqvist
> >> <filto338@student.liu.se> wrote:
> >>
> >> > Hello!
> >> >
> >> > We're trying to connect and run matlab from excel using a
> >> > subroutine(executed via a commandbutton in excel) with the following
> >> > visualbasic code:
> >> >
> >> > Sub Exc_to_Mat()
> >> >
> >> > Dim MatLab As Object
> >> > Dim result As String
> >> > Dim MReal() As Double
> >> > Dim MImag() As Double
> >> > Set MatLab = CreateObject("Matlab.Application")
> >> >
> >> > Call MatLab.Execute("clear all")
> >> > Call MatLab.Execute("cd
> >> > C:\My_Docs\Kurser_T\ProduktModellering_TMKT57\Excel_Matlab")
> >> > Call MatLab.Execute("test1([ " &
> >> > ThisWorkbook.Worksheets("sheet1").Range("C5").Value _
> >> > & " " & ThisWorkbook.Worksheets("sheet1").Range("C6").Value _
> >> > & " " & ThisWorkbook.Worksheets("sheet1").Range("C7").Value & "])")
> >> > Call MatLab.Execute("ans")
> >> >
> >> > Call MatLab.GetFullMatrix("ans", "base", MReal, MImag)
> >> >
> >> > ThisWorkbook.Worksheets("sheet1").Range("Stress").Value = MReal
> >> >
> >> > End Sub
> >> >
> >> > Running the m-file called test1 which looks like this:
> >> >
> >> > function Stress= test1(x);
> >> > Radius = x(1);
> >> > Thickness = x(2);
> >> > Pressure = x(3);
> >> > Stress = Pressure*Radius/Thickness;
> >> >
> >> > This connection will work and run on a computer using the 2006b licens
> >> > but not a computer using the 2007a what might be the problem? Have we
> >> > forgotten to add references in excel in order to be able to run these
> >> > types o files? Or have the possibility to run these types of codes
> >> been
> >> > removed from the 2007 version?
> >> >
> >> > Best regards
> >> > /Filip and Oskar
> >

Re: excel matlab connection and execution

Walter Roberson — Wed, 29 Oct 2008 20:58:02 -0400

Filip T?rnqvist wrote:
> I'm sorry to inform everyone that the problem still exist. We can run the matlab window
> from excel but it will only write 65535 in the specified excel position.

You haven't spoken about which excel versions you have on the respective machines.

As I wrote in a posting a couple of days ago:

http://office.microsoft.com/en-us/excel/HA101375451033.aspx

A Microsoft Office Excel worksheet contains 65,536 rows and 256 columns.
You cannot increase the number of rows or columns beyond the maximum row and column limits.

http://support.microsoft.com/kb/264626

MORE INFORMATION
You cannot exceed the maximum limits of features in Excel. For example, you cannot
increase the number of worksheet rows beyond 65,536.
http://support.microsoft.com/kb/120596/en-us

In Microsoft Excel 97 for Windows, Microsoft Excel 2000, Microsoft Excel 2002,
and Microsoft Office Excel 2003, text files that contain more than 65,536 rows
cannot be opened in their entirety. You cannot open these files because these versions
of Microsoft Excel are limited to 65,536 rows.

Notes

Versions of Excel earlier than Excel 97 have a limit of 16,384 rows.

Versions of Excel later than Excel 2003 have a limit of 1,048,576 rows.

"Versions of Excel later than Excel 2003" includes only "Excel 2007" at the present
time. And Matlab's xlswrite() has not caught up to Excel 2007 format in your version.