export EXCEL-charts programmatically in 'Picture'- format

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Berkson WeinTech FreelancerCommented:
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 http://www.asap-utilities.com/about-asap-utilities.php 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.
BSavioliOwnerAuthor Commented:
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

Berkson WeinTech FreelancerCommented:
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?
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

BSavioliOwnerAuthor Commented:
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


Berkson WeinTech FreelancerCommented:
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) http://groups.google.ca/group/microsoft.public.access.modulesdaovba/browse_thread/thread/674742b64c6e7d01/3e95e8f696986245?lnk=st&q=getclipboarddata(cf_bitmap)+visual+basic&rnum=2#3e95e8f696986245 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.
BSavioliOwnerAuthor Commented:
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

Berkson WeinTech FreelancerCommented:
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.
BSavioliOwnerAuthor Commented:
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 normal.dot 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 - Normal.dot 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 & "RO_Bilateral.dot",, 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.