Excel actxserver function reference?
Marshall
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.
Donn Shull
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
Yair Altman
http://www.mathworks.com/matlabcentral/fileexchange/17935-uiinspect-display-methods-properties-callbacks-of-an-object
Yair Altman
http://UndocumentedMatlab.com
Philip Nienhuis
> 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.
Marshall
> 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
Marshall
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
Marshall
That's pretty slick. The F2 works with Excel 2007 too, once you get into VB. Thanks!
Marshall
Yair Altman
> "Yair Altman" <altma...@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
>
>
> 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
Marshall
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