asked on

Export all charts on a sheet, Excel 2016 for Mac

Trying to create a VBA macro button I can click to export all chart objects on a sheet to PNG files in the same directory as the workbook. Charts have names like "Chart 3," "Chart 5," etc. that apparently I can't change.

Here is my current code:

Sub Button_Click()
Dim cht As ChartObject
For Each cht In ActiveSheet.ChartObjects
cht.Export ActiveWorkbook.Path & Application.PathSeparator & cht.Name & ".png"
Next cht
End Sub

When I run this code, I get "Compile error:

Method or data member not found
(Module1 3:15)"

If I do "cht.Chart.Export" instead of "cht.Export," I get error 70, permission denied.

Also tried this:

For i = 1 To 9
ActiveSheet.ChartObjects(i).Select
ActiveChart.Export ActiveWorkbook.Path & Application.PathSeparator & ActiveChart.Name & ".png", "PNG"
Next i

Got error 70, permission denied.

Rgonzo1971

Hi,

maybe your File has not yet be saved that's why the ActiveWorkbook.Path makes an error

then try on a saved file

Sub ButtonClick()
     Dim cht As ChartObject
         For Each chtObj In ActiveSheet.ChartObjects
             chtObj.Chart.Export ActiveWorkbook.Path & Application.PathSeparator & chtObj.Name & ".png"
         Next chtObj
 End Sub

Open in new window

Regards

netfriendsinc

ASKER

No, the file was saved. I also tried having it put up a MsgBox with the export path, and that worked fine, so the path itself is fine.

Rgonzo1971

maybe the png is already open

ASKER CERTIFIED SOLUTION

netfriendsinc

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

netfriendsinc

ASKER

Did not receive any helpful comments or suggestions.