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

It's been a while since you asked, but I had this same problem and just discovered a solution. Basically, you can use VBA to change pivot tables to a new pivot cache. If the new cache uses the same fields then your layout will be preserved.

1. Create a new pivot table based on the new data source (it can just be empty).
2. Click in the new pivot.
3. In the immediate window type "? selection.pivottable.cacheindex" and press enter.
4. Note the index number of the pivot cache.
5. In Excel click in the old pivot which you want to change.
6. In the immediate window type "selection.pivottable.cacheindex = x" where x is the index number you noted in step 4.
7. Repeat step 6 for every pivot table which you want to use the new source.
8. Remove the table you created in step 1.

There may to change all tables from one cache to another in a single step, but it's probably not something you need to do often.

Answer 2

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.