ActiveChart does not work when running a batch. How to solve it?

[dpConsulendo]

Hi Guys,
I am having difficulty running a small report via Task Scheduler. It works fine updating form the database but this last code gives me a headache.

The error says:

"Run-time error '91':​

Object cariable or With block varible not set​

I know its the highligthed with red color, but just can't get around it..

Any hints??

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Const FName As String = "\\srv1\Data\KPI.png"
Dim pic_rng As Range
Dim ShTemp As Worksheet
Dim ChTemp As Chart
Dim PicTemp As Picture
Application.ScreenUpdating = False
Set pic_rng = Worksheets("Dashboard").Range("E17:AO77")
Set ShTemp = Worksheets.Add
Charts.Add
[SIZE=4][FONT=arial black][COLOR=#ff0000]ActiveChart.Location Where:=xlLocationAsObject, Name:=ShTemp.Name[/COLOR][/FONT][/SIZE]
Set ChTemp = ActiveChart
pic_rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
ChTemp.Paste
Set PicTemp = Selection
With ChTemp.Parent
.Width = 1920
.Height = 940
End With
ChTemp.Export Filename:="\\srv1\Data\KPI.png", FilterName:="png"
Application.DisplayAlerts = False
ShTemp.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[dpConsulendo]

I still have this issue. Is there anybody that could give a hint..?

 

[Domenic]

Since you only get an error when running a batch, it's likely a timing issue. Try pausing the macro for 2 or 3 seconds after Charts.Add. And maybe even try ActiveSheet.Activate after pausing. In any case, here's an alternative that creates a chartobject instead of a chart sheet/chartobject, and that doesn't require creating a temporary worksheet. So it should be more efficient.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim pic_rng As Range
    Dim chrt_obj As ChartObject
    
    Const FName As String = "\\srv1\Data\KPI.png"
    
    Application.ScreenUpdating = False
    
    With Worksheets("Dashboard")
        Set pic_rng = .Range("E17:AO77")
        pic_rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
        Set chrt_obj = .ChartObjects.Add(Left:=pic_rng.Left, Top:=pic_rng.Top, Width:=pic_rng.Width, Height:=pic_rng.Height)
    End With
    
    With chrt_obj
        .Activate
        With .Chart
            .ChartArea.Format.Line.Visible = msoFalse
            .Paste
            .Export Filename:=FName, FilterName:="PNG"
        End With
        .Delete
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Hope this helps!

 

[dpConsulendo]

Hi Domenic,

The error is now gone, thank you, but now there is only a blank image instead of charts. If I run the batch when I am logged off it shows blank. If I run it when logged on it shows the charts. Do you know of a workaround… ?

 

[Domenic]

Which code did you end up using? Can you post the exact code?

 

[dpConsulendo]

I used your code. My code with pausing the macro did not work. Tried 3 sec., 5 sec. ... 30 sec.

Your code does execute, but leaves the chart blank. ​

 

[Domenic]

Activating the chartobject before pasting the picture should have helped.

The only other thing I could suggest is to try pausing the macro for several seconds after the chartobject is created, if you haven't already done so.