Export from EXcel to jpg with 2007

[Pops Jackson]

Tom Ogilvy posted the following code some time back and it works wonderfully
in Excel 2003 but, of course, encounters problems in 2007. Can anyone give
suggestions for bringing it up to date for 2007?

Sub ExportPicAsJpg()
Dim chTemp As Chart
Dim picCopy As Picture
Dim dWidth As Double
Dim dHeight As Double
Dim shNew As Worksheet

Set picCopy = Selection
Set chTemp = Charts.Add
Set shNew = Sheets.Add

dWidth = picCopy.Width
dHeight = picCopy.Height

Application.ScreenUpdating = False
With chTemp

.SetSourceData Source:=Sheets("Sheet1").Range("FA16383")
.Location Where:=xlLocationAsObject, Name:=shNew.Name

With shNew.ChartObjects(1)

.Width = dWidth + 2
.Height = dHeight + 2
.Top = 0
.Left = 0
Range("A1").Select
picCopy.Copy
.Activate
ActiveChart.Paste
.Interior.ColorIndex = 1
ActiveChart.Export "c:\TempPic.JPG", "jpg"

End With

End With

Application.DisplayAlerts = False
shNew.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Thanks for any help given,

 

[Chip Pearson]

It would be very helpful if you would explain what you are trying to
accomplish and why it does work. What errors are you getting?

The Export method of a Chart works in 2007 as it did in earlier
version:

Dim ChartObj As ChartObject
Set ChartObj = Worksheets("Sheet1").ChartObjects(1)
ChartObj.Chart.Export "D:\Chart1.jpg"

In your code, you have
Set picCopy = Selection

What is selected when you Set it to picCopy?

Don't post messages with something like "it doesn't work". If you
expect the readers to take the time to read, decypher, and find a
solution to your question, you should put in the effort to fully
explain in detail what you need, what should happen that doesn't, or
what doesn't happen that should. Gold stars for details.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Pops Jackson]

Sorry about that, Chip. I am guilty of the same thing I complain of others
doing.
I have made some progress but run into trouble at
".SetSourceData Source:=Sheets("viewProducts").Shapes("Picture 17")".

At that point I get Runtime error 13: Type mismatch. I am attempting to
export a picture in the viewProducts sheet to jpg.

Thanks,

Jim

 

[Martin Brown]

Sorry about that, Chip. I am guilty of the same thing I complain of others
doing.
I have made some progress but run into trouble at
".SetSourceData Source:=Sheets("viewProducts").Shapes("Picture 17")".

At that point I get Runtime error 13: Type mismatch. I am attempting to
export a picture in the viewProducts sheet to jpg.

Without having looked at your code I would suspect that good old 2007
race condition has allowed it to create graphics objects in some random
order which may well depend on wind direction! The result is that you
cannot safely rely on Picture 17 being the one you expect it to be.

XL2003 was always deterministic AFAICT. XL2007 is not at least on
multicore machines and you sometimes get objects not quite completely
instantiated before code tries to reference them or with different
numbers to the XL2003 version. It is a PITA if you have to maintain code
that will work on both platforms.

If it works when you step in the debugger then it is likely a race
condition that can be fixed with judicious application of DoEvents or
wait loops. If not then look to see which shapes or charts are in an
unexpected order.

Regards,
Martin Brown

 

[Peter T]

Are you really trying to set your chart's source data to a picture?
This is even more confusing than your first post!

It would be better to start again and clearly explain what you want to do.
After typing, read it back and ask yourself if it will make sense to someone
who cannot see what you have in front of you (worksheet and code if
applicable) and your objective.

Regards,
Peter T