Save Chart of a spreadsheet in a folder by VB

Question

Hey there, guys. I’m a beginner in VB programming and I’m with a project, where I access a spreadsheet and insert data into it and this data is shown in graph. How do I save this graphic as an image to a folder and then use that image? Is it possible? I use VB and Excel 2010.

Answer 1

Save Graphics as PNG

To save all charts use this code:

    For Each sht In ActiveWorkbook.Sheets
        x = 1
        For Each co In sht.ChartObjects
            co.Chart.Export SeuDiretório & "\" & sht.Name _
                          & "_" & x & ".png", "PNG"
            x = x + 1
        Next co
    Next sht

Complete Code

A more complete code to save all charts in the same file folder and in case you don’t find the file, open a window to choose the directory path:

Sub ExportarGrafico()
    Dim strPath As String
    Dim co As ChartObject
    Dim x As Long
    Dim sht As Worksheet

    strPath = ThisWorkbook.Path
Inicio:
    If strPath <> "" Then
        For Each sht In ThisWorkbook.Sheets
            x = 1
            For Each co In sht.ChartObjects
                co.Chart.Export strPath & "\" & sht.Name _
                              & "_" & x & ".png", "PNG"
                x = x + 1
            Next co
        Next sht
    Else
        MsgBox "A pasta do arquivo não foi encontrada - Escolha a pasta."
        strPath = GetFolder
        GoTo Inicio
    End If
End Sub

Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select folder to export Charts to"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show = True Then sItem = .SelectedItems(1)
    End With
    GetFolder = sItem
    Set fldr = Nothing
End Function

Explanation

strPath = Thisworkbook.Path

Is assigned to the String variable StrPath the Excel file directory.

If strPath <> "" Then

If you find the file path, that is, the strPath variable is not empty, export all graphics as PNG.

Else

Otherwise:

    MsgBox "A pasta do arquivo não foi encontrada - Escolha a pasta."

Shows the message that the file was not found.

    strPath = GetFolder

Calls the Getfolder function to choose a folder.

    GoTo Inicio

Back to Inicio: