How to insert an image into a specific Excel cell?

In Excel, I can place an image into a specific cell using Insert -> Pictures -> Place in Cell; this inserts the image into the cell, not floating over the cell. Can the same thing be done from MATLAB using ActiveX? Note I already have working code to insert a floating image in any desired location. What I really need to do is to insert it into a cell of my choosing.

Answer 1

[dpb]

Record a macro doing the desired operation and then look up the functions/methods referenced to in it in the <VBA for Excel documentation>; it will show you all the details on syntax.

Converting VBA to ActiveX in a form for MATLAB syntax is an exercise in replacing higher-level VBA constructions like using block constructs, etc., with specific calls from object handles that MATLAB can recognize, but it can be done with enough patience. Be prepared to restart Excel and kill crashed processes; setting a breakpoint and executing proposed tries from the command line before letting the code crash on first attempts can alleviate much grief, or putting each line in a try...catch block with breakpoints in the catch side although that can get tedious. Remember to close the workbook and the ActiveX to not leave zombie processes hanging around...but, it's almost guaranteed you'll crash it many times before getting something complicated to work...

[DDC]

I selected a cell in a blank Excel spreadsheet and inserted a .png image into the cell. Here's the corresponding macro:

Sub Macro5()
'
' Macro5 Macro
'
'
Range("C10").Select
Selection.InsertPictureInCell ("C:\Users\MyDir\Pictures\picture.png")
End Sub

Here's my test MATLAB script;

excel = actxserver('Excel.Application');  % Create server object
excelWorkbook = excel.Workbooks.Open(filename);   % Add a workbook
excelSheet = excel.ActiveSheet;           % Get the active sheet

excelRange=excelSheet.Range(xlcell);
excelRange.Select;

Selection.InsertPictureInCell (imagefile);

excelWorkbook.Save();
excelWorkbook.Close();                    % Close workbook
excel.Quit();                             % Quit server
excel.delete();                           % Delete server object

I get the error message "Unable to resolve the name Selection.InsertPictureInCell" with or without the space before (imagefile). I've tried a number of other permutations with no success. Any suggestions?

[dpb]

While not defined in the code snippet pasted, I presume xlcell is defined?

The problem in MATLAB is that it doesn't have access to the VBA compiler so it doesn't know what 'Selection' refers to that VBA does know behind the scenes. To MATLAB it is an undefined variable at that point as far as the MATLAB compiler knows. That's the thing in the previous note about the higher-level constructs used in building macros; they give you the path, but there are generally a lot of intermediates such as this that you have to fill in to get to the penultimate method/property.

It is excelRange that is the handle to the object; it should be able to see the method.

excelRange.InsertPictureInCell(imagefile);

may work without the explicit selection, but it's safer to mimic the specific VBA code. To do that you would return the object handle from the Select method and use it...

...
excelCell=excelRange.Select;                % return the selected cell reference
excelCell.InsertPictureInCell(imagefile);   % then insert the image
...

You may be able to avoid the second reference here, but I've found it's generally better to follow along; the two handles may be the same, but the internal state of the workbook isn't guranteed to be the same; prior to the .Select method, the selected cell most likely is not the target cell.

Sometimes you can get one additional level of referencing; I forget otomh whether .Range is one of those or not, but

excelCell=excelSheet.Range(xlcell).Select;   % return the selected cell reference

may work...again, be sure to set breakpoint first and try rather than letting code run and crash and burn...

[DDC]

I tried this and several other permutations, keep getting "Dot indexing is not supported for variables of this type." I found a workaround that gets me to the desired end. Rather that inserting an image file into a cell, I use imread to create a MATLAB image from the file, print to copy it to the clipboard, then

excelSheet.Range(xlcell).PasteSpecial();
excelSheet.Shapes.Item(1).PlacePictureInCell();

to insert the figure into Excel. The figure is actual in the cell and not just floating over the cell.

[dpb]

Did the VBA macro exercise actually do what was wanted as well?

I've never tried to do such a thing even in Excel itself, didn't really know it could be done, so no experience and don't have time to go try at the moment...well, it doesn't take long to at least poke at it...

K>> FF=fullfile('C:\Users\Duane\Documents\Book1.xlsm');
K>> excel=actxserver('Excel.Application');
K>> wbk=excel.Workbooks.Open(FF)
wbk =
	Interface.000208DA_0000_0000_C000_000000000046
K>> wksht=wbk.ActiveSheet
wksht =
	Interface.000208D8_0000_0000_C000_000000000046
K>> wksht.Name
ans =
    'Sheet1'
K>> range=wksht.Range('A1')
range =
	Interface.00020846_0000_0000_C000_000000000046
K>> range.Select
ans =
  logical
   1
K>> wksht.Range('A1').Select  % can use double dot addressing here, too
ans =
  logical
   1
K>> range.InsertPictureInCell % above returned by tab completion at command line for range

The above discovered that the range object thinks there is a method named InsertPictureInCell, but the range object methods listed in the VBA for Excel documentation don't show it...I don't recall ever running into that before...anyways, proceeding on following the macro outline--

I could not find documentation on the method/function other than a reference to a user-written function of the same name in C#/.Net syntax that illustrated lower level methods to do the work...but it has a different calling syntax including the cell row, column, as initial parameters. End result is I am not sure from where the reference came, but it showed up here as working precisely as the macro code suggests it should--carrying on from above:

K>> p=pwd;
K>> range.InsertPictureInCell(fullfile(p,'area.jpg'))
K>> excel.ActiveWorkbook.Save       % write the updated file
K>> winopen(FF)                     % open it and see if it worked...indeed, it did!!!
K>> excel.ActiveWorkbook.Close(0)   % clean up...
K>> excel.Quit; delete(excel);clear excel

actually put the jpg image in the spreadsheet here...

On my earlier comment in response, I had forgotten that the .Select method returns a logical indicating success/failure and not the object handle so that was wrong, sorry.