Geek asks and answers

Excel 2010 - Pivot table data source change from external to worksheet in workbook

I am having problems with a pivot table that uses an external .csv file as the source, especially with dates in the csv file being processed as text. So, I decided to import the csv file into another worksheet, and then force the dates to be recognized as such by reformatting the corresponding columns.

Now the problem is to modify the existing pivot table to use data on a new sheet instead of an external source. If I click the Change Data Source button, the Select Table or Range option will be grayed out. If I continue with the external data source and click the "Select Connection" button and select a new worksheet, I will get a pop-up message that says: "The type of the selected connection cannot be used to create a pivot table."

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

+3
source share
5 answers

, .

.csv .xls :

  • CSV Name, Birthdate, Sign. .
  • CSV PivotTable1 .
  • orignal.csv c:\path\to\datasource.xls, , A1.

VBA :

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

.CommandText .

YMMV 2010 / . , , Excel. , , , .

+3

, , , . , VBA . , .

  • ( ).
  • .
  • "? selection.pivottable.cacheindex" enter.
  • .
  • Excel , .
  • "selection.pivottable.cacheindex = x", x - , 4.
  • 6 , .
  • , 1.

, , , , .

+2

, , , , Visual Basic Macro Ctrl + G, . mendosi. , , , . , VBA . , .

( ). . "? Selection.pivottable.cacheindex" enter. . Excel , . "selection.pivottable.cacheindex = x", x - , 4. 6 , . , 1. , , , , .

+1

, . , , , .

Excel 2010:

  1. ....
  2. ...
  3. ...
  4. 3 ( )
  5. , .
  6. 3
  7. ""
  8. Microsoft Excel List Database
  9. "",
  10. .

, ,

Thanks a lot, Ines. (I don’t know why someone voted for you).

0
source

Have you tried opening the rotation wizard and returning to step 1 (where are the data that you want to analyze)? In excel 2007, the shortcut to open the wizard is alt + d + p (or you can hover a button on it in the quick access toolbar). Hope this helps.

-1
source

More articles:


All Articles