MATLAB Central Newsreader - More details -- actxserver('excel.application')

More details -- actxserver('excel.application')

Andy Eisenberg — Thu, 02 Jul 2009 18:31:02 -0400

I'm trying to write a GUI in MATLAB that should have the ability to read from and write to an Excel spreadsheet arbitrarily. (By that I mean, given a range in a spreadsheet, I should be able to load the data into MATLAB from the spreadsheet. Or, given a range and an array that fits the range, I should be able to write that array to that range.) The problem I'm having is one of documentation. The MATLAB online documentation goes as far as establishing the connection between MATLAB and Excel, namely:

e = actxserver ('Excel.Application')
e.Visible = 1;
eWorkbooks = e.Workbooks
w = eWorkbooks.Add
e.Quit;
e.delete;

Elsewhere I've found things like:

exl = actxserver('excel.application');
exlWkbk = exl.Workbooks;
exlFile = exlWkbk.Open([docroot '/techdoc/matlab_external/examples/input_resp_data.xls']);
exlSheet1 = exlFile.Sheets.Item('Sheet1');
robj = exlSheet1.Columns.End(4);
numrows = robj.row;
dat_range = ['A1:G' num2str(numrows)];
rngObj = exlSheet1.Range(dat_range);
exlData = rngObj.Value;
exl.registerevent({'WorkbookBeforeClose',@close_event1});

But I only sort of understand this code because I can't find documentation for the methods/properties anywhere. The expression exlSheet1.Columns.End(4) does the right thing, but I don't know why.

Is there somewhere where I can find more complete documentation on the Excel objects, including documentation on their methods, properties, and common usage? I am not against Reading The Fantastic Manuals, if only I could find them.

Thanks in advance for your help.

Re: More details -- actxserver('excel.application')

ImageAnalyst — Thu, 02 Jul 2009 19:16:18 -0400

On Jul 2, 2:31 pm, "Andy Eisenberg" <theorigam...@gmail.com> wrote:
> I'm trying to write a GUI in MATLAB that should have the ability to read from and write to an Excel spreadsheet arbitrarily. (By that I mean, given a range in a spreadsheet, I should be able to load the data into MATLAB from the spreadsheet. Or, given a range and an array that fits the range, I should be able to write that array to that range.) The problem I'm having is one of documentation. The MATLAB online documentation goes as far as establishing the connection between MATLAB and Excel, namely:
>
> e = actxserver ('Excel.Application')
> e.Visible = 1;
> eWorkbooks = e.Workbooks
> w = eWorkbooks.Add
> e.Quit;
> e.delete;
>
> Elsewhere I've found things like:
>
> exl = actxserver('excel.application');
> exlWkbk = exl.Workbooks;
> exlFile = exlWkbk.Open([docroot '/techdoc/matlab_external/examples/input_resp_data.xls']);
> exlSheet1 = exlFile.Sheets.Item('Sheet1');
> robj = exlSheet1.Columns.End(4);
> numrows = robj.row;
> dat_range = ['A1:G' num2str(numrows)];
> rngObj = exlSheet1.Range(dat_range);
> exlData = rngObj.Value;
> exl.registerevent({'WorkbookBeforeClose',@close_event1});
>
> But I only sort of understand this code because I can't find documentation for the methods/properties anywhere. The expression exlSheet1.Columns.End(4) does the right thing, but I don't know why.
>
> Is there somewhere where I can find more complete documentation on the Excel objects, including documentation on their methods, properties, and common usage? I am not against Reading The Fantastic Manuals, if only I could find them.
>
> Thanks in advance for your help.

-----------------------------------------------------------------------------------------------------------------------------
Andy:
Here's the Excel 2007 Developer Reference
http://msdn.microsoft.com/en-us/library/bb242656.aspx

It came from the "Learn" tab of the Microsoft Office Developer Center
http://msdn.microsoft.com/en-us/office/default.aspx

There's a boatload of stuff there, but it's a place to start.
Good luck,
ImageAnalyst

Re: More details -- actxserver('excel.application')

Steven Lord — Thu, 02 Jul 2009 19:22:25 -0400

"Andy Eisenberg" <theorigamist@gmail.com> wrote in message
news:h2iud6$c94$1@fred.mathworks.com...
> I'm trying to write a GUI in MATLAB that should have the ability to read
> from and write to an Excel spreadsheet arbitrarily. (By that I mean,
> given a range in a spreadsheet, I should be able to load the data into
> MATLAB from the spreadsheet. Or, given a range and an array that fits the
> range, I should be able to write that array to that range.) The problem
> I'm having is one of documentation. The MATLAB online documentation goes
> as far as establishing the connection between MATLAB and Excel, namely:
>
> e = actxserver ('Excel.Application')
> e.Visible = 1;
> eWorkbooks = e.Workbooks
> w = eWorkbooks.Add
> e.Quit;
> e.delete;
>
> Elsewhere I've found things like:
>
> exl = actxserver('excel.application');
> exlWkbk = exl.Workbooks;
> exlFile = exlWkbk.Open([docroot
> '/techdoc/matlab_external/examples/input_resp_data.xls']);
> exlSheet1 = exlFile.Sheets.Item('Sheet1');
> robj = exlSheet1.Columns.End(4);
> numrows = robj.row;
> dat_range = ['A1:G' num2str(numrows)];
> rngObj = exlSheet1.Range(dat_range);
> exlData = rngObj.Value;
> exl.registerevent({'WorkbookBeforeClose',@close_event1});
>
>
> But I only sort of understand this code because I can't find documentation
> for the methods/properties anywhere. The expression
> exlSheet1.Columns.End(4) does the right thing, but I don't know why.
>
> Is there somewhere where I can find more complete documentation on the
> Excel objects, including documentation on their methods, properties, and
> common usage? I am not against Reading The Fantastic Manuals, if only I
> could find them.

