Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
Excel actxserver function reference?

Subject: Excel actxserver function reference?

From: Marshall

Date: 11 Dec, 2010 01:17:21

Message: 1 of 9

Is there an exhaustive list of all possible things I can do with the Excel ActiveX server in MatLab?

By endlessly searching and experimenting, I've learned how to do many things such as setting formatting, but I have yet to figure out how to make my workbook select a specific sheet or range of cells. I can manipulate sheets and cell ranges, but I can't seem to find a command that will change which sheet is being shown and highlight (select) a range of cells.

For my project, I use xlswrite to create a multi-sheet workbook. When I open the workbook, the last sheet is always the one that is selected and the range of cells I wrote to is the current selection. I want it so the first sheet is the active one when I open it and I want cell A1 to be the current selection.

When I use actxserver to format the workbook, I have enabled visibility so I can see what's happening. As I work on the various sheets, it does now show the sheet I am working on and the range selection never changes. I have to manually change the active sheet in Excel to see the one being worked on. I've wasted hours trying to figure this out. Some sort of reference would really be helpful.

Here are some of the things I have done:

ExObj = actxserver ( 'Excel.Application' ); % Start Excel
ExObj.Visible = 1; % Make it visible
AllBooksObj = ExObj.Workbooks; % No idea what this does, but it's required
WkBkObj = AllBooksObj.Open( 'C:\MyDir\MyWorkbook.xlsx'); Open workbook
AllSheetsObj = WkBkObj.Sheets; % Object containing all the sheets
NumSheets = AllSheetsObj.Count; % Get the number of sheets in workbook
SheetObj = get( AllSheetsObj, 'Item', n ); % Get sheet #n
SheetObj.Delete; % Delete the sheet
RngObj = SheetObj.Range('B2:C9') % object pointing to a range
RngObj.Font.Bold = true; % Make the contents in the range bold
RngObj.Interior.ThemeColor = 5; % Use color theme #5 (5th column in color table)
RngObj.Interior.TintAndShade = 0.6; % Set brightness to 0.6 (valid = -1.0 to 1.0)
RngObj.HorizontalAlignment = -4152; % Right justify the contents
RngObj.cells.EntireColumn.ColumnWidth = 10; % Set column width to 10.
RngObj.cells.EntireColumn.AutoFit(); % Autofit column to contents
RngObj.cells.EntireColumn.NumberFormat = '0.00E+00'; % Use scientific notation
WkBkObj.Save; % Save workbook
WkBkObj.Close( false ); % Close workbook (false = no prompt?)
ExObj.Quit % Quit Excel
ExObj.delete % Not sure what this does. ExObj still exists afterward

I recorded some Excel macros and then examined them to figure out some things, found examples online for some, and just made lucky guesses for others.

Subject: Excel actxserver function reference?

From: Donn Shull

Date: 11 Dec, 2010 18:06:04

Message: 2 of 9

