将所有图表导出为PNG [英] Exporting all Charts as PNG

问题描述

我正在尝试将Excel文件中的所有图表导出为PNG图像.图表未嵌入工作表中，而是在创建后被移动为新工作表.

I'm trying to export all charts within my Excel file as a PNG image. The charts are not embedded in the worksheets, but have instead been moved as a new sheet upon creation.

我不熟悉VBA或Office宏，但我尝试根据我在网络上找到的代码示例将某些内容串在一起，但没有成功.

Not being familiar with VBA or office macros, I've tried stringing together something based on code examples I found on the web but with no success.

这是我尝试过的方法，它可能适用于工作表中嵌入的图表，但不适用于独立图表:

Here's what I've tried, which may work with charts embedded within worksheets but not with standalone charts:

Private Sub ExportChartsButton_Click()
    Dim outFldr As String
    Dim ws As Worksheet
    Dim co As ChartObject

    outFldr = GetFolder(ActiveWorkbook.Path) 
    For Each ws In ActiveWorkbook.Worksheets
        For Each co In ws.ChartObjects
            co.Export outFldr & "\" & ws.Name & ".png", "PNG"
        Next
    Next
End Sub

单击按钮时，似乎什么也没发生.

When the button is clicked, nothing seems to happen.

如果将内部循环替换为MsgBox co.ChartObjects.Count，则对于每个非图表工作表都会得到一个0弹出窗口，因此很明显，我没有遍历正确的对象(因此，没有图表，因此没有任何反应).

If I replace the inner loop with MsgBox co.ChartObjects.Count I get a 0 popup for each of my non-chart worksheets, so I'm obvious not iterating through the right objects (hence, no charts so nothing happens).

那么，如何遍历未嵌入工作表中的图表?

So, how do I iterate through Charts that are not embedded within worksheets?

推荐答案

我找到了解决方案.我必须使用ActiveWorkbook.Charts而不是.Worksheets.

I found a solution. I had to use ActiveWorkbook.Charts instead of .Worksheets.

Private Sub ExportChartsButton_Click()
    Dim outFldr As String
    Dim wc As Chart
    Dim co As ChartObject

    outFldr = GetFolder(ActiveWorkbook.Path)
    If outFldr = "" Then
        MsgBox "Export Cancelled"
    Else
        For Each wc In ActiveWorkbook.Charts
            wc.Export outFldr & "\" & wc.Name & ".png", "PNG"
        Next
    End If
End Sub

根据记录，GetFolder()定义为:

Function GetFolder(strPath As String) 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

非常欢迎评论/建议.

这篇关于将所有图表导出为PNG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！