Export Excel chart to SVG creates an empty file

Question

I am trying to export an Excel chart in SVG format using VBA.

    Set objChrt = ActiveChart.Parent
    objChrt.Activate
    Set curChart = objChrt.Chart
    
    curChart.Export fileName:=fileName, FilterName:="SVG"

If I replace "SVG" by "PNG", the export works exactly as intended and produces a valid PNG file. However, "SVG" results in an empty file. (Manually, there is an option to save as SVG inside Excel 365, so the export filter exists).

According to the documentation, Filtername is "The language-independent name of the graphic filter as it appears in the registry.", but I couldn't find anything like that in the registry, and either way, it's hard to imagine the SVG filtername being named anything other than "SVG".

Is there a way to export a Chart in SVG format using VBA?

---

Note: There is another question about Chart.export producing an empty file, and the fix was to use ChartObject.Activate before the export. This question is different because the code works correctly with "PNG" but fails with "SVG" (so it's not an issue related to activation or visibility). Also the recommended fix does not work.

Answer 1

Exporting in vector format:

If your main issue is exporting the charts in some vector format, I recommend just exporting as PDF, as this is very easy:

Set curChart = objChrt.Chart
objChrt.ExportAsFixedFormat xlTypePDF, "YourChart"

The PDF now contains your chart as a vector graphic and PDF is a widely supported format for further processing.

If you absolutely need to convert the chart to .svg you can do so from the command line (and therefore easily automatable) using the open-source software Inkscape or so I thought :/.

Converting to SVG:

Unfortunately, the Inkscape conversion didn't seem to work for me so I implemented it using the open-source pdf rendering toolkit Poppler. (Install instructions at the bottom of this post)

This library provides the command line utility pdftocairo, which will be used in the following solution:

Sub ExportChartToSVG()
    Dim MyChart As ChartObject
    Set MyChart = Tabelle1.ChartObjects("Chart 1")
    
    Dim fileName As String
    fileName = "TestExport"

    Dim pathStr As String
    pathStr = ThisWorkbook.Path
    
    ' Export chart as .pdf
    MyChart.Chart.ExportAsFixedFormat Type:=xlTypePDF, FileName:=pathStr & "\" & fileName
   
    ' Convert .pdf file to .svg
    Dim ret As Double
    ret = Shell("cmd.exe /k cd /d """ & pathStr & """ & " & "pdftocairo -svg -f 1 -l 1 " & fileName & ".pdf", vbHide)
End Sub

Note that the text in the resulting .svg file isn't selectable and the file is larger than the file generated by manual export (241 KB vs. 88 KB in my test). The file is definitely infinite resolution, so not that weird bitmap embedded in a .svg file one occasionally sees but comes with another little problem:

Unfortunately, the ExportAsFixedFormat method creates a PDF 'page' where the graphic is positioned on the page depending on the position on the worksheet. The .svg conversion unfortunately keeps this 'page' format. I had to learn that getting rid of this problem is not as simple as I initially thought because excel does not support custom page sizes and therefore exporting a chart as .pdf without white borders seems pretty much impossible, see this bountied but unsolved question (Edit: I solved it in the following part and also posted my method as answer to that question). I tried several methods they didn't even think of in this linked question and still didn't manage to get it done properly using only Excel, it might be possible depending on your printer drivers but I'm not going that way...

Exporting to clean SVG without the white bars:

The easiest workaround is to just use Word to properly export the chart as .pdf:

Sub ExportChartToSVG()
    Dim MyWorksheet As Worksheet
    Set MyWorksheet = Tabelle1
    
    Dim MyChart As ChartObject
    Set MyChart = MyWorksheet.ChartObjects(1)
    
    Dim fileName  As String
    fileName = "TestExport"
    
    Dim pathStr As String
    pathStr = ThisWorkbook.Path
    
    'Creating a new Word Document
    'this is necessary because Excel doesn't support custom pagesizes
    'when exporting as pdf and therefore unavoidably creates white borders around the
    'chart when exporting
    Dim wdApp As Object
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = False
    
    Dim wdDoc As Object
    Set wdDoc = wdApp.Documents.Add
    
    MyChart.Copy
    wdDoc.Range.Paste
    
    Dim shp As Object
    Set shp = wdDoc.Shapes(1)
    
    With wdDoc.PageSetup
        .LeftMargin = 0
        .RightMargin = 0
        .TopMargin = 0
        .BottomMargin = 0
        .PageWidth = shp.Width
        .PageHeight = shp.Height
    End With
    shp.Top = 0
    shp.Left = 0
    
    wdDoc.saveas2 fileName:=pathStr & "\" & fileName, FileFormat:=17  '(wdExportFormatPDF)
    wdApp.Quit 0 '(wdDoNotSaveChanges)
    Set wdApp = Nothing
    Set wdDoc = Nothing
    Set shp = Nothing

    ' Convert .pdf file to .svg
    Dim ret As Double
    ret = Shell("cmd.exe /k cd /d """ & pathStr & """ & " & "pdftocairo -svg -f 1 -l 1 " & fileName & ".pdf", vbHide)
End Sub

The resulting .pdf and .svg look exactly the same as the manually exported .svg, with only the .pdf having selectable text. The .pdf file remains in the folder. If necessary, it can easily be deleted later via VBA code...

If this method is used to export a larger number of charts, I strongly recommend moving it into a class and having the class hold an instance of the Word application, so it doesn't constantly reopen and close Word. It has the added benefit of making the actual code to export very terse and clean.

Class-based method for exporting to clean SVG:

The code for exporting becomes very simple:

Sub ExportChartToSVG()
    Dim MyWorksheet As Worksheet
    Set MyWorksheet = Tabelle1
    
    Dim MyChart As ChartObject
    Set MyChart = MyWorksheet.ChartObjects(1)
    
    Dim fileName  As String
    fileName = "TestExport"
    
    Dim filePath As String
    filePath = ThisWorkbook.Path
    
    Dim oShapeExporter As cShapeExporter
    Set oShapeExporter = New cShapeExporter
    
    ' Export as many shapes as you want here, before destroying oShapeExporter
    ' cShapeExporter can export objets of types Shape, ChartObject or ChartArea
    oShapeExporter.ExportShapeAsSVG MyChart, fileName, filePath

    Set oShapeExporter = Nothing
End Sub

Code for class module called cShapeExporter:

Option Explicit

Dim wdApp As Object
Dim wdDoc As Object
Dim wdWasOpen As Boolean

Private Sub Class_Initialize()
    If WordIsRunning Then
        Set wdApp = GetObject(, "Word.Application")
        wdWasOpen = True
    Else
        Set wdApp = CreateObject("Word.Application")
        wdApp.Visible = False
        wdWasOpen = False
    End If
    
    Set wdDoc = wdApp.Documents.Add
    
    With wdDoc.PageSetup
        .LeftMargin = 0
        .RightMargin = 0
        .TopMargin = 0
        .BottomMargin = 0
    End With
End Sub

Private Sub Class_Terminate()
    If Not wdWasOpen Then
        wdApp.Quit 0 '(wdDoNotSaveChanges)
    Else
        wdDoc.Close 0
    End If
    Set wdApp = Nothing
    Set wdDoc = Nothing
End Sub

Public Sub ExportShapeAsSVG(xlShp As Object, fileName As String, filePath As String)
    If TypeName(xlShp) = "ChartObject" Or TypeName(xlShp) = "Shape" Or TypeName(xlShp) = "ChartArea" Then
        'fine
    Else
        MsgBox "Exporting Objects of type " & TypeName(xlShp) & " not supported, sorry."
        Exit Sub
    End If
    
    xlShp.Copy
    wdDoc.Range.Paste
    
    Dim wdShp As Object
    Set wdShp = wdDoc.Shapes(1)
    
    With wdDoc.PageSetup
        .PageWidth = wdShp.Width
        .PageHeight = wdShp.Height
    End With
    
    wdShp.Top = 0
    wdShp.Left = 0
    
    ' Export as .pdf
    wdDoc.saveas2 fileName:=filePath & "\" & fileName, FileFormat:=17  '(wdExportFormatPDF)
    
    ' Convert .pdf file to .svg
    Dim ret As Double
    ret = Shell("cmd.exe /k cd /d """ & filePath & """ & " & "pdftocairo -svg -f 1 -l 1 " & fileName & ".pdf", vbHide)
    
    ' Delete temporary shape in wdDoc
    wdShp.Delete
End Sub

Private Function WordIsRunning() As Boolean
    Dim wdApp As Object
    On Error Resume Next
    Err.Clear
    Set wdApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
        WordIsRunning = False
    Else
        WordIsRunning = True
    End If
End Function

Installing the Poppler utility:

I'm assuming you are using Windows here, on Linux getting Poppler is trivial anyway...

So on Windows, I'd suggest installing it using the chocolatey packet manager for Windows. To install chocolatey, you can follow these instructions (takes <5 min).

When you have chocolatey, you can install Poppler with the simple command

choco install poppler

and you are ready to run the code I proposed for converting .pdf to .svg.

If you prefer installing Poppler in a different way, there are various options described here, but I'd like to add some notes about some of the methods:

1. Downloading the binaries didn't work for me, running the utility would always result in errors.
2. Installing via Anaconda (conda install -c conda-forge poppler) somehow didn't work for me either. The installation just failed.
3. Installing via the Windows Subsystem for Linux did work, and the utility worked too, but if you don't already have wsl including a distribution installed you will have to download and install several hundred MB ob data which might be overkill.
4. If you have MiKTeX installed, the utility is supposed to be included (and was in my case). I tried the utility from my MiKTeX install, and somehow it didn't work.