Extract Graph from Excel for latex use

Firstly, add micro into the original spread sheet and save it as xlsm file

The following code is for micro

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.

now, create a vbs file to run the macro

Option Explicit

RunExcelMacro

Sub RunExcelMacro() 

  Dim xlApp 

  Dim xlBook 

  Set xlApp = CreateObject("Excel.Application") 

  Set xlBook = xlApp.Workbooks.Open("c:\\Users\\ydeng\\soody\\gitLocal2\\study\\HTTP_Server\\HTTP_Server\\analyse.xlsm", 0, True) 

  xlApp.Run "Charts_To_Pdf"

  xlApp.Quit 

  Set xlBook = Nothing 

  Set xlApp = Nothing 

End Sub 

save above code as vbs file, eg. extract_graph.vbs

Then create a batch file

cscript extract_graph.vbs

md cropped

FOR /F "eol=; tokens=1 delims=," %%i in ('dir /b *.pdf') do call :begin "%%i"

goto eof

:begin

pdfcrop "%~1" "cropped\%~1"

Save above code to vbs file, eg. generate_figure.bat

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.

PDFCROP 1.38, 2012/11/02 - Copyright (c) 2002-2012 by Heiko Oberdiek.

Syntax:   pdfcrop [options] <input[.pdf]> [output file]

--

Feng