Eu estou trabalhando em um projeto no MS Excel onde eu tenho várias tabelas dinâmicas que leem dados de um arquivo de texto delimitado por tabulação com nomes de coluna na primeira linha (no mesmo diretório que o arquivo do Excel) usando o Microsoft Text Driver. Eu me deparo com um problema em que quando copio o texto e os arquivos do Excel para um novo diretório e tento atualizar os dados, ele diz que não consegue encontrar os arquivos de texto. Infelizmente, parece não haver maneira de dizer ao Excel que eu quero que os caminhos para os arquivos de texto sejam relativos, não absolutos. Todas as tabelas dinâmicas usam a mesma conexão de dados, então imaginei que não seria muito difícil criar uma macro que atualizasse a conexão de dados para se referir ao arquivo de texto correto e ter um botão vinculado à macro que atualizaria o arquivo. caminhos de arquivo e atualize os dados para mim.

Eu não sou muito familiarizado com o VBA e a documentação on-line parece ser muito ruim, então eu não consegui fazer isso funcionar - eu posso criar o caminho correto do arquivo e atualizar os dados, mas não fui capaz de descobrir como atualizar a conexão para usar o novo caminho de arquivo, mas manter todas as suas antigas configurações de análise de importação / arquivo. Também tentei gravar uma macro enquanto atualizava manualmente a fonte de dados, mas, por algum motivo, isso sempre me causa erros que interrompem a gravação, o que não ajudou.

O seguinte é a string de conexão e o texto de comando atualmente usados ​​pela conexão, mas não há nada sobre como analisar / importar os dados (o arquivo sendo delimitado por tabulação ou com cabeçalhos na primeira coluna, etc.), então não tenho certeza como ter certeza de que a conexão mantém esses dados.

Seqüência de conexão:

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;

Texto de Comando:

SELECT *
FROM tableName.txt tableName

Se alguém souber como escrever uma macro que atualizará o caminho na conexão com o arquivo de texto, compartilhe o código ou, se você souber como tornar o caminho relativo, ótimo também. Qualquer ajuda seria muito apreciada!

EDITAR:

Eu estive mexendo com isso um pouco mais e consegui mudar as strings de conexão para usar o novo caminho. No entanto, quando vou atualizar a tabela dinâmica, ela importa todos os dados como texto, em vez de adivinhar se deve ser numérica, etc. (embora obtenha pelo menos os cabeçalhos de coluna da primeira linha do arquivo de texto). Alguma idéia de como dizê-lo para adivinhar os tipos de dados (ou apenas manter os tipos de dados antigos)? O código que estou usando agora é:

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