Copy chart to PPT and save it as image

jreddy

New Member
Hi,

I have to create reports and Charts in Excel and then send them to respective managers. Charts must use our corporate color scheme, which is not available in Excel. Thus I've written a code stage in Existing Excel VBO to copy charts to PPT, where the right color scheme is automatically applied, and then save them as images. Everything runs perfectly until it hits an export line - then I get an error HRESULT: 0x80070057 (E_INVALIDARG). Does anybody have any idea why this happens? The PowerPoint code also works perfectly fine in PowerPoint Macro Editor.

Code:
Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname)

ws.ChartObjects(1).Chart.ChartArea.Copy

Dim pptApp as Object
pptApp = CreateObject("PowerPoint.Application")

Dim activePPT as Object = pptApp.Presentations.Open(presPath, 0, 0, -1)

activePPT.Slides(1).Shapes.Paste
activePPT.Slides(1).Shapes(1).ScaleWidth(2, 0)
activePPT.Slides(1).Shapes(1).ScaleHeight(2, 0)

activePPT.Slides(1).Shapes(1).Chart.Export(imgPath, "PNG")

pptApp.Quit
 

VJR

Well-Known Member
Hi,

I have to create reports and Charts in Excel and then send them to respective managers. Charts must use our corporate color scheme, which is not available in Excel. Thus I've written a code stage in Existing Excel VBO to copy charts to PPT, where the right color scheme is automatically applied, and then save them as images. Everything runs perfectly until it hits an export line - then I get an error HRESULT: 0x80070057 (E_INVALIDARG). Does anybody have any idea why this happens? The PowerPoint code also works perfectly fine in PowerPoint Macro Editor.

Code:
Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname)

ws.ChartObjects(1).Chart.ChartArea.Copy

Dim pptApp as Object
pptApp = CreateObject("PowerPoint.Application")

Dim activePPT as Object = pptApp.Presentations.Open(presPath, 0, 0, -1)

activePPT.Slides(1).Shapes.Paste
activePPT.Slides(1).Shapes(1).ScaleWidth(2, 0)
activePPT.Slides(1).Shapes(1).ScaleHeight(2, 0)

activePPT.Slides(1).Shapes(1).Chart.Export(imgPath, "PNG")

pptApp.Quit
Hi jreddy, what is the value of imgPath?
 

jreddy

New Member

Attachments

  • Code stage.png
    19.5 KB · Views: 33
  • Image Path properties.png
    11.5 KB · Views: 30
  • Start stage.png
    20 KB · Views: 28
  • VBO.png
    50.8 KB · Views: 28

jreddy

New Member
okay I actually meant what is the value going in the imgPath?

It's the address where to save the image, like "C:\Users\username\Documents\chart1.png". Attached the screenshot with inputs for the respective action stage in the process.
 

Attachments

  • Export Chart.png
    23.5 KB · Views: 25

VJR

Well-Known Member
It's the address where to save the image, like "C:\Users\username\Documents\chart1.png". Attached the screenshot with inputs for the respective action stage in the process.
activePPT.Slides(1).Shapes(1).Chart.Export(imgPath, "PNG")

Have you re-checked the powerpoint object model.
For eg;
Chart doesn't appear to be a method of the Shapes object.
https://docs.microsoft.com/en-us/office/vba/api/powerpoint.shapes

The PowerPoint code also works perfectly fine in PowerPoint Macro Editor.
Share the code that works fine in the powerpoint editor.
 

jreddy

New Member
Hi VJR,

Chart is a property of a Shape object, which is being returnedfrom a Shapes collection. It turns a generic Shape object into a Chart object, thus I can use an Export method of a Chart object.

Here is PowerPoint macro code:

Code:
ActivePresentation.Slides(1).Shapes.Paste
ActivePresentation.Slides(1).Shapes(1).ScaleWidth 2, msoFalse
ActivePresentation.Slides(1).Shapes(1).ScaleHeight 2, msoFalse

ActivePresentation.Slides(1).Shapes(1).Chart.Export "C:\Users\ievgenii.skurtov\Documents\BP\Chart1.png", "PNG"
 

VJR

Well-Known Member
Hi VJR,

Chart is a property of a Shape object, which is being returnedfrom a Shapes collection. It turns a generic Shape object into a Chart object, thus I can use an Export method of a Chart object.

Here is PowerPoint macro code:

Code:
ActivePresentation.Slides(1).Shapes.Paste
ActivePresentation.Slides(1).Shapes(1).ScaleWidth 2, msoFalse
ActivePresentation.Slides(1).Shapes(1).ScaleHeight 2, msoFalse

ActivePresentation.Slides(1).Shapes(1).Chart.Export "C:\Users\ievgenii.skurtov\Documents\BP\Chart1.png", "PNG"
Hi jreddy,

When I try to run the above given code it throws the below error
View attachment 1535653466403.png

