Ms-access – Microsoft Access Export to Excel

Easiest fix would be to have your networking folks point DNS for your old server name to your new server's IP address. Unless you rely on Kerberos that will likely work fine.

If that's not an option, then if you left click on the PivotTable, to the Data tab in the ribbon, then click Properties under the Connections section, then go to the Definition tab, please see if "Always use connection file" is checked. If it is, then you can just manually edit the .odc file in Notepad and point to the new server.

If "Always use connection file" isn't checked, then you can left click on the PivotTable, then go to the PivotTable Tools... Analyze tab in the ribbon, then click the Change Data Source button, then click Choose Connection, then choose a different .odc file to connect to. Your PivotTables should be preserved and they should now connect to your new server.

PowerShell is the way to go:

# Original Author : Bill Fellows (http://billfellows.blogspot.com/2011/03/powershell-export-query-to-csv.html)
# Modified by     : Kin Shah (http://dba.stackexchange.com/users/8783/kin)
                    # Added the -NoTypeInformation to remove "#TYPE System.Data.DataRow" from the header !

# http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html
$server = "servername\INSTANCE1"  #YourServer Name e.g. server1\instance1
$database = "your_db_name"    #your database name
$query = "select some columns from table inner join other table on table.id = othertable.id"

# powershell raw/verbatim strings are ugly
# Update this with the actual path where you want data dumped
# change here to the path that you want
$extractFile = @"
C:\SQLtoExcel.csv  
"@

# Use windows authentication !!
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# dump the data to a csv
# http://technet.microsoft.com/en-us/library/ee176825.aspx
# -NoTypeInformation will remove the first #TYPE System.Data.DataRow
$DataSet.Tables[0] | Export-Csv $extractFile  -NoTypeInformation

See also:

Multi-Tabbed Excel Through Powershell by Kevin Feasel