Path: news.mathworks.com!not-for-mail
From: <HIDDEN>
Newsgroups: comp.soft-sys.matlab
Subject: Re: More details -- actxserver('excel.application')
Date: Mon, 6 Jul 2009 17:58:02 +0000 (UTC)
Organization: The MathWorks, Inc.
Lines: 22
Message-ID: <h2tdva$20o$1@fred.mathworks.com>
References: <h2iud6$c94$1@fred.mathworks.com> <h2j2th$bbf$1@fred.mathworks.com>
Reply-To: <HIDDEN>
NNTP-Posting-Host: webapp-02-blr.mathworks.com
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit
X-Trace: fred.mathworks.com 1246903082 2072 172.30.248.37 (6 Jul 2009 17:58:02 GMT)
X-Complaints-To: news@mathworks.com
NNTP-Posting-Date: Mon, 6 Jul 2009 17:58:02 +0000 (UTC)
X-Newsreader: MATLAB Central Newsreader 1895050
Xref: news.mathworks.com comp.soft-sys.matlab:553178


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.