title: Changing connection string in Excel messes up column data types
body: <p>This is related to my <a href="https://stackoverflow.com/questions/12117408/changing-pivot-table-external-data-source-path-with-excel-macro">previous question</a>. I'm trying, in Excel, to update external data connections to text files (used by several pivot tables) to point to the correct data sources so data can be refreshed when the Excel workbook and text files are copied to a different directory/computer. (It should act like the paths to the text files are relative.) </p>
  
  <p>The data sources are tab delimited text files with the headers in the first columns. I have code that can change the path in the ODBC connection string, but when I try to refresh the data Excel imports it all as text, not keeping some of the columns numeric as they originally were.</p>
  
  <pre><code>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=" &amp; path &amp; ";DefaultDir=" &amp; path &amp; ";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
  </code></pre>
  
  <p>I tried changing <code>MaxScanRows</code> to zero, which I read somewhere would have Excel scan all the rows to guess the data type, but that didn't seem to help.</p>
  
  <p>The original connection info is:</p>
  
  <p>Connection String:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Command Text:</p>
  
  <pre><code>SELECT *
  FROM tableName.txt tableName
  </code></pre>
  
  <p>When I created the connection with the PivotTable wizard (External Data -> MS text driver) I set up all the import/parsing information (tab delimited, column headers in first row, guess data types, etc). </p>
  
  <p>Is there any way to (1) tell Excel to look through the rows and figure out the data types, (2) manually code in the data types for each column, (3) keep the same data types  that were originally used, or (4) after the data is refreshed go through the pivot tables in the macro and convert all the numbers to numeric values instead of text? Obviously (1) or (3) seem like they would be easiest and make the most sense, but I'm willing to try other options.</p>