VBA Save Range as a JPG File ?

[Ken1000]

Hi. I am using Excel 2000.

I have made a macro which will save a range on a Sheet
to a seperate Sheet as a Bitmap file on that Sheet (see code below).

The BMP file format saves images larger than the JPG
(or JPEG) format. Do you know of any VBA code which
could save save a specified range as a seperate, external
JPG file ? By "seperate, external" I mean independent of
Excel and in the same directory as the Excel workbook from
which it derived the picture. (Or, if not a JPG, perhaps as a
GIF file ?)

Or, if a seperate, external JPG file could not be generated
from Excel, could a JPG copy of the range be placed onto a
different Sheet (such as the code I have below).

Also, could such a macro get the name for the intended
JPG file from a cell on the Sheet. For example, from cell A5.
If cell A5 read as "Budget2005", then the JPG would be
saved as Budget2005.jpg.

Thank for your help.

Ken

'-------------------------------------------------

Sub CopyRangeAsBMP()

'choose the Sheet from which you need your picture
Worksheets("SourceSheet").Activate

'specify your range
Range("M11:R73").Select

Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
Range("A1").Select

'Goto the proper Target Sheet and Paste This
Sheets("DestinationSheet").Select

'select cell where Picture will be placed
Range("A1").Select

'Paste the Picture to cell
ActiveSheet.Pictures.Paste.Select

'Position Cursor
Range("M5").Select

End Sub

 

[Seti]

See if this link helps any:

http://www.mrexcel.com/archive2/48600/56518.htm

Another option is to Export as BMP and then convert to JPG. Irfanview does this very nicely and is a free download.

 

[Oorang]

Someone correct me if I'm wrong, but as I understand it, for a program (such as excel) to include jpeg support the vendor of the application has to pay a licensing fee (same with .gif if I am not mistaken). This means you usually will not find support for those compression formats unless there is an explicit need. However if you use WinNt (not sure about XP) you probably have photo editor (look for Photoed.exe). Since it is a microsoft product I would not be shocked if there was a way to make your code work with that object to save the photo as bitmap, convert it and bring it back into excel. That said... I have no idea how to do it.

 

[Seti]

Oorang,

In the link I gave, the process is to sort of paste the data into a blank chart which does have support for JPG exporting. I guess MS thought that no one would ever want a snapshot of their worksheet in compressed form.

Seti

 

[Ken1000]

Thanks, Seti and Oorang.

Seti, you mentioned exporting a range as a BMP. How would
you do this via VBA code ? The coding I'm aware of does a
capture of a range as a BMP, but it places it onto a Sheet.
How would you export that BMP alone to sit in your directory
and what name would it be ?


Ken

 

[Ken1000]

Thanks, Seti and Oorang.

Seti, you mentioned exporting a range as a BMP. How would
you do this via VBA code ? The coding I'm aware of does a
capture of a range as a BMP, but it places it onto a Sheet.
How would you export that BMP alone to sit in your directory
and what name would it be ?


Ken

 

[Seti]

From the link I gave you:

====================================================
Approach one.

The following code can, rather "painlessly", export an image to a BMP file.

It uses the PastePicture code from Stephen Bullen's site:

http://www.bmsltd.co.uk/Excel/Default.htm

Option Explicit

Private Sub SaveRngAsBMP(Rng As Range, FileName As String)
Rng.CopyPicture xlScreen, xlBitmap
SavePicture PastePicture(xlBitmap), FileName
End Sub

Sub TestIt()
Dim Rng As Range, Fn As String
Set Rng = Range("A1:A5")
Fn = "C:\MyFile.bmp"
SaveRngAsBMP Rng, Fn
End Sub

 

[Ken1000]

Seti, thanks for explaining. Looks like some folks
"hijacked" the last link you gave, for it now re-routes
to bad **** sites. Viewers beware.

Ken

 

[jolivanes]

Hi Ken
Following were supplied to me by people a lot more knowledgeable than I am. I don't think there is any problems with supplying it to people wanting the code. If so, my apologies.

This macro exports a chart as a picture

Sub ExportChartsJPG()
Application.ScreenUpdating = False
Sheets("MondayChart").Select
  ActiveChart.Export Filename:="D:\My Documents\My Pics\MondayChart.jpg", _
  FilterName:="jpg"
Sheets("MondayYearly").Select
  Application.ScreenUpdating = False
End Sub

Following macro exports a range as a picture

Sub ExportNumChart()
Const FName As String = "D:\My Documents\My Pics\Numbers.jpg"
Dim pic_rng As Range
Dim ShTemp As Worksheet
Dim ChTemp As Chart
Dim PicTemp As Picture
Application.ScreenUpdating = False
Set pic_rng = Worksheets("Numbers").Range("B1:F25")
Set ShTemp = Worksheets.Add
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=ShTemp.Name
Set ChTemp = ActiveChart
pic_rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
ChTemp.Paste
Set PicTemp = Selection
With ChTemp.Parent
.Width = PicTemp.Width + 8
.Height = PicTemp.Height + 8
End With
ChTemp.Export Filename:="D:\My Documents\My Pics\Numbers.jpg", FilterName:="jpg"
'UserForm1.Image1.Picture = LoadPicture(FName)
'Kill FName
Application.DisplayAlerts = False
ShTemp.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Good Luck
John

 

[Ken1000]

Thank you, also, John. Generosity like yours and the others
here makes this a superb forum.

Ken