How to insert an image into a specific Excel cell?

Question

DDC 2024-9-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2154275-how-to-insert-an-image-into-a-specific-excel-cell

编辑： dpb 2024-9-25

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

dpb 2024-9-20

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2154275-how-to-insert-an-image-into-a-specific-excel-cell#answer_1520210

移动：dpb 2024-9-20

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...

4 个评论
显示 2更早的评论隐藏 2更早的评论

dpb 2024-9-24

编辑：dpb 2024-9-24

在 MATLAB Online 中打开

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 2024-9-24

在 MATLAB Online 中打开

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 2024-9-24

编辑：dpb 2024-9-25

在 MATLAB Online 中打开

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.

请先登录，再进行评论。

How to insert an image into a specific Excel cell?

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

回答（1 个）

4 个评论
显示 2更早的评论隐藏 2更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

How to insert an image into a specific Excel cell?

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

回答（1 个）

4 个评论 显示 2更早的评论隐藏 2更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论