Excel VBA update chart not visible

Question

I have an excel file which iterates over different excel files to update them and create a PDF file from them.

The macro iterates over rows and opens the files. This code opens the files:

Private Sub openFile(row As Integer)
    Dim filePath As String
    Dim wb As Workbook

    filePath = Application.ActiveWorkbook.Path + "\" + allRows(row).filename
    Set wb = Workbooks.Open(filePath)
    Application.Run "RefreshEntireWorksheet"
    
    Application.OnTime Now + (TimeSerial(0, 1, 59)), "'ThisWorkbook.updateCharts """ & row & "'"
    
    Application.OnTime Now + (TimeSerial(0, 2, 59)), "'ThisWorkbook.createEmail'"
End Sub

Now if there are multiple files, only 1 file has the charts properly updated (the file being on the front). It seems like the updateCharts code doesn't work at all:

Sub updateCharts(row As Integer)
    
    Dim SheetName As String
    Dim wb As Workbook
    
    SheetName = "Sheet1"
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With


    Set wb = Workbooks(allRows(row).filename)
    wb.Activate
    wb.Sheets(SheetName).Activate
    For Each cht In wb.Sheets(SheetName).ChartObjects
        cht.Chart.Refresh
        DoEvents
    Next cht
    
End Sub

I checked following links for answers, but none helped:

They all suggest to add an DoEvent, but that doesn't work. So the question is, how can I update charts in a different workbook via VBA?

When you say: It seems like the updateCharts code doesn't work at all, does it throw an error? does it run without an error but nothing happens? Please provide some more details. Also, what is allRows? i.e. what data type is it?. You shouldn't use things like Activate. If you qualify your workbook/worksheets, you don't need to activate them (unless you are doing that just to see what happens?) — Zac
@Zac The code runs fine without errors, but nothing is happening. The Activate parts are already removed as they messed up the resulting PDF. allRows is a made up property which simply refers to all the rows. Different columns are different properties. That part is working just fine, its just that charts are not properly updating. From what I gathered so far, it should be enough to trigger a CellChanged event, as they aren't triggered on files in the background — XtremeBaumer

Chronocidal Chronocidal · Accepted Answer · 2020-07-21T11:18:56

I have encountered similar issues in the past, and have observed that they seem to increase as the Version of Excel increments; I suspect it is a case of falling afoul of side-effects caused efficiency enhancements elsewhere to speed up the Application in general (reducing unnecessary drawing on the screen, et cetera)

One thing that I have found (sometimes) works is to try and force the Application Window to Refresh/Redraw, by setting the WindowState to itself:

Application.WindowState = Application.WindowState

This is similar to how some people suggest using ActiveWindow.SmallScroll down:=0, ActiveWindow.SmallScroll 0, ActiveWindow.SmallScroll, or ActiveWindow.SmallScroll down:=1: ActiveWindow.SmallScroll up:=1

Combining this with the ScreenUpdating toggle, and the DoEvents, you can create a quick Subroutine like this:

Public Sub RedrawScreen()
    Dim ScreenUpdating As Boolean
    ScreenUpdating = Application.ScreenUpdating

    Application.ScreenUpdating = True
    Application.WindowState = Application.WindowState
    DoEvents

    Application.ScreenUpdating = ScreenUpdating
End Sub

Excel VBA update chart not visible

1 Answers