***********************************************************************

Under Tools, set a reference to MicrosoftScriptingRuntime.
Put the following code in Module1.
There is more code below to put in another location as well.

***********************************************************************

Public Sub updatePivot()
    Dim oPivot
    Dim thisPivot As PivotTable
    
    On Error Resume Next
    For Each oPivot In thisWorksheet.PivotTables
        Set thisPivot = oPivot
        thisPivot.PivotCache.Refresh
        thisWorksheet.PageSetup.PrintArea = thisPivot.DataBodyRange.Address
        thisPivot.PrintTitles = True
        FitRows thisPivot:=thisPivot
        
    Next oPivot
    On Error GoTo 0

End Sub

Public Sub updateConnections()
    'Copyright (c) 2012 Richard M. Bixler, All Rights Reserved. Do not delete this copyright notice.
    
    'Updates Connections and Queries, to definitions defined in table on DataSources tab.
    Dim connectionSheet As Worksheet
    Dim tblConnectionConfig As ListObject
    Dim thisListRow As ListRow
    Dim thisConnection As WorkbookConnection
    Dim strConnectionName As String, strAltPath As String, strPriPath As String, strTableName As String, strLinkType As String, strSQL As String, wkstnIDStringExisting As String, wkstnIDStringNew As String, leftDelim As String, leftIDdelim As String
    Dim thisConnectionRowFound As Boolean
    Dim thisCell As Range
    Dim nameIndex As Integer, altPathIndex As Integer, priPathIndex As Integer, tableNameIndex As Integer, linkTypeIndex As Integer, sqlIndex As Integer
    
    Dim thisListObject As ListObject
    Dim thisListObjectObject As Variant, thisSheetObject As Variant, objectRow As Variant
    Dim thisSheet As Worksheet
    Dim thisQuery As QueryTable
    Dim thisConnectionString As String, dataSourceStringExisting As String, strPathSymbol As String, strThisWorkbookPath As String, strTargetFileName As String
    Dim oldPath As String, dataSourceStringNew As String, dataSourceFileName As String
    Dim dbSourceFile As String, dbTableSheet As String, sDatabase As String, dbTable As String, sSQL As String
    Dim leftChar As Long, rightChar As Long, rightChar2 As Long
    Dim thisRow As Range
    Dim fsFileSystem As New FileSystemObject
    Dim thisPivotObject As Variant
    Dim thisPivot As PivotTable
    Dim thisPivotCache As PivotCache
    Dim thisWorkbook As Workbook
    Dim thisWBQuery As WorkbookQuery
    Dim newFormula As String
    
    leftDelim = ";Data Source="
    leftIDdelim = ";Workstation ID="
    Set thisWorkbook = Module1.myWorkbook
    Set myWorkbook = thisWorkbook
    strThisWorkbookPath = thisWorkbook.Path
    Set connectionSheet = thisWorkbook.Worksheets("DataSources")
    Set tblConnectionConfig = connectionSheet.ListObjects("tblConnectionConfig")
    
    'Index the tblConnectionConfig column names
    nameIndex = 0
    priPathIndex = 0
    altPathIndex = 0
    tableNameIndex = 0
    linkTypeIndex = 0
    For Each thisCell In tblConnectionConfig.HeaderRowRange
        Select Case thisCell.Value2
            Case "Connection Name"
                nameIndex = thisCell.Column
            Case "Primary Path"
                priPathIndex = thisCell.Column
            Case "Alternate Path"
                altPathIndex = thisCell.Column
            Case "Table Name"
                tableNameIndex = thisCell.Column
            Case "LinkType"
                linkTypeIndex = thisCell.Column
            Case "SQL"
                sqlIndex = thisCell.Column
        End Select
        
    Next thisCell
    
    'Process all workbook Connections
    For Each thisConnection In thisWorkbook.Connections
        'Find listRow for this connection
        thisConnectionRowFound = False
        For Each thisListRow In tblConnectionConfig.ListRows
            strConnectionName = ""
            strAltPath = ""
            strPriPath = ""
            strTableName = ""
            strLinkType = ""
            strSQL = ""
            If nameIndex > 0 Then strConnectionName = thisListRow.Range.Cells(1, nameIndex)
            If altPathIndex > 0 Then strAltPath = thisListRow.Range.Cells(1, altPathIndex)
            If priPathIndex > 0 Then strPriPath = thisListRow.Range.Cells(1, priPathIndex)
            If tableNameIndex > 0 Then strTableName = thisListRow.Range.Cells(1, tableNameIndex)
            If linkTypeIndex > 0 Then strLinkType = thisListRow.Range.Cells(1, linkTypeIndex)
            If sqlIndex > 0 Then strSQL = thisListRow.Range.Cells(1, sqlIndex)
            
            If (strLinkType = "TableSource" Or strLinkType = "PivotSource" Or strLinkType = "mppImport") And strConnectionName = thisConnection.Name Then
                thisConnectionRowFound = True
                Application.StatusBar = "Linking " & strConnectionName
                'Perform the connection link update
                'First convert relative path to absolute
                'Primary path first
                sDatabase = strEvalPath(strPriPath, thisWorkbook, "path")
                If sDatabase = "" Then sDatabase = strEvalPath(strAltPath, thisWorkbook, "path")
                If sDatabase = "" Then Exit For
                
                If sDatabase <> "" And (fsFileSystem.FileExists(filespec:=sDatabase) Or strEvalPath(strPriPath, thisWorkbook, "pathSymbol") = "") Then
                    'At this point, sDatabase contains correct NEW path. Fix up connection path here
                    'Get the existing connection string, and extract the datasource file and path
                    thisConnectionString = thisConnection.OLEDBConnection.Connection
                    If InStr(1, thisConnectionString, leftDelim, vbTextCompare) > 0 Then
                        leftChar = InStr(1, thisConnectionString, leftDelim, vbTextCompare) + Len(leftDelim)
                        rightChar = InStr(leftChar, thisConnectionString, ";", vbTextCompare)
                        dataSourceStringExisting = Mid(thisConnectionString, leftChar, rightChar - leftChar)
                        
                        'From the existing datasource file path, separate into path and file name
                        rightChar2 = InStrRev(dataSourceStringExisting, "\", -1, vbTextCompare)
                        oldPath = Left(dataSourceStringExisting, rightChar2)
                        dataSourceFileName = Right(dataSourceStringExisting, Len(dataSourceStringExisting) - rightChar2)
                        
                        'Put the new path with the datasource file name, and update the connection string
                        dataSourceStringNew = sDatabase
                        thisConnectionString = Replace(thisConnectionString, dataSourceStringExisting, dataSourceStringNew, 1, -1, vbTextCompare)
                    
                    End If
                    
                    If InStr(1, thisConnectionString, leftIDdelim, vbTextCompare) > 0 Then
                        leftChar = InStr(1, thisConnectionString, leftIDdelim, vbTextCompare) + Len(leftIDdelim)
                        rightChar = InStr(leftChar, thisConnectionString, ";", vbTextCompare)
                        wkstnIDStringExisting = Mid(thisConnectionString, leftChar, rightChar - leftChar)
                        wkstnIDStringNew = thisWorkbook.Worksheets("DataSources").Range("Hostname").Value2
                        thisConnectionString = Replace(thisConnectionString, wkstnIDStringExisting, wkstnIDStringNew, 1, -1, vbTextCompare)
                    
                    End If
                    
                    If strSQL = "-" Then
                        sSQL = "SELECT * FROM " & strTableName
                        
                    Else
                        sSQL = strSQL
                        
                    End If
                    
                    'Update the query object with the new source file info, and refresh
                    thisConnection.OLEDBConnection.Connection = thisConnectionString
                    thisConnection.OLEDBConnection.SourceConnectionFile = dataSourceStringNew
                    thisConnection.OLEDBConnection.CommandType = xlCmdSql
                    thisConnection.OLEDBConnection.CommandText = sSQL
                            
                
                Else    'file does not exist
                    MsgBox prompt:="File does not exist: " & sDatabase, Buttons:=vbOKOnly, Title:="Missing File"
                    Application.StatusBar = ""
                    Exit Sub
                    
                End If  'fsFileSystem.FileExists
                
            End If
            If thisConnectionRowFound Then Exit For
            
        Next thisListRow
        
    Next thisConnection
    
    'Process all workbook Queries
    For Each thisWBQuery In thisWorkbook.Queries
        'Find listRow for this Query
        thisConnectionRowFound = False
        For Each thisListRow In tblConnectionConfig.ListRows
            strConnectionName = ""
            strAltPath = ""
            strPriPath = ""
            strTableName = ""
            strLinkType = ""
            strSQL = ""
            If nameIndex > 0 Then strConnectionName = thisListRow.Range.Cells(1, nameIndex)
            If altPathIndex > 0 Then strAltPath = thisListRow.Range.Cells(1, altPathIndex)
            If priPathIndex > 0 Then strPriPath = thisListRow.Range.Cells(1, priPathIndex)
            If tableNameIndex > 0 Then strTableName = thisListRow.Range.Cells(1, tableNameIndex)
            If linkTypeIndex > 0 Then strLinkType = thisListRow.Range.Cells(1, linkTypeIndex)
            If sqlIndex > 0 Then strSQL = thisListRow.Range.Cells(1, sqlIndex)
            
            If ((strLinkType = "webQuery") Or (strLinkType = "mppQuery")) And strConnectionName = thisWBQuery.Name Then
                thisConnectionRowFound = True
                Application.StatusBar = "Linking " & strConnectionName
                
                'Perform the query link update
                'First convert relative path to absolute
                'Primary path first
                sDatabase = strEvalPath(strPriPath, thisWorkbook, "path")
                If sDatabase = "" Then sDatabase = strEvalPath(strAltPath, thisWorkbook, "path")
                If sDatabase = "" Then Exit For
                
                If sDatabase <> "" And (fsFileSystem.FileExists(filespec:=sDatabase) Or strEvalPath(strPriPath, thisWorkbook, "pathSymbol") = "") Then
                    'At this point, sDatabase contains correct NEW path. Fix up connection path here
                    'Get the new query connection formula, and then replace it into the query definition.
                    newFormula = getUpdateFormula(thisWBQuery, sDatabase, strTableName)
                    setUpdateFormula updQuery:=thisWBQuery, updFormula:=newFormula
                                                
                Else    'file does not exist
                    MsgBox prompt:="File does not exist: " & sDatabase, Buttons:=vbOKOnly, Title:="Missing File"
                    Application.StatusBar = ""
                    Exit Sub
                    
                End If  'fsFileSystem.FileExists
                
            End If 'End Processing this query using info in tblConnectionConfig
            If thisConnectionRowFound Then Exit For
            
        Next thisListRow
        
    Next thisWBQuery
    
    'Update Queries and Connections, clean up and exit sub
    thisWorkbook.RefreshAll
    Application.StatusBar = ""
    alignButtons
    
End Sub

Private Function getUpdateFormula(ByVal thisQuery As WorkbookQuery, newLoc As String, updTable As String) As String
    Dim thisFormula As String, leftString As String, locString As String, rightString As String, newFormula As String
    Dim countLeft As Integer, countRight As Integer
    Dim tableNameL As Integer, tableNameR As Integer
    Dim tempString As String
    
    thisFormula = thisQuery.Formula
    countLeft = InStr(2, thisFormula, """", vbTextCompare)
    leftString = Left(thisFormula, countLeft)
    rightString = Right(thisFormula, Len(thisFormula) - countLeft)
    countRight = InStr(1, rightString, """", vbTextCompare)
    locString = Left(rightString, countRight - 1)
    rightString = Right(rightString, Len(rightString) - Len(locString))
    
    newFormula = leftString & newLoc & rightString
    
    'Update table name. Find Item="tablename" string, and replace all instances of tablename.
    tableNameL = InStr(1, thisFormula, "Item=", vbTextCompare) + Len("Item=") + 1
    tableNameR = InStr(tableNameL, thisFormula, """", vbTextCompare)
    tempString = Mid(thisFormula, tableNameL, tableNameR - tableNameL)
    newFormula = Replace(newFormula, tempString, updTable, 1, , vbTextCompare)
    
    getUpdateFormula = newFormula

End Function

Private Sub setUpdateFormula(ByVal updQuery As WorkbookQuery, updFormula As String)
    updQuery.Formula = updFormula
    
End Sub

Public Function strEvalPath(ByVal strPathName As String, ByVal thisWorkbook As Workbook, ByVal returnType As String) As String
    'Copyright (c) 2012 Richard M. Bixler, All Rights Reserved. Do not delete this copyright notice.
    
    Dim strPathSymbol As String, strTargetFileName As String, strThisWorkbookPath As String
    
    strTargetFileName = ""
    strEvalPath = ""
    strThisWorkbookPath = thisWorkbook.Path
    If strPathName <> "" Then
        If Left(strPathName, 2) = ".\" Then
            strPathSymbol = ".\"
            strTargetFileName = Right(strPathName, Len(strPathName) - 2)
        ElseIf Left(strPathName, 2) = "\\" Then
            strPathSymbol = "\\"
        ElseIf Left(strPathName, 3) = "..\" Then
            strPathSymbol = "..\"
            strTargetFileName = Right(strPathName, Len(strPathName) - 3)
        Else
            strPathSymbol = ""
            strTargetFileName = strPathName
        End If
        
        Select Case strPathSymbol
            Case ""
                strEvalPath = strTargetFileName
                
            Case ".\"
                strEvalPath = strThisWorkbookPath & "\" & strTargetFileName
            
            Case Else
                strEvalPath = myFileSystemObject.GetAbsolutePathName(strPathName)
                
        End Select
        If (strPathSymbol <> "" And Not myFileSystemObject.FileExists(strEvalPath)) Then strEvalPath = ""
        Select Case returnType
            Case "path"
                'return value already set to path
                
            Case "pathSymbol"
                strEvalPath = strPathSymbol
            
            Case Else
                strEvalPath = ""
                
        End Select
            
            
    End If 'strPathName is not null
    
End Function


***********************************************************************

Place the following code behind the DataSources page.

***********************************************************************


Option Explicit
Private fsFileSystem As New FileSystemObject

Private Sub CommandButton1_Click()
    buttonProcessor whichButton:="mppImport"
    
End Sub

Private Sub CommandButton2_Click()
    Module1.updateConnections
    
End Sub

Private Sub RefreshLinkedServer_Click()
    buttonProcessor whichButton:="LinkedServer"
    
End Sub

Private Sub buttonProcessor(ByVal whichButton As String)
    'Copyright (c) 2019 Richard M. Bixler, All Rights Reserved. Do not delete this copyright notice.
    
    Dim tblConnectionConfig As ListObject
    Dim strConnectionName As String, strPrimaryPath As String, strAlternatePath As String, strTableName As String, strLinkType As String, strSQL As String
    Dim sDatabase As String, thisConnectionString As String, dataSourceStringExisting As String, wkstnIDStringExisting As String, wkstnIDStringNew As String, leftDelim As String, leftIDdelim As String, sqlCommandText As String
    Dim leftChar As Long, rightChar As Long
    Dim thisCell As Range, rngConnectionName As Range, rngPrimaryPath As Range, rngAlternatePath As Range, rngTableName As Range, rngLinkType As Range, rngSQL As Range
    Dim rowConnectionName As Integer, colConnectionName As Integer, colPrimaryPath As Integer, colAlternatePath As Integer, colTableName As Integer, colLinkType As Integer, colSQL As Integer
    Dim thisConnection As WorkbookConnection
    Dim connectionFound As Boolean
    Dim thisWorksheet As Worksheet
    Dim thisWorkbook As Workbook
    
    Set thisWorkbook = Module1.myWorkbook
    Set thisWorksheet = thisWorkbook.Worksheets("DataSources")
    Set tblConnectionConfig = thisWorksheet.ListObjects("tblConnectionConfig")
    
    'Catalog relevant named columns
    For Each thisCell In tblConnectionConfig.HeaderRowRange.Cells
        Select Case thisCell.Value2
            Case "Connection Name"
                Set rngConnectionName = thisCell
                colConnectionName = rngConnectionName.Column
            
            Case "Primary Path"
                Set rngPrimaryPath = thisCell
                colPrimaryPath = rngPrimaryPath.Column
            
            Case "Alternate Path"
                Set rngAlternatePath = thisCell
                colAlternatePath = rngAlternatePath.Column
            
            Case "Table Name"
                Set rngTableName = thisCell
                colTableName = rngTableName.Column
            
            Case "LinkType"
                Set rngLinkType = thisCell
                colLinkType = rngLinkType.Column
            
            Case "SQL"
                Set rngSQL = thisCell
                colSQL = rngSQL.Column
            
        End Select
        
    Next thisCell
    
    'Now run down LinkType column looking for LinkType set to "LinkedServer", and process each of those rows.
    For Each thisCell In tblConnectionConfig.DataBodyRange.Columns(colLinkType).Cells
        If thisCell.Value2 = whichButton Then
            rowConnectionName = thisCell.Row
            
            'Pick up relevant values from the row identified as "LinkedServer"
            strConnectionName = thisWorksheet.Cells(rowConnectionName, colConnectionName)
            strPrimaryPath = thisWorksheet.Cells(rowConnectionName, colPrimaryPath)
            strAlternatePath = thisWorksheet.Cells(rowConnectionName, colAlternatePath)
            strTableName = thisWorksheet.Cells(rowConnectionName, colTableName)
            strLinkType = thisCell.Value2
            strSQL = thisWorksheet.Cells(rowConnectionName, colSQL)
            leftDelim = ";Data Source="
            leftIDdelim = ";Workstation ID="

            
            Select Case thisCell.Value2
                Case "mppImport", "mppQuery"
                    rowConnectionName = thisCell.Row
                    
                    'If the named Connection exists, set its relevant properties
                    If Module1.exists(strConnectionName, thisWorkbook.Connections) Then
                        Set thisConnection = thisWorkbook.Connections(strConnectionName)
                        
                        'When Connection is set up, refresh it with the values set.
                        thisConnection.Refresh
                        
                    End If
                                    
                Case "webQuery"
                
                Case "PivotSource"
                
                Case "LinkedServer"
                    'Data Sources button "Link to Database File Specs" updates Connection source address.
                    'This button "Refresh Linked Server" updates LinkedServer connection address, and also re-instantiates the Linked Server
                    '   in that SQL Server instance.
                    'Operator sequence on re-location of files would be
                    '   Set Primary Path and Alternate Path in DataSources\tblConnectionConfig for all tables in re-located source files.
                    '       That will be Source File address for Excel files to be read by MS Access.
                    '       or <ServerName>\<SQLServer Instance Name> for Excel files to be read by SQL Server.
                    '   Set Primary Path and Alternate Path in DataSources\tblConnectionConfig for all Linked Servers to specify SQL Server instances in which they will reside.
                    '       <ServerName>\<SQLServer Instance Name> for Excel files to be read by SQL Server.
                    '       One Linked Server in each SQL Server Instance is required for each Excel Source File to be linked to SQL Server.
                    '       Separate configured SQL will be needed for Drop, Create, Execute SQL for each Linked Server. (CODE ONLY SUPPORTS SINGLE SET NOW).
                    '   Set Source File address for Excel Source File in configuration parameters for Drop, Create, Execute SQL.
                    
                    
                    'If the named Connection exists, set its relevant properties. Disable non-programmatic refreshes.
                    If Module1.exists(strConnectionName, thisWorkbook.Connections) Then
                        Set thisConnection = thisWorkbook.Connections(strConnectionName)
                        
                        sDatabase = Module1.strEvalPath(strPrimaryPath, thisWorkbook, "path")
                        If sDatabase = "" Then sDatabase = Module1.strEvalPath(strAlternatePath, thisWorkbook, "path")
                        If sDatabase = "" Then Exit For
                        
                        If sDatabase <> "" And (fsFileSystem.FileExists(filespec:=sDatabase) Or Module1.strEvalPath(strPrimaryPath, thisWorkbook, "pathSymbol") = "") Then
                            'At this point, sDatabase contains correct NEW path. Fix up connection path here
                            'Get the existing connection string, and extract the datasource file and path
                            thisConnectionString = thisConnection.OLEDBConnection.Connection
                            
                            If InStr(1, thisConnectionString, leftDelim, vbTextCompare) > 0 Then
                                leftChar = InStr(1, thisConnectionString, leftDelim, vbTextCompare) + Len(leftDelim)
                                rightChar = InStr(leftChar, thisConnectionString, ";", vbTextCompare)
                                dataSourceStringExisting = Mid(thisConnectionString, leftChar, rightChar - leftChar)
                                
                                'Put the new path with the datasource file name, and update the connection string
                                thisConnectionString = Replace(thisConnectionString, dataSourceStringExisting, sDatabase, 1, -1, vbTextCompare)
                                'Now sDatabase contains new path.file, thisConnectionString contains new connection path containing new path.file
                                
                            End If
                            
                            'Next, set the Workstation ID.
                            
                            If InStr(1, thisConnectionString, leftIDdelim, vbTextCompare) > 0 Then
                                leftChar = InStr(1, thisConnectionString, leftIDdelim, vbTextCompare) + Len(leftIDdelim)
                                rightChar = InStr(leftChar, thisConnectionString, ";", vbTextCompare)
                                wkstnIDStringExisting = Mid(thisConnectionString, leftChar, rightChar - leftChar)
                                wkstnIDStringNew = thisWorkbook.Worksheets("DataSources").Range("Hostname").Value2
                                thisConnectionString = Replace(thisConnectionString, wkstnIDStringExisting, wkstnIDStringNew, 1, -1, vbTextCompare)
                                
                            End If
                            
                            
                        Else    'file does not exist
                            MsgBox prompt:="File does not exist: " & sDatabase, Buttons:=vbOKOnly, Title:="Missing File"
                            Application.StatusBar = ""
                            Exit Sub
                            
                        End If  'fsFileSystem.FileExists
                        
                        'Update the query object with the new source file info, and refresh
                        
                        'These are read-only; must be set when connection is created.
                            'thisConnection.ODBCConnection.RefreshOnFileOpen = False
                            'thisConnection.ODBCConnection.BackgroundQuery = False
                            'thisConnection.ODBCConnection.RefreshPeriod = 0
                            'thisConnection.ODBCConnection.EnableRefresh = False
                        
                        thisConnection.OLEDBConnection.Connection = thisConnectionString
                        thisConnection.OLEDBConnection.SourceConnectionFile = sDatabase
                        thisConnection.OLEDBConnection.CommandType = xlCmdSql
                        
                        'Update SQL Server address/instance, and execute SQL to drop LinkedServer specified in the configured SQL, if it exists in that SQL Server instance.
                        thisConnection.OLEDBConnection.CommandText = thisWorkbook.Worksheets("DataSources").Range("Drop_spOpILinkedServer").Value2
                        thisConnection.Refresh

                        'Execute SQL to create a stored procedure in the SQL Server instance, that will create a LinkedServer in that instance that links to source files identified in the configured SQL.
                        thisConnection.OLEDBConnection.CommandText = thisWorkbook.Worksheets("DataSources").Range("Create_spOpILinkedServer").Value2
                        thisConnection.Refresh

                        'Execute SQL to execute the stored procedure in the SQL Server instance that creates the LinkedServer connecting to Excel source files (identified in the configured SQL).
                        thisConnection.OLEDBConnection.CommandText = thisWorkbook.Worksheets("DataSources").Range("Exec_spOpILinkedServer").Value2
                        thisConnection.Refresh
                        
                    End If  'End of Connection Exists
                    
                'End of "LinkedServer" Case
                    
            End Select  'Selection of code to execute button command
        
        End If  'Command in tblConnectionConfig line matches Button command
        
    Next thisCell   'Scan of tblConnectionConfig
    Module1.alignButtons

End Sub