VBA/VB6 CommandButton Picture property

[dean12]

My problem is that I have an Excel Workbook (XLS) with a number of commandbuttons on various forms. Most of the commandbuttons have the picture property set with some type of image. I used to have a directory where I kept all of the images I had placed on the various worksheets but unfortunately I cannot find that directory now.

Anyone know of a utility/tool/procedure for getting the images out of the XLS file and written back to disk so I can recreate that directory of image files?

 

[fumei]

I do think the images are in the .xls file - are they?

Besides, I think the controls simply have pointers to the image files. Those pointers once set - either dynamically via LoadPicture, or through the control Picture file dialog - can not be returned as a string (i.e. the path/filename).

In other words, once you set a control Picture as, say "c:\images\blah.jpg", there is no way to GET "c:\images\blah.jpg" back out as a string.

"Anyone know of a utility/tool/procedure for getting the images out of the XLS file and written back to disk so I can recreate that directory of image files? "

I do not think this is possible, but I would be interested in hearing otherwise.

Gerry

 

[dean12]

I understand what you are saying. My guess is that the actual image is stored within the XLS file as I can send you the XLS, you open it, and you will see command buttons with 16x16 images embedded. I only need to send the XLS file.

Now if Excel is doing something cute like exporting all the images to some directory somewhere so that the mechanism you mention is followed then fine but I don't know where that directory is located.

 

[combo]

Maybe there's better way to get pictures, but you can get the picture from the control:

Private Sub cmdGetPicture_Click()
PictureName = "Path\Filename"
Select Case Me.cmdWithPicture.Picture.Type
Case 0
    PictureExt = "" ' none
Case 1
    PictureExt = ".bmp" ' bitmap
SavePicture Me.cmdWithPicture.Picture, PictureName & ".bmp"
Case 2
    PictureExt = ".wmf" ' metafile
Case 3
    PictureExt = ".ico" ' icon or cursor
Case 4
    PictureExt = ".emf" ' extended metafile
End Select
SavePicture Me.cmdWithPicture.Picture, PictureName & PictureExt
End Sub

You could modify the code, after referencing VBIDE you can access vba project and loop vbcomponents to search userforms, loop controls and test for pictures. All without running the form.

combo

 

[fumei]

Have you actually got a path/filename using the above? I sure can not.

SavePicture writes a NEW file...it does NOT get a path and filename of the existing graphics image file. If you read the reference for SavePicture:

Picture or Image control from which the graphics file is to be created.

My bolding.

As it is, PictureName (undeclared) is hard coded as "Path\Filename", so:

SavePicture Me.cmdWithPicture.Picture, PictureName & ".bmp"


is actually:

SavePicture Me.cmdWithPicture.Picture, "Path\Filename" & ".bmp"

That is not very useful.

But lets make a real example.

I have a control.

I have placed an image (to keep it simple...and I have actually done this right now) - "c:\cells.bmp" onto the control. So the path/filename is "c:\cells.bmp". Simple, yes?

Adjusting your code to use declared variables:

Private Sub CommandButton2_Click()
Dim PictureName As String
Dim PictureExt As String
PictureName = "c:\ZZZ\Combo"
Select Case Me.cmdWithPicture.Picture.Type
Case 0
    PictureExt = "" ' none
Case 1
    PictureExt = ".bmp" ' bitmap
SavePicture Me.cmdWithPicture.Picture, PictureName & ".bmp"
Case 2
    PictureExt = ".wmf" ' metafile
Case 3
    PictureExt = ".ico" ' icon or cursor
Case 4
    PictureExt = ".emf" ' extended metafile
End Select
SavePicture Me.cmdWithPicture.Picture, PictureName & PictureExt
End Sub

Result? A NEW file is created and saved - "c:\ZZZ\Combo.bmp"

Original graphics inserted onto the control? "c:\cells.bmp"

SavePicture result? "c:\ZZZ\Combo.bmp"

Your code does NOT get the path and filename of the existing graphic used on the control. I still think the .xls does NOT contain the original information, or at least if it does it is not exposed to VBA.

There is no Property that contains the path/filename, so how can you get it?

But again, if someone can come up with a way I would be interested. But sorry combo, I can not get your code to work. SavePicture writes a NEW file.

Gerry

 

[TonyJollans]

Hi Gerry,

I think you're reading more into the question than was asked.