"Marshall" <marshall_buhl_nospam@nrel.gov> wrote in message <idujf1$oli$1@fred.mathworks.com>...
> Is there an exhaustive list of all possible things I can do with the Excel ActiveX server in MatLab?
>
> By endlessly searching and experimenting, I've learned how to do many things such as setting formatting, but I have yet to figure out how to make my workbook select a specific sheet or range of cells. I can manipulate sheets and cell ranges, but I can't seem to find a command that will change which sheet is being shown and highlight (select) a range of cells.
>
> For my project, I use xlswrite to create a multi-sheet workbook. When I open the workbook, the last sheet is always the one that is selected and the range of cells I wrote to is the current selection. I want it so the first sheet is the active one when I open it and I want cell A1 to be the current selection.
>
> When I use actxserver to format the workbook, I have enabled visibility so I can see what's happening. As I work on the various sheets, it does now show the sheet I am working on and the range selection never changes. I have to manually change the active sheet in Excel to see the one being worked on. I've wasted hours trying to figure this out. Some sort of reference would really be helpful.
>
> Here are some of the things I have done:
>
> ExObj = actxserver ( 'Excel.Application' ); % Start Excel
> ExObj.Visible = 1; % Make it visible
> AllBooksObj = ExObj.Workbooks; % No idea what this does, but it's required
> WkBkObj = AllBooksObj.Open( 'C:\MyDir\MyWorkbook.xlsx'); Open workbook
> AllSheetsObj = WkBkObj.Sheets; % Object containing all the sheets
> NumSheets = AllSheetsObj.Count; % Get the number of sheets in workbook
> SheetObj = get( AllSheetsObj, 'Item', n ); % Get sheet #n
> SheetObj.Delete; % Delete the sheet
> RngObj = SheetObj.Range('B2:C9') % object pointing to a range
> RngObj.Font.Bold = true; % Make the contents in the range bold
> RngObj.Interior.ThemeColor = 5; % Use color theme #5 (5th column in color table)
> RngObj.Interior.TintAndShade = 0.6; % Set brightness to 0.6 (valid = -1.0 to 1.0)
> RngObj.HorizontalAlignment = -4152; % Right justify the contents
> RngObj.cells.EntireColumn.ColumnWidth = 10; % Set column width to 10.
> RngObj.cells.EntireColumn.AutoFit(); % Autofit column to contents
> RngObj.cells.EntireColumn.NumberFormat = '0.00E+00'; % Use scientific notation
> WkBkObj.Save; % Save workbook
> WkBkObj.Close( false ); % Close workbook (false = no prompt?)
> ExObj.Quit % Quit Excel
> ExObj.delete % Not sure what this does. ExObj still exists afterward
>
> I recorded some Excel macros and then examined them to figure out some things, found examples online for some, and just made lucky guesses for others.

Hi Marshall,

I don't know of an exhaustive list of the things you can do with an Excel actxserver object. You can probably find most of the information that you are looking for at Microsofts website. As far as MATLAB goes, when working with actxserver objects you can find out a lot about the object using get, methods, and methodsview. You can also use the MATLAB dot notation to simplify some of your commands for example:

SheetObj = get( AllSheetsObj, 'Item', n );

Can be written using dot notation as

SheetObj = AllSheetsObj.Item(n);

And this can be accessed from the root Excel object as:

SheetObj = ExObj.Workbooks.Item(1).Sheets.Item(n);

As far as your specific question you can use the Activate method to cause specific things to be displayed. In your example

AllSheetsObj.Item(1).Activate; % display the first sheet
AllSheetsObj.Item(1)..Range('A1:A1').Activate % Select the first cell

Hope this helps,

Donn

Subject: Excel actxserver function reference?

From: Yair Altman

Date: 11 Dec, 2010 21:18:04

Message: 3 of 9

Using the uiinspect utility should help you to explore the available properties, methods and callbacks of the Excel objects:
http://www.mathworks.com/matlabcentral/fileexchange/17935-uiinspect-display-methods-properties-callbacks-of-an-object

Yair Altman
http://UndocumentedMatlab.com

Subject: Excel actxserver function reference?

From: Philip Nienhuis

Date: 12 Dec, 2010 21:50:44

Message: 4 of 9

Marshall wrote:
> Is there an exhaustive list of all possible things I can do with the
> Excel ActiveX server in MatLab?

Don't know if it is what you want, but I've learned most Excel stuff by
perusing
- the Visual Basic Help in Excel itself and
- the Visual Basic Object Browser (in Excel: Tools | Macro | Visual
Basic Editor F11, once there hit F2 for the Object Browser.) At least,
that's in my old Excel '97 (at home), should work similarly in other
versions.

Very enlightening to do this with different Excel versions, it'll learn
one that (or why) much existing Matlab/Excel code is not as portable
across different Excel versions as one might be inclined to expect.

P.

Subject: Excel actxserver function reference?

From: Marshall

Date: 13 Dec, 2010 16:54:20

Message: 5 of 9

"Donn Shull" <donn.shull.no_spam@aetoolbox.com> wrote in message <ie0eic$jtm$1@fred.mathworks.com>...
> Hi Marshall,
>
> I don't know of an exhaustive list of the things you can do with an Excel actxserver object. You can probably find most of the information that you are looking for at Microsofts website. As far as MATLAB goes, when working with actxserver objects you can find out a lot about the object using get, methods, and methodsview. You can also use the MATLAB dot notation to simplify some of your commands for example:
>
> SheetObj = get( AllSheetsObj, 'Item', n );
>
> Can be written using dot notation as
>
> SheetObj = AllSheetsObj.Item(n);
>
> And this can be accessed from the root Excel object as:
>
> SheetObj = ExObj.Workbooks.Item(1).Sheets.Item(n);
>
> As far as your specific question you can use the Activate method to cause specific things to be displayed. In your example
>
> AllSheetsObj.Item(1).Activate; % display the first sheet
> AllSheetsObj.Item(1)..Range('A1:A1').Activate % Select the first cell
>
> Hope this helps,
>
> Donn

