Export excel2000 charts into EMF/WMF

[yongtao.yang]

Dear group,

I am trying to find out how to export an chart/diagramn from Excel2000
to an graphics file in EMF or WMF format. After a not-so-short google
search, I found two of the most popular solutions:

1. Copy the diagram and paste special as emf graphics into powerpoint,
adjust the page size to fit the diagram then save as WMF file

2. Use Stephen Bullen's Excel tool PastePicture availabe at
http://www.bmsltd.co.uk/Excel/Default.htm to save the diagram as an EMF
file

I don't like the first solution very much since it involves too much
manual work. So I went for the second one, however the PastePicture.zip
does not seem to work with Excel 2000 on my computer. When clicking the
SavePicture button and providing a file name, an error message poped
out with the following information:
"Runtime error number 380, invalid property value".

If I choose Debug from the error message box, the VBA window shows that
error happens at within the subroutine btn_ClickSave at the line
SavePicture oPic, vFile
where oPic has a value "Nothing".

I am not really sure what could be wrong here as I have never touch VBA
myself. Could some body help me with that?

Thanks in advance!

 

[Michel Pierron]

Hi, you can try:

Private Declare Function _
CloseClipboard& Lib "user32" ()
Private Declare Function _
OpenClipboard& Lib "user32" (ByVal hwnd&)
Private Declare Function _
EmptyClipboard& Lib "user32" ()
Private Declare Function _
GetClipboardData& Lib "user32" (ByVal wFormat&)
Private Declare Function CopyEnhMetaFileA& _
Lib "gdi32" (ByVal hemfSrc&, ByVal lpszFile$)
Private Declare Function _
DeleteEnhMetaFile& Lib "gdi32.dll" (ByVal hemf&)

Sub SaveChart()
On Error GoTo 1
Const Graph$ = "Name of your chart"
Dim hCopy&, fName$
ActiveSheet.ChartObjects(Graph).Copy
OpenClipboard 0&
hCopy = GetClipboardData(14)
If hCopy Then
fName = ThisWorkbook.Path & "\" & Graph & ".wmf"
DeleteEnhMetaFile CopyEnhMetaFileA(hCopy, fName)
EmptyClipboard
End If
CloseClipboard
Exit Sub
1: MsgBox "Error " & Err.Number & vbLf & Err.Description, 48
End Sub

Regards,
MP

 

[yongtao.yang]

Hi, you can try:

Private Declare Function _
CloseClipboard& Lib "user32" ()

...

Hello Micael,

Thank you very much for your reply.

Unfortunately, it does not work here, when I save the attached code as
module subroutine inside my excel document and play the macro, it
complains with error:

Error 1004, can not find property CharObjects for class Worksheet.

Which version did you use when running the code? Mine here is Excel
2000. The code does not on Excel XP installed on another machine
either, but the error message is somehow different.

Best regards,