Excel 2010 - Change data source of pivot table from external to worksheet within workbook

Question

I have been having problems with a pivot table that uses an external .csv file as its source, particularly with dates in the csv file being treated as text. So, I decided to import the csv file into another worksheet and then force the dates to be recognised as such by reformatting the relevant columns.

The problem I now have is changing the existing pivot table to use the data in the new worksheet instead of an external source. If I click on "Change Data Source" button, the "Select a table or range" option is greyed out. If I continue with external data source and click the Choose Connection button and select the new worksheet I get a pop up that says "The type of connection selected cannot be used to create a PivotTable".

I also tried converting the csv file to xlsx but had a similar problem trying to get the existing pivot table to use it instead of the csv as its source.

Answer 1

Yes, that is an annoying constraint for pivot tables.

Steps to recreate the and change the datasource from .csv to .xls file:

1. Created a .csv file with headings Name, Birthdate, Sign. Closed the file.
2. Imported the .csv into a new pivot table PivotTable1 in a new workbook.
3. Saved the orignal .csv into a new workbook c:\path\to\datasource.xls, naming the worksheet Data and making sure the upper left cell of the data range was in cell A1.

Then I added the following VBA macro and executed it:

Sub getExcelData()
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .Connection = Array( _
            Array("ODBC;DSN=Excel Files;"), _
            Array("DBQ=c:\path\to\datasource.xls;"), _
            Array("DefaultDir=c:\path\to;DriverId=790;MaxBufferSize=2048;PageTimeout=5;") _
      )
        .CommandText = _
            "SELECT `Data$`.Birthdate, `Data$`.Name, `Data$`.Sign" & vbCrLf & _
            "FROM `c:\path\to\datasource.xls`.`Data$` `Data$`"
    End With
End Sub

The .CommandText syntax for this driver is horribly picky and not at all smart.

YMMV with this code in 2010 or later and/or with different drivers. Date and numeric data are notoriously ill-handled even in later versions of Excel data drivers. I generally make sure that every cell in a numeric or date field has a valid value, or at the very least every cell in the first row of data, before importing.