ODBCConnection.RobustConnect (Excel)

Returns or sets how an ODBC connection connects to its data source. Possible return values are xlAlways - The PivotTable cache or query table always uses external source information (as defined by the SourceConnectionFile or SourceDataFile property) to reconnect, xlAsRequired - The PivotTable cache or query table uses external source information to reconnect by using the Connection property, xlNever - The PivotTable cache or query table never uses source information to reconnect.

If you use robust connectivity, when Microsoft Excel is unable to establish a connection by using the workbook connection information, Excel will check if the workbook connection has a path to an external connection file. If it does, Excel will open the external connection file and try to connect by using the information contained in the external connection file. If a connection can be established after using the external connection file, Excel will then update the workbook's connection object. This provides robust connectivity in scenarios where an information technology department maintains and updates connections in a central place, permitting a user's workbook to automatically fetch those updates whenever the previous version of the connection (cached within the workbook) fails.

ActiveWorkbook.Connections(1).ODBCConnection.RobustConnect = xlAlways