Solved

export EXCEL-charts programmatically in 'Picture'- format

Posted on 2009-07-02
10
1,558 Views
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
0
Comment
Question by:BSavioli
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
10 Comments
 
LVL 15

Expert Comment

by:Berkson Wein
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 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.
0
 

Author Comment

by:BSavioli
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

Benno
0
 
LVL 15

Expert Comment

by:Berkson Wein
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?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:BSavioli
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

Benno

0
 
LVL 15

Expert Comment

by:Berkson Wein
ID: 24786800
This VBA code will get you the image to the clipboard:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
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
then

Dim TheImageData As DataObject
Set TheImageData = New DataObject
TheImageData.GetFromClipboard
 
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.
0
 

Author Comment

by:BSavioli
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

Benno
0
 
LVL 15

Expert Comment

by:Berkson Wein
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.
0
 

Accepted Solution

by:
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)
'<errHeader>
10    On Error GoTo ERR_CopyPasteChart2Doc
    '</errHeader>
    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

    '<errFooter>
EXIT_CopyPasteChart2Doc:
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

ERR_CopyPasteChart2Doc:
    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
    Else
730   MsgBox CStr(Err.Number) & " - " & Err.Description & vbCrLf & _
           "in VBAProject.Module1.CopyPasteChart2Doc " & _
           "at line " & Erl
740   Resume EXIT_CopyPasteChart2Doc
    End If
    '</errFooter>
End Sub

Open in new window

0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

737 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