вообще-то здесь уже много раз это описывалось,
ну если этого мало то вот выдержка из MS KB

Вообще-то да, поиском по форуму я тоже несколько раз пользовался - потом бросил - что-то найти здесь не представляется возможным, однако частенько предлагают - "поищите...", да еще и ссылки потом дают. А ведь Ваш-то вопрос из области FAQ... Не радует такое отношение со стороны модераторов-администраторов.

Я когда разбирался с этой проблемой, так получилось что в этот форум еще не ходил и с экселем (с VBA в частности) был "на Вы", и спрашивать было не у кого, поэтому надеялся только на себя.
Сценарий поиска был простой:
Если сводные таблицы работают с данными через стандартные микрософтовские интерфейсы, значит где-то д.б. строка подключения. Открыв экселевский файл в Фаре на просмотр (F3), решил, попробую-ка я найти имя сервера к которому мы подключались раньше. Aга - нашел, но радость-то какая - целиком строка подключения как в документации. Значит эксель в каком-то объекте ее хранит. Вопрос в каком. Запустил эксель, открыл Visual Basic Editor, зашел в Immediate Window и простым ковырянием объектов выяснил, что у ActiveWorkbook есть PivotCaches у которых, в свою очередь, есть свойство Connection в котором и содержится ссылка на наш старый сервер. Ну а теперь все просто: ее надо только поменять - а сделать это можно там же в Immediate Window. И на все - про все у меня ушло тогда не более получаса... Потом уже я оформил это в маленькую фекцию на VB.

Что касается MS KB - они пошли другим путем, более универсальным и приняли во внимание что надо поменять что-то (путь, имя сервера или имя БД) во всех подключениях и командах ко внешним источникам, которые есть в книге. Поэтому и перебирают все Листы на предмет наличия QueryTables и PivotTables. У каждой QueryTable есть своя строка подключения и команда, а у каждой PivotTable есть ссылка на PivotCache (несколько PivotTable могут использовать один и тот же PivotCache, поэтому для замены логично и достаточно было бы перебрать PivotCaches у WorkBook). У PivotCache тоже строка подключения (та самая, которую я инашел) и команда. В них они и меняют старое на новое (Substitute). Вот и вся логика...

(Я времени потерял больше на это описание, однако надеюсь может кому и сгодится...) :)

Да, странно.
Не понял почему у Вас не получилось. Повторил все у себя в Immediate Window:
- возвращает строку подключения
там же:
Все работает...