Save Graph of a spreadsheet in a folder by VB

Question

Save Graph of a spreadsheet in a folder by VB

Navigation

#1 by (0 votes)

1

Hello everyone. I am a beginner in VB programming and I am with a project, where I access a worksheet and I enter data in it and this data is shown in graph. How do I save this chart as an image in a folder and then use that image? It's possible? I use VB and Excel 2010.

excel vba excel-vba

asked by anonymous 11.01.2018 / 07:40

1 answer

MIgrations error loading System.Configuration.ConfigurationManager - MIgrations SQL script monitoring [closed]

score 0 · Answer 1

Save Graphics as PNG

To save all graphics 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 the graphics in the same folder of the file and if it does not 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

String variable StrPath is assigned to the Excel file directory.

If strPath < > "" Then

If the file path is found, that is, the strPath variable is not empty, it exports all the 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

Return to Inicio: