Advertisement
Guest User

Untitled

a guest
Jul 22nd, 2014
206
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.83 KB | None | 0 0
  1. Private Sub CommandButton1_Click()
  2. Dim ch As Chart
  3. Dim objChartObject As ChartObject
  4. Dim ws As Worksheet
  5. Dim strExportPath As String
  6. Dim strFileName As String
  7. Dim lngWSChartsCount As Long
  8. Dim lngChartSheetsCount As Long
  9.  
  10. strExportPath = ThisWorkbook.Path 'Change if you want different path
  11.  
  12. 'Export charts from worksheets
  13. lngWSChartsCount = 0
  14. For Each ws In Worksheets
  15.  
  16. For Each objChartObject In ws.ChartObjects
  17. Set ch = objChartObject.Chart
  18. On Error Resume Next
  19. strFileName = ch.ChartTitle.Text & ".jpg"
  20. ch.Export Filename:=strExportPath & "" & strFileName, FilterName:="JPG"
  21. If Err <> 0 Then
  22. Err.Clear
  23. strFileName = objChartObject.Name & ".jpg"
  24. ch.Export Filename:=strExportPath & "" & strFileName, FilterName:="JPG"
  25. If Err = 0 Then
  26. lngWSChartsCount = lngWSChartsCount + 1
  27. End If
  28. Else
  29. lngWSChartsCount = lngWSChartsCount + 1
  30. End If
  31. On Error GoTo 0
  32. strFileName = vbNullString
  33. Next objChartObject
  34. Next ws
  35.  
  36. 'Export also chart sheets
  37. lngChartSheetsCount = 0
  38. For Each ch In ThisWorkbook.Charts
  39. On Error Resume Next
  40. strFileName = ch.ChartTitle.Text & ".jpg"
  41. ch.Export Filename:=strExportPath & "" & strFileName, FilterName:="JPG"
  42. If Err <> 0 Then
  43. Err.Clear
  44. strFileName = ch.Name & ".jpg"
  45. ch.Export Filename:=strExportPath & "" & strFileName, FilterName:="JPG"
  46. If Err = 0 Then
  47. lngChartSheetsCount = lngChartSheetsCount + 1
  48. End If
  49. Else
  50. lngChartSheetsCount = lngChartSheetsCount + 1
  51. End If
  52. On Error GoTo 0
  53. strFileName = vbNullString
  54. Next ch
  55.  
  56. Call MsgBox(lngWSChartsCount & " charts from worksheets and " & lngChartSheetsCount & " chart sheets exported at " & strExportPath, vbInformation, "Charts Export Result")
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement