Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making .jpg's
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making .jpg's
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making .jpg's
Thanks for your response Peter.........I will check out all your suggestions.
Vaya con Dios, Chuck, CABGx3 "Peter T" wrote: 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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge jpg's from Excel data file into word document | Excel Discussion (Misc queries) | |||
Making #N/A zero or - | Excel Discussion (Misc queries) | |||
making changes | Excel Worksheet Functions | |||
Making An Add-in | Excel Programming | |||
Making add-in available | Excel Programming |