Edit PivotTable SQL datasource
 

Hi, anyone please help....

I have an existing excel file with pivot table referencing to an SQL server
database.

Since the excel file was created by another person no longer in the office,
I do not know exactly what specific sql statement or statements it is linked
to.

Now, the current data contains only items from until a last year. I want to
modify that to include data for the current year, i already modified all sql
statements in the queries folder containing the limited year range, but it
doesnt give any effects in the excel file.

Is there a way to know which sql statement is linked or used as a data
source of an existing excel pivot table? If then, how to I "refresh" the data
in the current excel file to show the modifications i made in the sql
statement? Is the "enable automatic refresh" enough?

Thanks very much for any help...:)

Edit PivotTable SQL datasource
 

Hi Therezee,

Is there a way to know which sql statement is linked or used as a data
source of an existing excel pivot table? If then, how to I "refresh" the data
in the current excel file to show the modifications i made in the sql
statement? Is the "enable automatic refresh" enough?

You could use this code to show and modify the existing SQL and connection
information:

Sub ChangePivotSource()
Dim sStr As String
sStr = InputBox("Give new SQL command", "Pivot cache",
ActiveWorkbook.PivotCaches(1).CommandText)
If sStr < "" Then
ActiveWorkbook.PivotCaches(1).CommandText = sStr
End If
sStr = InputBox("Give new Connection string", "Pivot cache",
ActiveWorkbook.PivotCaches(1).Connection)
If sStr < "" Then
ActiveWorkbook.PivotCaches(1).Connection = sStr
End If
End Sub

Apart from that, you need to refresh the pivot table manually by selecting a
cell within the PT and choosing Data, refresh.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Edit PivotTable SQL datasource
 

hi there!

this is sooo helpful..thanks so much! :)

"Jan Karel Pieterse" wrote:

Hi Therezee,

Is there a way to know which sql statement is linked or used as a data
source of an existing excel pivot table? If then, how to I "refresh" the data
in the current excel file to show the modifications i made in the sql
statement? Is the "enable automatic refresh" enough?

You could use this code to show and modify the existing SQL and connection
information:

Sub ChangePivotSource()
Dim sStr As String
sStr = InputBox("Give new SQL command", "Pivot cache",
ActiveWorkbook.PivotCaches(1).CommandText)
If sStr < "" Then
ActiveWorkbook.PivotCaches(1).CommandText = sStr
End If
sStr = InputBox("Give new Connection string", "Pivot cache",
ActiveWorkbook.PivotCaches(1).Connection)
If sStr < "" Then
ActiveWorkbook.PivotCaches(1).Connection = sStr
End If
End Sub

Apart from that, you need to refresh the pivot table manually by selecting a
cell within the PT and choosing Data, refresh.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com