Pracuję nad projektem w MS Excel, gdzie mam kilka tabel przestawnych, które odczytują dane z pliku tekstowego rozdzielanego tabulatorami z nazwami kolumn w pierwszym wierszu (w tym samym katalogu co plik Excel) za pomocą sterownika tekstowego Microsoft. Wystąpił problem, gdy po skopiowaniu plików tekstowych i Excela do nowego katalogu i próbie odświeżenia danych mówi się, że nie może znaleźć plików tekstowych. Niestety wydaje się, że nie ma sposobu, aby powiedzieć Excelowi, że chcę, aby ścieżki do plików tekstowych były względne, a nie bezwzględne. Wszystkie tabele przestawne używają tego samego połączenia danych, więc pomyślałem, że pisanie makra, które zaktualizowałoby połączenie danych, aby odwoływało się do poprawnego pliku tekstowego i nie miało przycisku powiązanego z makrem, który zaktualizowałby ścieżki plików i odśwież dane dla mnie.

Nie jestem zbyt zaznajomiony z VBA, a dokumentacja online wydaje się być całkiem zła, więc nie udało mi się tego osiągnąć - mogę utworzyć poprawną ścieżkę pliku i odświeżyć dane, ale nie byłem potrafi dowiedzieć się, jak zaktualizować połączenie, aby użyć nowej ścieżki do pliku, ale zachować wszystkie stare ustawienia importowania / analizowania plików. Próbowałem również nagrać makro podczas ręcznego aktualizowania źródła danych, ale z jakiegoś powodu zawsze powoduje to błędy, które przerywają nagrywanie, więc to nie pomogło.

Poniżej znajduje się ciąg połączenia i tekst polecenia aktualnie używane przez połączenie, ale nie ma nic o tym, jak analizować / importować dane (plik jest rozdzielany tabulatorami lub ma nagłówki w pierwszej kolumnie itp.), Więc nie jestem pewien jak upewnić się, że połączenie zachowuje te dane.

Ciąg połączenia:

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;

Tekst polecenia:

SELECT *
FROM tableName.txt tableName

Jeśli ktoś wie, jak napisać makro, które zaktualizuje ścieżkę w połączeniu z plikiem tekstowym, proszę udostępnić kod, lub jeśli wiesz, jak po prostu uczynić ścieżkę względną, która również byłaby świetna. Każda pomoc byłaby bardzo mile widziana!

EDYTOWAĆ:

Trochę więcej się z tym bawiłem i mogłem zmienić ciągi połączeń, aby użyć nowej ścieżki. Jednak, gdy idę do odświeżania tabeli przestawnej, importuje wszystkie dane jako tekst, zamiast zgadywać, czy powinny być numeryczne, itp. (Chociaż przynajmniej pobiera nagłówki kolumn z pierwszego wiersza pliku tekstowego). Jakieś pomysły na powiedzenie mu, aby odgadnąć typy danych (lub po prostu zachować stare typy danych)? Kod, którego używam teraz, to:

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