export EXCEL-charts programmatically in 'Picture'- format

Posted on 2009-07-02
Last Modified: 2012-05-07
I know the options to export a chart via VBA in GIF or JPG - but the quality of this often is not as good as when choosing [shift]+[edit] - [copy picture] - appearance/size =as shown on screen and pasting it.
I'm wondering if there is a way to export the chart into the same format (i guess it is wmf or emf) that EXCEL uses internally for the above described copy-paste mechanism

I was already looking wild around for hours but I didn't find a proper solution so far - I presume I have to go over the clipboard object with API-calls etc. - that's beyond my semi-professional/amateur status

Thanks for a good working proposal
Question by:BSavioli
  • 4
  • 4
LVL 15

Expert Comment

ID: 24774060
I'm pretty sure that Excel only supports exports to jpg, png and maybe tif, all raster graphics.  WMF/EMF are vector and are far superior in quality when resizing.
There's a program that is supposed to be able to export to wmf, though i don't know if it's usable through VBA or not.
It appears to be free for non-commercial use.

Author Comment

ID: 24774109
Thanks for the hint - but what I'm actually looking for is really a way for programatically (VBA) exporting into a file-format that is like the one used within copy-paste - I don't want to use a third party tool but to have a code to save from the clipboard into the graphic-file. Copy to the clipboard I can do via standard VBA - so the second step: identifying in and storing from clipboard is the critical point

Best regards

LVL 15

Expert Comment

ID: 24785349
Lets talk more.  
First what version of excel?
Second, I wrote some code to copy the picture, but what do you want to do next, save it to a file or paste it into excel or another office application?

Author Comment

ID: 24785461
Hi Weinberk

Currently I'm still with EXCEL 2000 but I'm preparing to move to 2007 - nonetheless I'd prefer to have a solution that is not related to the newest Office version alone but more general.
In meantime I found codes that refer to manipulate the ClipBoard via API, but they all demonstrate only GetText or SetText.

Copy the chart programmatically to ClipBoard I know how to do with on-board tools of VBA as it is directly supported (as is the direct paste too)

What I wish to have is
1. something like 'GetPicture' from the clipboard and
2. Save this to file as *.emf or *.wmf with a file-name and path specified by me

Hope this is suffciciently clear

Best regards


Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

LVL 15

Expert Comment

ID: 24786800
This VBA code will get you the image to the clipboard:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:= xlPicture
This is in Excel 2003, but it should work equally well in 2000.
I still think that you're going to be hard pressed to find a built in solution that will save the clipboard to a wmf or emf file.
This post (old) has info on saving the clipboard to a bitmap.
Have you considered the possibility of printing to a postscript file?
Or how about using a picture object and pasting from the clipboard.  Something like this
first copy to the clipboard as I showed in the previous post

Dim TheImageData As DataObject
Set TheImageData = New DataObject
That'll get the metafile into the data object. I don't know how to get the DataObject into the picture control.  If you can figure that out, maybe the SavePicture function can then be used?  That might only be gif,jpg, etc again though.  Worth a shot.

Author Comment

ID: 26348597
I have no objection as I didn't receive a solution matching my question - I made a workaround for myself that is ok for the direct context of use but not what I had in mind

Sorry for not closing myself the topic earlier  

best regards

LVL 15

Expert Comment

ID: 26351512
So post your solution for others to find.
I no way to know you didn't get a solution or that my suggestions didn't help since you didn't reply.

Accepted Solution

BSavioli earned 0 total points
ID: 26357516
ok - here my general workaround description and attached the code
The code itself has some parts that are very specific for my context but starting with line 170 is the 'core' of procedure

What I am doing is
1. run in a loop through the worksheet for charts and if there is a chart to be exported (could be filtered by information in the worksheet - e.g. create before a list of all charts and using check-boxes to decide which one shall be exported)
2. create a new word-file (with the option to use a preselected template the name/path of which I can have stored in the worksheet),
3. create a caption in the new word-file using the chart-title and
4. use the copy-paste as picture option to insert the chart and after all charts (resp. the ones preselected) have been handled
5. save the new word-file (path is either the workbook path or a path I have stored in the workbook before - like I did with the template) with an automatic name generated using some specific for the context information and the date of creation

That's it.
As I said, it is a workaround as I don't get what I actually wanted to have: The charts saved as files in the picture-format that I can use later for integrating flexibly into reports (e.g. by defining OLE-connections to regular updated picture-files)
Sub CopyPasteChart2Doc(ByVal oWs As Excel.Worksheet)


10    On Error GoTo ERR_CopyPasteChart2Doc


    Const CINT_EmptyPar As Integer = 3

    Const CSTR_TOPIC As String = "n-Plus_"

    Dim strPrg As String		'a label that I need for the charts

    Dim strPrgSel As String		'dito

    Dim oChartObj As Excel.ChartObject	

    Dim oChart As Excel.Chart

    Dim oMsWord As Object

    Dim bolWordActiv As Boolean		'needed to differentiate between Get or Create a WORD instance

    Dim oDoc As Word.Document 		'the object for the document to be created

    Dim intCount As Integer

    Dim strTitle As String

    Dim strChartName As String		'the chart that is exported

    Dim strExpPath As String		'I give the option in the worksheet to enter a path

    Dim strExpTempl As String		'a template can be assigned for the new word-file

    Dim strDocSaveAs As String		'the name for the new document

