Solved

export EXCEL-charts programmatically in 'Picture'- format

Posted on 2009-07-02
10
1,484 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
  • 4
  • 4
10 Comments
 
LVL 15

Expert Comment

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

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 15

Expert Comment

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

758 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

20 Experts available now in Live!

Get 1:1 Help Now