FYI: Excel Update Links change noticed

[stanl]

I have employed a standard method to either open or create an excel file by setting certain application properties

Code (WINBATCH) Select Expand


oXL.Visible             = 1 
oXL.ScreenUpdating      = 1   
oXL.UserControl         = 1
oXL.DisplayAlerts       = 0
oXL.UpdateLinks      = 0


But it appears that in Excel 2013 or greater UpdateLinks is no longer an application property but a Workbook property. The application now features AskToUpdateLinks and my workaround is

Code (WINBATCH) Select Expand


oXL.Visible             = 1 
oXL.ScreenUpdating      = 1   
oXL.UserControl         = 1
oXL.DisplayAlerts       = 0
If Int(oXL.Version) <15
   oXL.UpdateLinks      = 0
Else
   oXL.AskToUpdateLinks = 0
EndifI


and as part of my standard library of udfs will make it easier to re-compile older exes for user versions.  Even better, I can set the visible and links properties as parameters to the udf and control from the main script.

The AskToUpdate property controls whether prompts are given to update links so if you want to avoid this set to 0 [false]. However, the 0 setting will automatically update any links and for files that might use power query or power pivot the load time can really increase.

This may or may not be useful to anyone reading, but I'm personally happy to have stumbled upon it.

Oh.... and when my job was terminated on May 17, it was depressing at my age. But I start again tomorrow with even a better position.