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