Syntax for calling Excel COM (actxserver) methods in Matlab - Specifically the 'Find' method

1
votes

I have search and search and just can't seem to figure this out. I think this is a pretty general question applicable to a lot of methods, but mine is specifically for 'Find'.

I am data mining AND writing to an Excel spreadsheet in the Matlab environment. I have been able to do quite a bit but I've been using a work around for something I can't figure out for over a year and it really slows things down.

I need to call the 'Find' method to look for specific values of cells.

Here is where I'm at... Excel = actxserver('Excel.Application'); Sheet = Excel.Worksheets.Item(mysheet); Sheet.Activate; cellObj = Sheet.Cells;

Now... here is what I need to do. I want to find a cell that contains EXACTLY the value 'myData_1'. So I try to use...

cellObj.Find('myData_1')

of course 

invoke(cellObj,'Find','myData_1')

also works..

Either way the call does find a cell, but the cell actually contains 'myData_10'. Since the default 'Find' method parameters in Excel are only for a partial match.

So I recorded a macro and discovered that there is a 'Find' property called 'LookAt: = xlPart'. Also I discovered that if I set that property to 'xlWhole' it will be an Exact match per the Microsoft Developer website.

Now, back to the Matlab environment. According to cellObj.methodsview, I should be able to call 'Find' with (handle,Variant,Variant(Optional))...

So, I tried...

invoke(cellObj,'Find','myData_1','LookAt:=xlWhole').

I also tried about 50 other combinations of syntax as well. I just CAN NOT figure out what the proper syntax is to search for the cell value I want AND change the 'LookAt' property.

Can anyone please help me? And please, to make this thread productive, do not ask why I am doing anything with Excel, there is a very good reason for my work to be done this way.

Thanks in advance, Justin

1
excelmatlabmethodscom

1 Answers

0
votes

I think if you use cellobj.Find('myData_1',[],[],1) it should do what you need.

If you take a look at the documentation page for Range.Find you'll see that the LookAt parameter is the fourth, so I've put empty arrays to leave the second and third arguments as default. The constant 1 corresponds to xlWhole, 2 corresponds to xlPart. (I guessed that and it worked - I've never been sure how to work out what numbers the enumerated constants in Excel correspond to, but often they are an initial set of integers).

Hope that helps!