Take a look at this section in the documentation:

http://www.mathworks.com/access/helpdesk/help/techdoc/matlab_external/brd4at8.html

Those methods and properties, like Workbooks, Open, Sheets, etc. are part of
Excel's COM interface. If you're looking for more information on those
methods and properties (not how to use methods and properties in MATLAB) I
think you're going to need to dig through the Microsoft Office Development
documentation on Microsoft's website:

http://msdn.microsoft.com/en-us/library/bb726434.aspx

--
Steve Lord
slord@mathworks.com

Re: More details -- actxserver('excel.application')

Andy Eisenberg — Thu, 02 Jul 2009 19:48:01 -0400

Thanks for the help. I'll get started reading through the developer reference.

Re: More details -- actxserver('excel.application')

Andy Eisenberg — Mon, 06 Jul 2009 17:58:02 -0400

I'm going to need a little more help with this. I'm writing a GUI that should allow the user to enter Excel formulas and add new columns to a spreadsheet whose values are the result of those formulas. For the purpose of testing, I have a spreadsheet with one column with 50 rows containing the numbers 1 through 50. I would like the B column to be 5 times the A column. I can do this in less-than-elegant ways, such as:

e=actxserver('excel.application');
eWkbk=e.Workbooks;
eFile=eWkbk.Open(filename);
sheet=exlFile.Sheets.Item(1);
range='A1:A50';
newrange='B1:B50';
sheet.Range(newrange).Value=['=5*' range];

I don't like this for a few reasons.
1) I entered the ranges explicitly. The following worked:
        robj=sheet.Columns.End(4);
        numrows=robj.row;
        range=['A1:A' num2str(numrows)];
        newrange=['B1:B' num2str(numrows)];
        sheet.Range(newrange).Value=['=5*' range];
But I don't know why this worked since I can't find the syntax 'End(4)' in the Excel 2003 Developer's Reference (http://msdn.microsoft.com/en-us/library/aa272268(office.11).aspx). All I found was this: http://msdn.microsoft.com/en-us/library/aa214585(office.11).aspx, which gives xlUp, xlDown, xlToRight, and xlToLeft as potential arguments to End.

2) Although the output was correct (B=5*A), the cells didn't appear the same as if I had just done this in Excel. For example, if I go to Excel and click in B10, the formula looks like '=5*A10:A59' rather than '=5*A10'.

Any help would be much appreciated.

Re: More details -- actxserver('excel.application')

Ilya Rozenfeld — Mon, 06 Jul 2009 18:29:02 -0400

To get the right formulas you need to create a cell array of strings or use loop
e.g.

for jj = 1:50
sheet.Range(['B' num2str(jj)]).Value=['=5*A' num2str(jj)];
end

"Andy Eisenberg" <theorigamist@gmail.com> wrote in message <h2tdva$20o$1@fred.mathworks.com>...
> I'm going to need a little more help with this. I'm writing a GUI that should allow the user to enter Excel formulas and add new columns to a spreadsheet whose values are the result of those formulas. For the purpose of testing, I have a spreadsheet with one column with 50 rows containing the numbers 1 through 50. I would like the B column to be 5 times the A column. I can do this in less-than-elegant ways, such as:
>
> e=actxserver('excel.application');
> eWkbk=e.Workbooks;
> eFile=eWkbk.Open(filename);
> sheet=exlFile.Sheets.Item(1);
> range='A1:A50';
> newrange='B1:B50';
> sheet.Range(newrange).Value=['=5*' range];
>
> I don't like this for a few reasons.
> 1) I entered the ranges explicitly. The following worked:
> robj=sheet.Columns.End(4);
> numrows=robj.row;
> range=['A1:A' num2str(numrows)];
> newrange=['B1:B' num2str(numrows)];
> sheet.Range(newrange).Value=['=5*' range];
> But I don't know why this worked since I can't find the syntax 'End(4)' in the Excel 2003 Developer's Reference (http://msdn.microsoft.com/en-us/library/aa272268(office.11).aspx). All I found was this: http://msdn.microsoft.com/en-us/library/aa214585(office.11).aspx, which gives xlUp, xlDown, xlToRight, and xlToLeft as potential arguments to End.
>
> 2) Although the output was correct (B=5*A), the cells didn't appear the same as if I had just done this in Excel. For example, if I go to Excel and click in B10, the formula looks like '=5*A10:A59' rather than '=5*A10'.
>
> Any help would be much appreciated.

Re: More details -- actxserver('excel.application')

Andy Eisenberg — Mon, 06 Jul 2009 20:01:05 -0400

Yeah, that works. But I was hoping to avoid looping in favor of a vectorized method. In this case, there's no point trying to work around this in MATLAB. It's far simpler to just do this in Excel (that is what it was made for after all), and then pull the resulting data into MATLAB.

Thanks.

Re: More details -- actxserver('excel.application')

Ilya Rozenfeld — Tue, 07 Jul 2009 13:24:01 -0400

There is nothing wrong with using loops. But if you like here is vectorized version

N = 50;
x = (1:N)';
exlFormula = arrayfun(@(x) ['=5*A' num2str(x)], x, 'uni', 0);
sheet.Range(['B1:B' num2str(N)]).Value = exlFormula;

"Andy Eisenberg" <theorigamist@gmail.com> wrote in message <h2tl61$9u4$1@fred.mathworks.com>...
> Yeah, that works. But I was hoping to avoid looping in favor of a vectorized method. In this case, there's no point trying to work around this in MATLAB. It's far simpler to just do this in Excel (that is what it was made for after all), and then pull the resulting data into MATLAB.
>
> Thanks.