Donn,

The Activate method(?) is exactly what I was looking for. Thanks to you, I have now finished my program. Yay!

Thanks, also, to all the others who responded.

Marshall

Subject: Excel actxserver function reference?

From: Marshall

Date: 13 Dec, 2010 17:06:22

Message: 6 of 9

"Yair Altman" <altmanyDEL@gmailDEL.comDEL> wrote in message <ie0pqc$8i1$1@fred.mathworks.com>...
> Using the uiinspect utility should help you to explore the available properties, methods and callbacks of the Excel objects:
> http://www.mathworks.com/matlabcentral/fileexchange/17935-uiinspect-display-methods-properties-callbacks-of-an-object
>
> Yair Altman
> http://UndocumentedMatlab.com

I'm afraid I couldn't get this to do anything useful in MatLab 7.10.0. For instance:

uiinspect(actxserver('Excel.Application'));
??? Undefined function or method 'uiinspect' for input arguments of type 'COM.Excel_Application'.

But, thanks for trying to help.

Marshall

Subject: Excel actxserver function reference?

From: Marshall

Date: 13 Dec, 2010 17:09:05

Message: 7 of 9

Philip Nienhuis <nospam@spamcop.org> wrote in message <4d0543b4$0$81481$e4fe514c@news.xs4all.nl>...
> Marshall wrote:
> > Is there an exhaustive list of all possible things I can do with the
> > Excel ActiveX server in MatLab?
>
> Don't know if it is what you want, but I've learned most Excel stuff by
> perusing
> - the Visual Basic Help in Excel itself and
> - the Visual Basic Object Browser (in Excel: Tools | Macro | Visual
> Basic Editor F11, once there hit F2 for the Object Browser.) At least,
> that's in my old Excel '97 (at home), should work similarly in other
> versions.
>
> Very enlightening to do this with different Excel versions, it'll learn
> one that (or why) much existing Matlab/Excel code is not as portable
> across different Excel versions as one might be inclined to expect.
>
> P.

That's pretty slick. The F2 works with Excel 2007 too, once you get into VB. Thanks!

Marshall

Subject: Excel actxserver function reference?

From: Yair Altman

Date: 13 Dec, 2010 21:56:05

Message: 8 of 9

"Marshall" <marshall_buhl_nospam@nrel.gov> ...
> "Yair Altman" <altmanyDEL@gmailDEL.comDEL> wrote ...
> > Using the uiinspect utility should help you to explore the available properties, methods and callbacks of the Excel objects:
> > http://www.mathworks.com/matlabcentral/fileexchange/17935-uiinspect-display-methods-properties-callbacks-of-an-object
>
> I'm afraid I couldn't get this to do anything useful in MatLab 7.10.0. For instance:
>
> uiinspect(actxserver('Excel.Application'));
> ??? Undefined function or method 'uiinspect' for input arguments of type 'COM.Excel_Application'.


Marshall - this simply indicates that you have not installed uiinspect correctly in your Matlab path, and so when you call uiinspect it does not recognize the function. Perhaps you just downloaded the zip file without extracting it?

- Yair

Subject: Excel actxserver function reference?

From: Marshall

Date: 14 Dec, 2010 21:23:04

Message: 9 of 9

"Yair Altman" <altmanyDEL@gmailDEL.comDEL> wrote in message >
Marshall - this simply indicates that you have not installed uiinspect correctly in your Matlab path, and so when you call uiinspect it does not recognize the function. Perhaps you just downloaded the zip file without extracting it?

Doh! I didn't even download it. I went straight to the part about how to use it and didn't realize it was not part of the standard MatLab.

Anyway, it's pretty cool. It may help me on my next project. Thanks!

Marshall

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us