0

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?

Improve this question
2
  • 1
    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?) Commented Jul 21, 2020 at 11:06
  • @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 Commented Jul 21, 2020 at 11:43

2 Answers 2

Reset to default
0

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
Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

I tried to call your code after refreshing all the charts, but nothing is happening
@XtremeBaumer Does the Chart show the correct data after the macro has run? There are 2 possibilities here: if the chart shows the wrong stuff even after the macro has finished, then the code for your chart is wrong (what code do you have to update the table that the Chart pulls data from?), and my answer does not apply. If your chart does display correctly after the macro has finished, but does not output the correct image during the macro, then... well, more investigation needed, because that's what this code was supposed to fix
The chart doesn't show any data at all, even after the macro finishes. If I go into the file after the macro and trigger an CellChanged event, then the correct data is displayed in every chart for that file. It happens only for the files that are not in the foreground. The data for the charts comes from addin formulas which update on file open
@XtremeBaumer What does your CellChanged event code do? Do you need to trigger it manually - either via Application.Run, or by changing a Cell and then DoEventsing - as part of the macro?
The CellChanged event code loops through all charts to set the minimum and maximum value of the y-axis. As of now I have no macro way to trigger the CellChanged event. I manually type something in a cell and then press enter to trigger the event
|
0

Try this (it works for me - Excel 2016): DoEvents must be in your code as others said before Delete all colours of the cells of the spreadsheet: all the background of the cells must be "no background"; use only automatic colour (black) for the text of all the cells. Save, close and reopen the file. Now start your macro and look at your chart. I don't know why it works, but.........it works! For those more capable than me: do the opposite: if your chart is updating with your macro running, try to colour some text or some cells. The chart will not update anymore. The Microsoft experts should check what is happening. Hope this helps. Bye, bb.

Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.