Excel VLOOKUP run from MSAccess remote automation stopped auto Calculate Dec 8, 2016 (1 Viewer)

[Rx_]

Did anyone notice the VLookup when used with Access VBA remote automation fails to auto refresh?

The VLookup was working on multiple PC for several weeks.
Around December 8-12 of 2016 - it stopped working on multiple PCs.

An application that processes a large text file, processes information from SQL Server, creates dozens of special cross-tabs, and other custom process in Excel, is saved as in a business process.
The Access run VBA Excel automation remotely opens up several Excel worksheets (Access and Excel reside on the same desktop folder) to insert a column and programmatically create a VLookup custom formula. Some of the data is immediately transferred to an Access Local Table and used in several next steps for the Excel workbook.
The entire Excel project is Saved as with date time stamps.

One of the users had a problem this week. Opening up the Excel, the Vlookup had not Refreshed! Clicking F9 on Excel - the Vlookup columns all updated correctly!
Going back several weeks of archived files, there was never a problem.
However, everyone's PC running the same code now have this problem.

One user had only just had IT upgrade them to Office 2016 to match the rest of us. Same problem.

Opening a saved workbook, the vlookup is stubborn to still refresh.
Either click In the cell, click in the formula - and only that cell will refresh.
A manual F9 (refresh) would update the entire column.

The code below (see the Refresh statements at the end) fixed this issue.
It is just strange that it suddenly appeared.
Wondering if anyone else came across it?

 With Objxl
    .Sheets(1).Select
    .Columns("N:N").Select
    .Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("N1").Select
    .ActiveCell.FormulaR1C1 = "Correct Expenditure Type"
    .Range("N1").Select
    .Selection.Font.Bold = True
    .Range("N2").Select
    .Selection.Style = "Good"
    LastRow = .Sheets(1).Range("A1").CurrentRegion.Rows.Count
    .ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'BAD Expenditure Type Fixes'!C[-13]:C[-12],2,FALSE)"  ' fixed Nov 2 was offset 5 rows on AP and ISS
    .Selection.AutoFill Destination:=.Range("N2:N" & LastRow), Type:=xlFillDefault
    .Columns("N:N").EntireColumn.AutoFit
    xlWB.Save
    
    .Sheets(2).Select
    .Columns("N:N").Select
    .Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("N1").Select
    .ActiveCell.FormulaR1C1 = "Correct Expenditure Type"
    .Range("N1").Select
    .Selection.Font.Bold = True
    .Range("N2").Select
    .Selection.Style = "Good"
    LastRow = .Sheets(2).Range("A1").CurrentRegion.Rows.Count
    .ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'BAD Expenditure Type Fixes'!C[-13]:C[-12],2,FALSE)"
    .Selection.AutoFill Destination:=.Range("N2:N" & LastRow), Type:=xlFillDefault
    .Columns("N:N").EntireColumn.AutoFit
    DoEvents
    .Calculate ' Have dozens of documents that prove this use to auto Calculate 
    DoEvents ' Added to force Calculate
    .Calculate ' Didn't need to do this before, suddenly it won't calculate vlookup unless specifically ordered to!
    DoEvents
    '.CalculateUntilAsyncQueriesDone
    .ActiveWorkbook.RefreshAll ' it worked most the time, came back and added this - now it works every time
    
    xlWB.Save
End With

 

[boerbende]

Did not check your code, but I had also once that we had something strange with dates. Don't remember exactly when, but day and month were changed by MS on one computer.

As you report 8-12-16, maybe MS can have converted in to Aug 12th 2016? or Dec 16th 2012