Updating linked tables
When the data is refreshed, an ‘Errors in Linked Tables’ dialog is shown (as below) so that the connection can be managed.
The options for management (as below) are rather self-explanatory and allow for; The selection of an existing excel table completely redefines the import so that the refresh will alter the columns and data in the table so that the powerpivot table will be exactly the same as the excel table.
Connect = "ODBC; Description=___; DRIVER=SQL Server; SERVER=___; APP=Microsoft Office 2010; DATABASE=___" tb. I'm working with a legacy Microsoft Access database that we've recently updated to use linked tables/SQL Server backend. I'm making some changes to the structure of the data and would like to programmatically update the linked table reference. One way around this is to define the excel table and its name before the excel data is imported into powerpivot.
This is easily achieved by selecting a formatting style from the ‘Format as Table’ button in the Home ribbon.(I'd like to have a function that will flip between dev and production, so I don't consider the above route to be an option.) Access/VB isn't my strongest skill anymore, but from MSDN (this and this) it seems like "~TMP") Then Debug.