Using VBA to Export an XL Chart (VBA Excel XP/2002)

Topic

New Reply

Is there any way to export an XL chart in WMF or EMF (Windows Metafile) format.
ActiveChart.Export Filename:=”C:TempChart.gif”, FilterName:=”gif”
works fine for some types of raster files but I would like to save a true metafile… a chart based on vector graphics that can be resized with excellent results.
XL must support this since one can select a chart, copy it to clipboard, then use Paste Special in another application such as Word and choose Picture or Enhanced Metafile as the Paste option. Note that one cannot use the .wmf or .emf file extensions in the ActiveChart.Export line above since an error will be generated.
Thanks to anyone that can help me with this.
Lorne

Reply | Quote

Replies

One idea to explore is using Word as a “middle-man” – using VBA to paste the chart into a Word document as a metafile, and then exporting the resulting image from Word. I know it’s a long way round, but hey, if it works…

Reply | Quote

Thanks Dave but I don’t think this will work… final .wmf or .emf files will be used in a larger automation exercise. Getting the file into Word still doesn’t allow me to save it as a metafile.
I have continued to search for an answer and may have come up with one.
The Chart object in Excel does have a CopyPicture method that allows one to copy the chart to the Windows clipboard
ActiveChar.CopyPicture Appearance:=xlScreen, Format:=xlPicture
There are Windows API functions that can be used to save the clipboard picture as a metafile.

Private Declare Function OpenClipboard Lib “User32” (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib “User32” () As Long
Private Declare Function CopyEnhMetaFileA Lib “Gdi32” (ByVal hENHSrc As Long, ByVal lpszFile As String) As Long

‘ First: use XL VBA to copy the picture to the clipboard
‘ Call a function like the one below to save the picture

Private Function Save_EMF_File(FileName As String) As Boolean

Dim ReturnValue As Long

OpenClipboard 0
ReturnValue = CopyEnhMetaFileA(GetClipboardData(14, FileName) ‘ 14 is the ID for extended metafile format
CloseClipboard

If ReturnValue = 0 Then
Save_EMF_File = True
Else
Save_EMF_File = False
End If

End Function

Reply | Quote

Excellent, well done

Reply | Quote