Изменение пути к внешнему источнику данных сводной таблицы с помощью макроса Excel

Я работаю над проектом в MS Excel, в котором у меня есть несколько сводных таблиц, которые считывают данные из текстового файла с разделителями табуляции с именами столбцов в первой строке (в том же каталоге, что и файл Excel), используя текстовый драйвер Microsoft. Я столкнулся с проблемой, когда при копировании текста и файлов Excel в новый каталог и попытке обновления данных он говорит, что не может найти текстовые файлы. К сожалению, кажется, нет никакого способа сказать Excel, что я хочу, чтобы пути к текстовым файлам были относительными, а не абсолютными. Все сводные таблицы используют одно и то же соединение данных, поэтому я решил, что не должно быть слишком сложным написать макрос, который обновит соединение данных, чтобы ссылаться на правильный текстовый файл, и иметь кнопку, связанную с макросом, которая обновит пути к файлам и обновить данные для меня.

Я не слишком знаком с VBA, и онлайн-документация кажется довольно плохой, поэтому я не смог заставить это работать - я могу создать правильный путь к файлу и обновить данные, но я не был возможность выяснить, как обновить соединение, чтобы использовать новый путь к файлу, но сохранить все свои старые настройки импорта / анализа файлов. Я также пытался записать макрос при обновлении источника данных вручную, но по какой-то причине это всегда дает мне ошибки, которые прерывают запись, так что это не помогло.

Ниже приведена строка соединения и текст команды, используемые в настоящее время соединением, но нет ничего о том, как анализировать / импортировать данные (файл с разделителями табуляции или заголовки в первом столбце и т. Д.), Поэтому я & m; не уверен, как убедиться, что соединение хранит эти данные.

Строка подключения:

DefaultDir=C:/directoryPath;Driver={Microsoft Text Driver (*.txt; *.csv)};
  DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;
  SafeTransactions=0;Threads=3;UserCommitSync=Yes;

Текст команды:

SELECT *
FROM tableName.txt tableName

Если кто-нибудь знает, как написать макрос, который будет обновлять путь в соединении с текстовым файлом, поделитесь кодом, или если вы знаете, как просто сделать путь относительно этого, это тоже будет здорово. Любая помощь будет принята с благодарностью!

EDIT:

Я немного больше возился с этим, и мне удалось изменить строки подключения, чтобы использовать новый путь. Однако, когда я иду, чтобы обновить сводную таблицу, она импортирует все данные в виде текста, вместо того, чтобы угадывать, должны ли они быть числовыми и т. Д. (Хотя он получает заголовки столбцов, по крайней мере, из первой строки текстового файла). Любые идеи о том, как сказать ему угадать типы данных (или просто сохранить старые типы данных)? Код, который я сейчас использую:

Public Sub Test()
    Dim wb As Excel.Workbook
    Dim pc As PivotCache
    Dim path As String

    Set wb = ActiveWorkbook
    path = wb.path

    For Each pc In wb.PivotCaches
        'Debug.Print pc.Connection
        pc.Connection = "ODBC;DBQ=" & path & ";DefaultDir=" & path & ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes"

    Next
End Sub