How to use Excel application, how can i know all the functions and ways to use it?

Hello,

I have to do a lot of stuff in excel through matlab. I found out the easiest way to do all i wanna do is using excel aplication.

excel = actxserver('Excel.Application');

I wanted to know all the functions and possibilities that i can do like creating a graph or making multiple graphs write and delete some sheets and others.

Is there a place where i can get the information for this Excel.Application.

[dpb]

Why do you "have" to use Excel to do this? If you have MATLAB, it would generally be much easier and faster to do the work in MATLAB and then (perhaps) export the results to Excel.

Starting to code Excel via activeX in Matlab is a long, long, long learning experience.

That said, if you really want to see and try, either open the help for VBA in excel or go to the MS site <Microsoft Office VBA API Excel>

The problem you'll undobutedly run into is that all the VBA syntax isn't available at COM level nor the inline help inside the VBA editor like for MATLAB syntax in the integrated editor. It's not entirely trivial to translate from one to the other...and you'll often crash Excel and leave zombie processes in memory on the way. All in all, for other than the rudimentary if not a VBA maven you'll probably find you've bitten off more than you care to chew...

[dpb]

That said, give us a clue of what it is that you want to accomplish that you think would be easier in Excel -- chances are, can show how to do it even more simply in Matlab.

[Bernardo Macara]

The thing is i need to evaluate some values and put them in excel, i accomplish it by using write table and other functions, but then it would help if i make a plot in excel. I need to analyze the data in excel and make matlab simply run everything smoothly. My main problem is making plots in excel from matlab, the only way i found was using this excel.aplication and making charts, the examples i had were a bit different from what i wanted. using the Excel aplication making a chart sheet i wanted to make several charts in the same sheet with different values and series, thats the main goal.

[dpb]

Don't see anything in that description that would necessitate analyzing the data in Excel...and can't imagine it couldn't be simpler in Matlab to do so.

See https://www.mathworks.com/matlabcentral/fileexchange/69528-xlsputimage?s_tid=FX_rc1_behav for saving a MATLAB image file of a graphic to Excel -- it's not an Excel chart, but will show whatever it is that was graphed in Excel.

Alternatively, only real way I would see to proceed would be to create a macro in Excel that does what you want the plot to do and then invoke it from MATLAB.

Still seems like the hard way to go about doing business to me -- what part does Matlab play, anyway? If you want to use Excel, then do so won't hurt our feelings but why try to drive Excel from Matlab for anything more than just putting data into it to give to somebody without Matlab or the like just seems like an exercise in futility to me.

[Bernardo Macara]

I have a bunch of data that needs to be evaluated (done) , then i need to store it into excel (done). Joining a lot of data from different sources into one excel, so i need to do a bunch of graphs to evaluate the tendecies (undone), and then store this graphs into excel too. Thats about it.

I am gonna see the function you are saying.

[dpb]

What will be done with the graphs in Excel that couldn't be done with the figures in Matlab?

If you would provide a sample data set and what is wanted to be done, someone could/would likely be able to show a pretty simple way to accomplish in Matlab.

I don't know anything about the above FEX submission; found it by searching for something that seemed to fit the scenario you were looking for so good luck if it works for you...

As you can tell, I'm not that enamored w/ Excel and particularly ActiveX -- I've just spent a month writing code to extract data from a whole bunch of Excel spreadsheets so could analyze/summarize it across some 100 workbooks. This was pretty painful for several reasons, one of which was that metadata was stored as comment in a cell and needed to be able to parse the cell formulas to track individual data entries for a cross comparison to separate databases.

IF one were really already proficient in VBA for Excel, this undoubtedly could have been done all in VBA; not being that person nor having anybody at hand who was, it necessitated using ActiveX to retrieve those pieces from the spreadsheets. Writing and debugging that code was simply terribly painful and time consuming owing to the reasons outlined above -- there is no clear documentation trail that covers actually writing ActiveX from MATLAB so one is left trying to decipher what it was that failed when something went wrong from less than helpful error messages (if any errorm message at all). And, every time that happens, odds are that the Excel process has been corrupted and orphaned in memory that requires killing in TaskManager before being able to try again.

If you've looked at the link, you'll see the Object Model is gargantuan -- just figuring out what pieces are those you need is a struggle.

As noted, one can try the expedient of recording macros for bits and pieces of what one wants to do and then try to translate that into ActiveX code, but even that isn't foolproof. Jeremy says "is in VBA, but the same features should be available." but I've discovered even that isn't true -- there are quite a few higher-level abstraction from the low-level calls available in VBA that are not interfaces that aren't duplicated directly. Unfortunately, it's not always clear when one is looking at one of these.

Anyways, just some context/background on my view...then again, of course, I don't have your specific job nor assigned task, either, just trying to see if maybe there's an easier way to get the result that might not need Excel in case hadn't thought of it...just assumed that Excel would be needed.

Answer 1

[Jeremy Hughes]

Active X is a Microsoft feature, controling the application depends on the APIs the application exposes. For Excel, you should check: https://docs.microsoft.com/en-us/office/vba/api/excel.application(object)

This is in VBA, but the same features should be available.

[Bernardo Macara]

exactly what i wanted, thank you so much, great. i appreciate it a lot.