12

SYNOPSIS-

I need to copy an image (.png format) embedded in one Excel picture object into another existing picture (overwriting its image data), while leaving intact the target shape's identity and fields (position, visibility, size, title, etc). This is with simple pictures, not ActiveX OLEObjects.


SITUATION:

I'm on Windows 7, Excel 2010.

I've got a sheet with multiple picture objects in the UI.

Depending on a user's setting, the displayed pictures need to change frequently. I've got the triggering and so forth all set up.

Sometimes, a single shape will need to display one of several different pictures. I'd like to just copy in the picture data from the "library" picture (embedded in another shape, hidden from the user) to that destination, but I can work around this by simply swapping in and out the picture objects.

However, sometimes multiple shapes in the UI will need to display the same image, even while retaining their unique data (name, id, size, visibility, title, etc). The "swap" trick on that is prohibitive - I'd have to create fifty copies of every single "library" object, one for each instance in the UI that might need to take on that appearance.


DESIDERATUM:

So what I'd like to do is to simply have one library copy of those pictures, and be able to copy that image into the picture objects that are integrated into the UI.


PROBLEM:

I can't find how to do this.

I can't find any field within the VBA data structures which corresponds to the "image" part of a picture object.

CopyPicture() and then Paste() appears to work only when the destination for Paste() is an address (e.g Range("A1")), and then it creates a new picture object. But I don't want a new object, and I don't want to paste into a cell. I want to overwrite the image data of an existing picture object with the image data of another picture object.

Duplicate() and similar routines are variations on the above - they create a new picture object. I've got particular events etc tied to the existing object, the destination object, whose image data I'm trying to change, so I have to change its image, not replace it with some newly created object every time.

LoadPicture() - which goes via the hard drive rather than the clipboard (slower and, thus, less desirable) doesn't seem to work on shape objects, only OLEObjects. Would I have to change all those picture objects into ActiveX images? That's a whole lot of overhead for static pictures! And I expect all that disk reading & writing will bog down the sheet noticably.

ActiveX - I could change these picture objects into activeX image objects, then change the 'library' objects, and then use an assignement (Shapes("Name").DrawingObject.Object.Picture = Shape("Library").DrawingObject.Object.Picture). [This approach is along the lines of the approach here - Excel VBA: Copying Pictures from image controls to activeX objects ] I'm not sure I can make that work for my situation. Not only have I already designed much of the UI to take advantage of characteristics and functionality which Shape/Pictures have and OLEObjects do not (such as 3d formatting and the particular way 'fill' works on a picture object), but also manipulating OLEObjects is markedly slower than manipulating simple pictures, enough, in my implementation, to be frustrating to the user. (That's the one of the main reasons I switched away from using OLEObjects in an earlier stage of development.]


Essentially, I want to do the equivalent of the "Change Picture" command one gets when right-clicking on a picture object (except getting the image data from another picture object or, if necessary, the clipboard, not from a file on disk). But when I record a macro of that action it comes up empty - apparently "Change Picture" doesn't produce any macro code.

A whole bunch of web searching and documentation browsing hasn't turned up anything else.


um.... help?

Community
  • 1
  • 1
user2157978
  • 121
  • 1
  • 4
  • Are you doing this on a Windows machine, or under Mac OS. It turns out that for things like this, they don't behave in quite the same way (and thanks a lot, Microsoft)... What is the version of Excel you are using? – Floris Mar 11 '13 at 19:24
  • Have you checked http://excel.tips.net/T003128_Displaying_Images_based_on_a_Result.html out? It requires Excel 2010 to create the sheet, but once you have the sheet it seems to work in Excel 2007 as well. – GTG Mar 11 '13 at 20:40
  • Have you looked at Shape.Duplicate ? – grahamj42 Mar 11 '13 at 20:47
  • Thanks to respondants so far:
    * Version/Machine - sorry, should have added that. Windows, Excel 2010.

    Shape.Duplicate - unfortunately that simply creates a new shape, essentially a shorthand for copy/paste. The "destination" variable is a Range: i.e. it directs the new copy to a particular cell. I need to overwrite the image data in an existing picture.
    * excel.tips: neat trick, but sadly not one which will work for me. I'm moving these shapes around independent of cell locations; the only solution, short of rewriting the sheet, is to copy an image *into* an existing shape object.
    – user2157978 Mar 12 '13 at 01:08
  • Update - I've now tried fudging this by creating a group object that contains an acitveX picture positioned over a shape object. This allows me both behaviors the UI needs, though at the cost of more VBA coding (no biggie) and triple the number of Excel objects (better than some alternatives). But, as expected (and as happened with an earlier version of the sheet), dragging around this group object (with the ActiveX picture control) is *much* slower and laggier than with a simple shape. On-the-fly changing of a regular picture object's image is still the ideal solution. Any other suggestions? – user2157978 Mar 14 '13 at 17:34
  • Have you considered using some Windows API calls to accomplish this, maybe starting with [BitBlt()](http://support.microsoft.com/kb/147810)? – Ross McConeghy Mar 15 '13 at 15:45
  • You may also find the API functions in [this SO post](http://stackoverflow.com/questions/9429048/outlook-how-to-make-a-screenshot-of-the-entire-e-mail-body-without-scrolling) helpful. – Ross McConeghy Mar 15 '13 at 15:59
  • BitBlt - Thanks for those links. I've not used API commands before, so I did some more searching & reading on BitBlt. If I understand correctly, it's used to capture a selection as a bitmap, from the screen or another bitmap, into a new bitmap. And I can see how this would work with an OLEObject, which has a "Picture" field pointing to the bitmap. But, with such pictures, I can already get that behavior (see OP, above, on ActiveX). But not with a picture that's a *shape* object. How can I find the address of such a shape's bitmap in order to use BitBlt to copy from one to another? – user2157978 Mar 16 '13 at 17:06
  • This SO question and answer should help: [here](http://stackoverflow.com/questions/28489836/how-to-show-a-picture-in-a-cell-depending-on-the-value-of-another-cell/28489837#28489837) – HarveyFrench Feb 13 '15 at 00:50

1 Answers1

2

I hate to say this, but I think you have painted yourself into a corner on this one. Excel's objects do not support modifying picture data in the way you describe, which means that you cannot make these changes while the sheet is open. Even if you figured out how to make changes to the raw file, it does not satisfy your UI requirements.

Your workaround is probably your best (Excel-based) answer.

Neil Mussett
  • 710
  • 6
  • 8