Exporting an Excel Range as BMP or GIF

[Siddharth Rout]

Hi All

I know how to export charts but now I want to export say Range("A1:L50") from say Sheet1 to a bitmap file so that I can display that in a picture box on the userform. The picture has to retain the formating of the Range.

I have searched on the net but couldn't find any code/hints.

Any help will be appreciated.

[VBAhack]

Don't know if this helps, but you can copy a picture of a range to the clipboard:

Range("B4:E5").CopyPicture Appearance:=xlScreen, Format:=xlBitmap

or

Range("B4:E5").CopyPicture Appearance:=xlScreen, Format:=xlPicture

You can do the same thing manually by selecting a range and clicking the edit menu while holding down the shift key.

[RobDog888]

You could use some API code to get the image off the clipborad and write it out to an image file.

[Siddharth Rout]

Hi All

Thanks for your replies...

@VBAhack: I am already know how to export the image to the clipboard, but how do i save it to a BMP or GIFF file?

@Rob: You know of any API that can help me?

[RobDog888]

OpenClipboard, GetClipboardData, and CloseClipboard.

[Siddharth Rout]

OpenClipboard, GetClipboardData, and CloseClipboard.

Err, Rob Like I said I know how to copy data to the clipboard. I want to save
that data to a BMP or a Giff File....

[RobDog888]

Once you have the data from the clipboard you would need to BitBlt API it to a DC where you could create a bitmap in memory and then save to a file. Its not really my area of knowledge but in the gaming forum they probably have threads on it.

[VBAhack]

This might be fruitful:

http://www.codeproject.com/csharp/cl...e_archiver.asp

http://www.codeguru.com/cpp/g-m/bitmap/

http://www.developer.com/lang/other/article.php/615101

[Siddharth Rout]

@Rob: Thx for the tip. Will check that out...
@VBAhack: I don't want to install a different utility. I want to do this from Excel using VBA code

I think I have been misunderstood ... I'll explain once again....

Using VBA Excel, I want to export say Range("A1:L50") from say Sheet1 to a bitmap file and save it as say myfile.bmp.

Hope this helps....

[VBAhack]

You weren't misunderstood. The thought was that perhaps buried in the pages associated with those links was an API or reference to an API that you could use. I didn't have the time to crawl through them in detail - figured you could do that.

[Siddharth Rout]

I have already checked them... nothing

[VBAhack]

You might try a post in the API forum. What you are really trying to do is get an image from the clipboard to a file.......

[VBAhack]

This might be promising:

http://vb.mvps.org/articles/ap200106.asp

Soup Up Office VBA by Karl E. Peterson

Q. Paste a Clipboard Object to VBA
.........
.........
Adding support for various graphical formats gets far more API-intensive. The main problem to overcome: making an in-memory copy of the data. I provided the CMemoryDC class for working with bitmaps in memory in my April column (see Resources); you can also find the class in this month's sample code, which you can download. I built into CMemoryDC the ability to "disconnect" a bitmap from its containing Picture object, as well as the ability to create a Picture object containing a copy of any bitmap given only the handle to the original.

Add bitmap support to your new custom CClipboard object by adding CMemoryDC to your VBA project. Then write your CClipboard class's GetData and SetData methods, which duplicate the corresponding text methods almost exactly, except they leverage the CMemoryDC class's unique capabilities to disconnect bitmaps from their containers (see Listing A).

[VBAhack]

http://www.vbforums.com/showthread.php?t=459484

[Siddharth Rout]

Hi VBAHack

Thanks for the link, I'll check it and post back the comments.

Also ----- I admire your patience

[VBAhack]

Hi All

I know how to export charts but now I want to export say Range("A1:L50") from say Sheet1 to a bitmap file so that I can display that in a picture box on the userform. The picture has to retain the formating of the Range.

I have searched on the net but couldn't find any code/hints.

Any help will be appreciated.

Hey koolsid,

I just stumbled on this. With a minor mod I was able to create a bmp of a range. See attached image below.

http://www.bmsltd.ie/DLCount/DLCount...stePicture.zip

'***************************************************************************
'*
'* MODULE NAME:     Paste Picture
'* AUTHOR & DATE:   STEPHEN BULLEN, Office Automation Ltd
'*                  15 November 1998
'*
'* CONTACT:         [email protected]
'* WEB SITE:        http://www.oaltd.co.uk
'*
'* DESCRIPTION:     Creates a standard Picture object from whatever is on the clipboard.
'*                  This object can then be assigned to (for example) and Image control
'*                  on a userform.  The PastePicture function takes an optional argument of
'*                  the picture type - xlBitmap or xlPicture.
'*
'*                  The code requires a reference to the "OLE Automation" type library
'*
'*                  The code in this module has been derived from a number of sources
'*                  discovered on MSDN.
'*
'*                  To use it, just copy this module into your project, then you can use:
'*                      Set Image1.Picture = PastePicture(xlPicture)
'*                  to paste a picture of whatever is on the clipboard into a standard image control.
'*
'* PROCEDURES:
'*   PastePicture   The entry point for the routine
'*   CreatePicture  Private function to convert a bitmap or metafile handle to an OLE reference
'*   fnOLEError     Get the error text for an OLE error code
'***************************************************************************