(AFAIK) only the pictures, and not their original source or name, are held in the workbook, but combo's code, with a little tidying up and some added loops as he suggests, should meet the OP's need - getting the images out of the XLS file and written back to disk so I can recreate that directory of image files (my bolding).

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

Yes Tony...EXCEPT...

You have to give a NEW name for each file. And there is no way to get the original name. You can not know what it was.

So, yes, you can "recreate" a directory with the image files. But it is not really a recreation, it is a whole new creation and you must give a new name without knowing what the old name was.

So say, for example, you used the following for the images on the controls.

c:\Images\client.bmp
c:\Images\yadda.bmp
c:\Images\hoo-ha.bmp
c:\Images\thatOne.bmp

YES, you can get the images out and make new ones. But again, you MUST give them a new name, and you do NOT, and can not, get what the old name was.

It this is not important, then sure, by all means. You could extract the images, and use a counter, and make them:

SavePicture Me.cmdWithPicture.Picture, PictureName & _
      "image_" & i & ".bmp"
   i = i + 1

Getting something like:

c:\NewImages\image_1.bmp
c:\NewImages\image_2.bmp
c:\NewImages\image_3.bmp
c:\NewImages\image_4.bmp

And if that is OK, then fine. However, I will re-iterate that there is no real connection between the created files and the actual control it came from. The newly created image file is just a file.

It would take - and yes it could be done - additional code to extract the control name and say add it to the new filename.

If Control.Picture.Type <> 0 Then ' it HAS an image
sName = Control.Name & "_image"
Select Case Control.Picture.Type
Case 1
SavePicture Control.Picture, sName & ".bmp"

[/code]to get something like:

c:\NewImages\cmdYadda_image.bmp
c:\NewImages\cmdBlahBlah_image.bmp
c:\NewImages\cmdDoThis_image.bmp

Gerry

 

[TonyJollans]

Correct on all counts, Gerry.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[dean12]

Guys,

You certainly expended a bunch of energy on this question and I appreciate your efforts. I confess I got a bit lost when trying to put the thing together with loops to cycle the controls and extract the images.

Turns out..........

In design mode you can click on the commandbutton control, and on the properties sheet do the following:

Click on the Picture property word "(Bitmap)". Next CTRL-C and you grab the image. CTRL-V to save it somewhere. Never would I have thought of that.

Andy Pope has an Excel web site with add-ins and a really nice "Graphics Explorer" add-in. It doesn't do what I just described but in conversation with him that little trick was passed along.

 

[dean12]

And now that my very old and slow mind is getting into gear I am remembering that CTRL-X will delete the image from the control.

 

[fumei]

Click on the Picture property word "(Bitmap)". Next CTRL-C and you grab the image. CTRL-V to save it somewhere. "

My bolding.

Well...yes.

1. But is not "save it somewhere" a bit vague? Are you going to save it to a graphics app, say Paint, then do a SaveAs? Yes, you can do that.

2. It still will NOT have the original filename. Nor would Ctrl-C/Ctrl-V (a copy and paste) have any information of the control it came from.

3. This would have to be done manually, for each one.

"In design mode you can click on the commandbutton control"

I thought there was a desire to have some automation.

Gerry

 

[dean12]

Maybe I was not clear in the initial post - my apologies. I do want to be clear about this:

I appreciate everyone's efforts and responses. Your time is valuable and I recognize that. The outcome is that my problem is solved. I can get my hands on the image and save it.

You'll have to forgive me on the CTRL-C/CTRL-V/CTRL-X stuff as it relates to VB6/VBA controls. VB6 was already a dead language when I started messing with it.

As to automation, yes this would be fun and might make for a good exercise but my immediate need was only to get the silly images out of Excel.

 

[strongm]

>VB6 was already a dead language

It isn't dead yet, you know ...

 

[dean12]

Yea I know.

Guess it's up there on my skills shelf with FORTRAN, COBOL, PL/1, and APL

 

[fumei]

My point is that that you can extract ALL images of out ALL the controls, into any folder you like, named as you like, with ONE execution of a procedure.

Yes, it uses a loop of the controls, but that is a minor issue, and if you asked or searched, there are threads that have code for doing this.

You do NOT have to manually select each control, and do a copy/paste operation.

However, if it works for you to manually select each one, and get the image, then at least you will get what you want.

Gerry