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

connection, csv, date, excel, pivot

Solution

Yes, that is an annoying constraint for pivot tables.

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

- Created a .csv file with headings Name, Birthdate, Sign. Closed the file.

- Imported the .csv into a new pivot table PivotTable1 in a new workbook.

- 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.

Problem

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.

Original source