PDA

View Full Version : Upon exit excel woorkbook save charts as picture



BojkoHB
05-16-2018, 12:31 AM
Hi guys

Since i'm new here and new on coding with macros and VBA - i'm totaly noob i need help

I'm gathering data on some things at home (bird watching, animals, ... ) basicaly on daily basis for a group that I'm in we then monitor movements.

Since these charts are quite some (10) i want to export them when i exit excel workbook at once not one by one and clicking on chart etc.
These pictures are then monitored on webpages so others in group can just open webpage not pptx or xlsx to see these pictures/charts.

i found this and adapted to my needs

Sub SaveChartAsPNG()
Dim sFileName As String


sFileName = ThisWorkbook.Path & "" & ActiveChart.Name & ".PNG"
ActiveChart.Export Filename:=sFileName, FilterName:="PNG"
End Sub

now my question is - IS IT POSSIBLE TO EXPORT AT ONCE ALL CHARTS IN SINGLE WORKBOOK AS PICTURE WHEN EXITING/CLOSING WORKBOOK?

When pressing X on excel workbook then after few secs (5-10s) pop-up would tell you eg - 10 charts exported as pictures! Have a nice day!

Thank you for your replies guys

Bye

HB

mancubus
05-16-2018, 02:49 AM
check this out:

http://www.excelforum.com/excel-programming-vba-macros/908532-export-charts-from-an-excel-file-as-images-and-name-the-image-files-by-the-charts-title.html

mancubus
05-16-2018, 05:55 AM
copy below code to ThisWorkbook code module
it triggers when the workbook is closed

i understand your workbook does not contain ant chart sheets
make sure all chart objects have unique names. otherwise it overwrites previously saved file.



Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim chObj As ChartObject
Dim ws As Worksheet
Dim fName As String
Dim chObjCnt As Long

ThisWorkbook.Save 'remove this line if you dont want to save the file automatically

For Each ws In Worksheets
For Each chObj In ws.ChartObjects
fName = chObj.Name & ".png"
ch.Export ThisWorkbook.Path & "\" & fName, "PNG"
chObjCnt = chObjCnt + 1
Next chObj
Next ws

MsgBox chObjCnt & " charts from worksheets exported at:" & vbLf & ThisWorkbook.Path, vbInformation, "Charts Export Result"

End Sub


adopted from post #9 of:

http://www.excelforum.com/excel-programming-vba-macros/908532-export-charts-from-an-excel-file-as-images-and-name-the-image-files-by-the-charts-title.html (http://<br />http://www.excelforum.com/excel-programming-vba-macros/908532-export-charts-from-an-excel-file-as-images-and-name-the-image-files-by-the-charts-title.html<br />)

BojkoHB
05-17-2018, 04:46 AM
WOW
Thanx.
Works. :D

mancubus
05-17-2018, 06:00 AM
you are welcome.
pls mark the thread as solved. (see item 3 in my signature)