Making .jpg's

[CLR]

Hi All......
I have the following code, which I got from these Groups some time ago. It
appears to "run" in all versions of Excel from '97 through 2007, but the
pictures only come through properly in XL2002 and XL2003......the other
versions are inconsistant, and usually break up the size of the picture.
Naturally, my user wants it to work in all versions........
Anybody see anything herein that would work better in 2002/3 than the
others.......or anything else that might help?.......or another way of doing
it? I'm actually making a .jpg of a range of cells that contains both data
and a Chart of that data.

Here's the code.......it's in two pieces.........

Sub MakePic1()
Dim JpgFileName
Dim JpgSheet
Dim JpgRange
JpgRange = Range("JPG!L15").Value
JpgSheet = Range("JPG!k15").Value
Sheets(JpgSheet).Select
CreateImageFile _
TheExportRange:=Range(JpgRange), _
TheFileName:=ThisWorkbook.Path & "\" & Range("jpg!j15").Value, _
TheFileFormat:="jpg"
Sheets("JPG").Select
End Sub


Sub CreateImageFile(TheExportRange As Range, _
TheFileName As String, _
TheFileFormat As String)
TheExportRange.CopyPicture Appearance:=xlScreen, _
Format:=xlPicture
Dim chtobj As ChartObject
Set chtobj = TheExportRange.Parent.ChartObjects.Add(1, 1, 1, 1)
With chtobj
.Height = 700
.Width = 800
.Chart.ChartArea.Border.LineStyle = 0
.Chart.Paste
.Chart.Export Filename:=TheFileName & "." & TheFileFormat, _
FilterName:=TheFileFormat
.Delete
End With
Set chtobj = Nothing
End Sub

Any help would be much appreciated
Vaya con Dios,
Chuck, CABGx3

 

[Peter T]

It's probably better to add the temporary chart outside the copy range, say
on a temporary or hidden sheet. Also why not size the chart to the copy size

.Height = TheExportRange.height ' 700
.Width = TheExportRange.width ' 800

Talking of size, might be an idea to ensure the size is not too big, if
necessary snip into smaller sections.

Be aware FilterName:="jpg" does not work in all systems, gif does. Typically
though GIF is a better format for a spreadsheet unless it contains pictures
or gradient colour formats. Strangely even specifying gif can fail, so if
going for gif omit the FilterName altogether and it will default to gif.

One more thing, CopyPicture xlPicture works fine in all versions but
xlBitmap doesn't in 2007. If you do want xlBitMap, in 2007 simply use "Copy"
and a bitmap will be copied to the clipboard.

Regards,
Peter T

 

[CLR]

Thanks for your response Peter.........I will check out all your suggestions.

Vaya con Dios,
Chuck, CABGx3