Estoy trabajando en un proyecto en MS Excel donde tengo varias tablas dinámicas que leen datos de un archivo de texto delimitado por tabulaciones con nombres de columna en la primera fila (en el mismo directorio que el archivo de Excel) usando el controlador de texto de Microsoft. Me he encontrado con un problema en el que cuando copio el texto y los archivos de Excel en un nuevo directorio e intento actualizar los datos, dice que no puede encontrar los archivos de texto. Desafortunadamente, parece que no hay manera de decirle a Excel que quiero que las rutas a los archivos de texto sean relativas, no absolutas. Todas las tablas dinámicas usan la misma conexión de datos, así que pensé que no debería ser demasiado difícil escribir una macro que actualizaría la conexión de datos para referirse al archivo de texto correcto y tener un botón vinculado a la macro que actualizaría la conexión de datos. archivo de rutas y actualizar los datos para mí.

No estoy demasiado familiarizado con VBA y la documentación en línea parece ser bastante mala, por lo que no he podido hacer que esto funcione. Puedo crear la ruta de archivo correcta y actualizar los datos, pero no he estado capaz de averiguar cómo actualizar la conexión para usar la nueva ruta de archivo, pero conservar todas sus configuraciones antiguas de importación / análisis de archivos. También he intentado grabar una macro mientras actualizaba manualmente la fuente de datos, pero por alguna razón eso siempre me da errores que interrumpen la grabación, por lo que no ha ayudado.

La siguiente es la cadena de conexión y el texto de comando actualmente utilizado por la conexión, pero no hay nada sobre cómo analizar / importar los datos (el archivo está delimitado por tabuladores o tiene encabezados en la primera columna, etc.), así que no estoy seguro Cómo asegurarse de que la conexión mantiene esos datos.

Cadena de conexión:

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

Si alguien sabe cómo escribir una macro que actualizará la ruta en la conexión al archivo de texto, comparta el código, o si sabe cómo hacer que la ruta sea relativa también sería genial. Cualquier ayuda sería muy apreciada!

EDITAR:

He estado jugando un poco más con él y pude cambiar las cadenas de conexión para usar la nueva ruta. Sin embargo, cuando voy a actualizar la tabla dinámica, importa todos los datos como texto en lugar de adivinar si debería ser numérico, etc. (aunque al menos obtiene los encabezados de las columnas de la primera línea del archivo de texto). ¿Alguna idea sobre cómo decirle para adivinar los tipos de datos (o simplemente mantener los tipos de datos anteriores)? El código que estoy usando ahora es:

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