Also you can see Chart doesn't appear to be the method or property for Shapes.

View attachment 1535653617252.png
 

jreddy

New Member
Hi VJR,

Check this link:
https://docs.microsoft.com/en-us/office/vba/api/powerpoint.shape

You can see that there is a Chart-proprety under Shape properties.

Also it gives me a suggestion in VBA Editor when I start typing it, and no compile errors. Chart pops up in the folder and is totally fine. Another way I tried and which worked in editor but not in Blue Prism is this (even though there is no Export Method for Shape in MSDN Docs, just found this code here https://stackoverflow.com/questions/25353221/export-excel-charts-as-images-using-powerpoint-vba):

Code:
With ActivePresentation.Slides(1).Shapes(1)
    .Export "C:\Users\ievgenii.skurtov\Documents\BP\Chart1.png", ppShapeFormatPNG
End With

I'm using Office 2016, in case it makes any difference.

Can you try implementing code in Blue Prism and tell me what works for you?
 

Attachments

  • Chart in the Folder.png
    19.3 KB · Views: 14
  • Code in the Editor.png
    25.1 KB · Views: 14
  • MSDN Docs.png
    100.1 KB · Views: 12
Last edited:

VJR

Well-Known Member
Hi VJR,

Check this link:
https://docs.microsoft.com/en-us/office/vba/api/powerpoint.shape

You can clearly see that there is a Chart-proprety under Shape properties.

Also it gives me a suggestion in VBA Editor when I start typing it, and no compile errors. Chart pops up in the folder and is totally fine. Another way I tried and which worked in editor but not in Blue Prism is this (even though there is no Export Method for Shape in MSDN Docs, just found this code here https://stackoverflow.com/questions/25353221/export-excel-charts-as-images-using-powerpoint-vba):

Code:
With ActivePresentation.Slides(1).Shapes(1)
    .Export "C:\Users\ievgenii.skurtov\Documents\BP\Chart1.png", ppShapeFormatPNG
End With

I'm using Office 2016, in case it makes any difference.

Can you try implementing code in Blue Prism and tell me what works for you?
The Office version might probably be making the difference (more on that below).
If you notice in the response I have provided you Shapes has an s in bold indicating that -
Shapes - does not have the Chart property https://docs.microsoft.com/en-us/office/vba/api/powerpoint.shapes
Shape - does has have the Chart property https://docs.microsoft.com/en-us/office/vba/api/powerpoint.shape

Before implementing anything like this in the Blue Prism Code stage I would prefer to go by the Object Model as above. If its not there I won't prefer it using. It could also be possible that the latest Office might be having the method as you pointed out in the VBA editor but Blue Prism might still be on the older object model.

If you are still looking for a solution on this you could even export it to an image even before putting it into Powerpoint since Excel's chart object does have an Export option.

Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname)

ws.ChartObjects(1).Chart.Export("Full File path", "PNG")
ws.ChartObjects(1).Chart.ChartArea.Copy

Dim pptApp as Object
 

jreddy

New Member
The Office version might probably be making the difference (more on that below).
If you notice in the response I have provided you Shapes has an s in bold indicating that -
Shapes - does not have the Chart property https://docs.microsoft.com/en-us/office/vba/api/powerpoint.shapes
Shape - does has have the Chart property https://docs.microsoft.com/en-us/office/vba/api/powerpoint.shape

Before implementing anything like this in the Blue Prism Code stage I would prefer to go by the Object Model as above. If its not there I won't prefer it using. It could also be possible that the latest Office might be having the method as you pointed out in the VBA editor but Blue Prism might still be on the older object model.

If you are still looking for a solution on this you could even export it to an image even before putting it into Powerpoint since Excel's chart object does have an Export option.

Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname)

ws.ChartObjects(1).Chart.Export("Full File path", "PNG")
ws.ChartObjects(1).Chart.ChartArea.Copy

Dim pptApp as Object

As I mentioned above, Shapes returns a collection of Shape-objects on the slide, thus referencing a single Shape-object from that collection (aka .Shapes(1)) should provide you with a Chart property.

I implemented exporting charts from Excel a long time ago and it all works fine there.

I just looked at the problem from a different angle, and found how to export our corporate PowerPoint theme and apply it to Excel. Will try implementing it in Blue Prism and will let you know how it goes.
 
Last edited:

jreddy

New Member
So I extracted our corporate theme from PowerPoint by saving the presentation as .thmx file. Then created new Page in Excel VBO with a following code:

Code:
Dim wb as Object = GetWorkbook(handle,workbookname)

wb.ApplyTheme(themePath)

Where themePath is a Strin with a full path to the theme. It all works perfectly well, now I just need to extract the chart from Excel and it's done :)

If in the future anybody comes across this thread and happens to know why extracting the image from PowerPoint didn't work, I would appreciate the input!
 
Top