Path: news.mathworks.com!not-for-mail
From: "Ilya Rozenfeld" <rozeni.nospam@alum.rpi.edu>
Newsgroups: comp.soft-sys.matlab
Subject: Re: More details -- actxserver('excel.application')
Date: Mon, 6 Jul 2009 18:29:02 +0000 (UTC)
Organization: Citizens Bank
Lines: 31
Message-ID: <h2tfpe$7jn$1@fred.mathworks.com>
References: <h2iud6$c94$1@fred.mathworks.com> <h2j2th$bbf$1@fred.mathworks.com> <h2tdva$20o$1@fred.mathworks.com>
Reply-To: "Ilya Rozenfeld" <rozeni.nospam@alum.rpi.edu>
NNTP-Posting-Host: webapp-05-blr.mathworks.com
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit
X-Trace: fred.mathworks.com 1246904942 7799 172.30.248.35 (6 Jul 2009 18:29:02 GMT)
X-Complaints-To: news@mathworks.com
NNTP-Posting-Date: Mon, 6 Jul 2009 18:29:02 +0000 (UTC)
X-Newsreader: MATLAB Central Newsreader 918333
Xref: news.mathworks.com comp.soft-sys.matlab:553183


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.