20    strExpPath = oWs.Range("nPLUS_PAR_ExpPath").Value

30    If (strExpPath = vbNullString Or Len(strExpPath) = 0) Then

        strExpPath = ThisWorkbook.Path

    ElseIf GetAttr(strExpPath) And vbDirectory = 0 Then

      strExpPath = ThisWorkbook.Path

    End If

40    If strExpPath = "" Then

50      MsgBox "Workbook has not yet been saved.", vbOKOnly + vbExclamation, "Path can not be read"

        '##insert option to save the workbook first and then continue - fbolWbSaved"

60      GoTo EXIT_CopyPasteChart2Doc

70    Else    'complement the path in case that necesssary

80      If Right$(strExpPath, 1) <> "\" Then strExpPath = strExpPath + "\"

90    End If

    'wich programme Element has been selected?

100   strPrg = Range("KL_Prg").Value

110   strPrgSel = oWs.Range("nPLUS_PAR_PRG_SelRes").Value

120   If Left(strPrgSel, Len(strPrg)) = strPrg Then

130     strPrg = strPrgSel    'avoid doubling the name of prg

140   Else

150     strPrg = strPrg + "-" + strPrgSel

160   End If

170   If oWs.ChartObjects.Count > 0 Then

180     Set oMsWord = fobjGetOfficeApp("word", bolWordActiv)    'GetObject("Word.Application") or CreateObject("Word.Application")

        '## define a template that shall be used - this skips the problem with save issues

190     strExpTempl = oWs.Range("nPLUS_PAR_ExpTempl").Value

200     If strExpTempl = vbNullString Or Len(strExpTempl) = 0 Then

210         Set oDoc = oMsWord.Documents.Add(Visible:=True)

        ElseIf GetAttr(strExpTempl) And vbNormal = 0 Then

            MsgBox "Template file is not valid - will be used instead!", vbExclamation + vbOKOnly

            Set oDoc = oMsWord.Documents.Add(Visible:=True)

220     Else

230         Set oDoc = oMsWord.Documents.Add(Template:=strExpTempl, Visible:=True)  'Template:=strPath & "",, NewTemplate, DocumentType, Visible

240     End If

250     oMsWord.Visible = True

260     For Each oChartObj In oWs.ChartObjects

270         Application.CutCopyMode = False    'Clear Clipboard

280         Set oChart = oChartObj.Chart

290         If Not oChart.HasTitle Then

300             strTitle = CSTR_TOPIC & oChart.Name

310         Else

320             strTitle = oChart.ChartTitle.Caption

330         End If

            '### modify in a way to exclude non-admissable characters (e.g. chr(10) etc.) when building the name from the title

340         If InStr(1, strTitle, CSTR_PREF_nPlusGaps, vbTextCompare) > 0 Then

350             strChartName = CSTR_PREF_nPlusGaps + "_" + strPrg

360         ElseIf InStr(1, strTitle, CSTR_PREF_nPlusStatus, vbTextCompare) > 0 Then

370             strChartName = CSTR_PREF_nPlusStatus + "_" + strPrg

380         Else

390             strChartName = strTitle    '+ "_" + strPrg

400         End If


            'complement the chart-name by date of export

410         strChartName = strChartName + "_" + Format(Date, "yyyy-mm-d")

420         oChart.CopyPicture Appearance:=xlScreen, Format:=xlPicture, Size:=xlScreen

430         With oMsWord.Selection

440             .TypeText (strChartName)

450             .typeparagraph

460             .Paste

470             .ParagraphFormat.LineSpacingRule = wdLineSpaceSingle

480             .ParagraphFormat.LineSpacing = 18

490             For intCount = 1 To CINT_EmptyPar

500                 .typeparagraph

510             Next intCount

520         End With

530     Next oChartObj

540     strDocSaveAs = CSTR_TOPIC & strPrg & "_" & Format(Date, "yyyy-mm-d") & ".doc"

550     oDoc.SaveAs strExpPath & strDocSaveAs, wdFormatDocument, False, , True

560     oDoc.Close wdSaveChanges, wdWordDocument

570     If Not bolWordActiv Then

580         oMsWord.Quit

590     Else

600         ThisWorkbook.Activate

610     End If

620     MsgBox "Charts have been stored into file: " & strDocSaveAs & vbCrLf & "to folder: " & strExpPath

630   Else

640     MsgBox "No charts have been found to be exported", vbOKOnly + vbCritical, "Chart Export Result"

650   End If



660   On Error Resume Next

670   If Not bolWordActiv Then oMsWord.Quit

680   Application.CutCopyMode = False    'Clear Office Clipboard

690   Set oDoc = Nothing

700   Set oMsWord = Nothing

710   Set oChart = Nothing

720   Exit Sub


    oWs.Activate 'to ensure the focus on the worksheet and dialogbox is on top of the windows

    If (Err.Number = 53 Or Err.Number = 76) Then

        MsgBox strExpPath + vbCrLf + " is not a valid folder!" + vbCrLf + _

        "Please select a valid export-folder first.", vbExclamation + vbOKOnly, "Invalid path"

        Resume EXIT_CopyPasteChart2Doc


730   MsgBox CStr(Err.Number) & " - " & Err.Description & vbCrLf & _

           "in VBAProject.Module1.CopyPasteChart2Doc " & _

           "at line " & Erl

740   Resume EXIT_CopyPasteChart2Doc

    End If


End Sub

Open in new window


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now