Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Export an XL Chart (VBA Excel XP/

    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...
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Export an XL Chart (VBA Excel XP/

    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

  4. #4
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Export an XL Chart (VBA Excel XP/

    Excellent, well done <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •