Upon exit excel woorkbook save charts as picture

[BojkoHB]

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]

check this out:

http://www.excelforum.com/excel-prog...rts-title.html

[mancubus]

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-prog...rts-title.html

[BojkoHB]

WOW
Thanx.
Works.

[mancubus]

you are welcome.
pls mark the thread as solved. (see item 3 in my signature)