将图表导出为图像有时会生成空文件 [英] Exporting charts as Image sometimes generates empty files

问题描述

我正在执行一个宏，该宏将导出工作表中的所有图表，然后打开Outlook并将其附加.但是，我注意到，有几次图表确实导出，但是导出为0KB(已创建文件，但看不到图像)

I'm doing a macro that exports all the charts in the sheet and then opens Outlook and attaches them. However, I've noticed, several times the charts do export but as 0KB (the file is created, but the image can't be seen)

但并非所有图表都如此.只是其中的大多数，有时它会毫无问题地生成它们.(当我逐步执行代码时，所有图表生成都没有问题，同样，在逐步执行之后，我也会正常执行，所有图表都会生成，但是如果我关闭并重新打开工作簿，它会给出相同的问题，仅生成两个文件，其余为空文件)

But it doesn't happen to all the charts. Just most of them and sometimes, it generates them all without a problem. (When I execute the code step by step, all charts generate without a problem, also after executing the step by step, then I execute it normally and all charts generate, BUT if I close and reopen the workbook, it gives the same issue, generates only two and the rest are empty files)

代码如下:

Dim sheetNumber, Size, i As Integer
    Dim chartNames(), FNames() As String
    Dim objChrt As ChartObject
    Dim myChart As Chart


    'Activate Charts Sheet
    Sheets("GRAFICAS").Activate
    'Calculate Number of Charts in Sheet
    Dim chartNumber
    chartNumber = ActiveSheet.ChartObjects.Count
    'Redimension Arrays to fit all Chart Export Names
    ReDim chartNames(chartNumber)
    ReDim FNames(chartNumber)
    'Loops through all the charts in the GRAFICAS sheet
    For i = 1 To chartNumber
        'Select chart with index i
        Set objChrt = ActiveSheet.ChartObjects(i)
        Set myChart = objChrt.Chart
        'Generate a name for the chart
        chartNames(i) = "myChart" & i & ".png"

        On Error Resume Next
        Kill ThisWorkbook.Path & "\" & chartNames(i)
        On Error GoTo 0
        'Export Chart
        myChart.Export FileName:=Environ$("TEMP") & "\" & chartNames(i), Filtername:="PNG"
        'Save path to exported chart
        FNames(i) = Environ$("TEMP") & "\" & chartNames(i)
    Next i

我想念什么?

推荐答案

事实证明，对于Excel 2010-2013用户而言，这是一个随机错误.但是，经过更多的谷歌搜索之后.我遇到了答案此处

It turns out, it's a random error for Excel 2010-2013 users. However, after some more googling. I encountered the answer here

您只需要添加

objChrt.Activate

选择图表后.所以在我的情况下，最终代码如下:

After selecting the chart. So In my case the final code looks like this:

 For i = 1 To chartNumber
        'Select chart with index i
        Set objChrt = ActiveSheet.ChartObjects(i)
        objChrt.Activate
        Set myChart = objChrt.Chart
        'Generate a name for the chart
        chartNames(i) = "myChart" & i & ".png"

        On Error Resume Next
        Kill ThisWorkbook.Path & "\" & chartNames(i)
        On Error GoTo 0
        'Export Chart
        myChart.Export FileName:=Environ$("TEMP") & "\" & chartNames(i), Filtername:="PNG"
        'Save path to exported chart
        'Application.Wait (Now + #12:00:01 AM#)
        FNames(i) = Environ$("TEMP") & "\" & chartNames(i)
    Next i

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