Public Sub Charts_To_Pdf()
Dim currentWorksheet As Worksheet
' Loop through all of the worksheets in the active workbook.
For Each currentWorksheet In Worksheets
Dim arrChartToPrint() As Variant
Dim myChart As ChartObject
Dim col As New collection
For Each myChart In currentWorksheet.ChartObjects
' MsgBox myChart.Name
col.Add (myChart.Name)
Next myChart
arrChartToPrint = toArray(col)
Dim strPdfName As String: strPdfName = currentWorksheet.Name + "_" + "Charts.pdf"
Call Charts_To_Pdf_(currentWorksheet, arrChartToPrint, strPdfName)
'Important, as dim as new doesn't clear it.
Set col = Nothing
Next
End Sub
Function toArray(col As collection)
Dim arr() As Variant
ReDim arr(1 To col.Count) As Variant
Dim I As Integer
For I = 1 To col.Count
arr(I) = col(I)
Next
toArray = arr
End Function
Private Sub Charts_To_Pdf_(wSheet_Src As Worksheet, arrChartToPrint() As Variant, strPdfName As String)
Dim strFilePath As String: strFilePath = ThisWorkbook.Path & "\"
If Dir(strFilePath & strPdfName) <> "" Then
' If vbYes <> MsgBox("File already exists:" & vbCrLf & strFilePath & strPdfName & vbCrLf & vbCrLf & "Do you wish to overwrite it ?", vbYesNo) Then
' Exit Sub
' End If
End If
Application.ScreenUpdating = False
On Error Resume Next
Dim wbBookTmp As Workbook: Set wbBookTmp = Workbooks.Add
Dim wsSheetTmp As Worksheet: Set wsSheetTmp = wbBookTmp.ActiveSheet
Dim l As Long
For l = 1 To UBound(arrChartToPrint)
'MsgBox ("current: " + wSheet_Src.Name + ": " + arrChartToPrint(l))
wSheet_Src.ChartObjects(arrChartToPrint(l)).Copy
wsSheetTmp.Paste
wsSheetTmp.ChartObjects(arrChartToPrint(l)).Chart.Location where:=xlLocationAsNewSheet, Name:=arrChartToPrint(l)
Next l
wbBookTmp.Sheets(arrChartToPrint).Select
wbBookTmp.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFilePath & strPdfName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
wbBookTmp.Close (False)
Application.ScreenUpdating = True
End Sub
This macro will create a pdf file for each of the graphs.
Once if you saved the macro, you can either run it in excel, or use vbs to execute it.
As we will need to crop these pdf files, we will do this from batch file.
save above code as vbs file, eg. extract_graph.vbs
Note: you will need to download pdfcrop tool. The one I used was from MikTex. I need to make a copy of mgs.exe file and rename to gs.exe in the bin folder to make pdfcrop work. You are welcome to modify the code to use other pdf cropping tool.
