I have the following statements in a script to update an existing Pivot
Table. They work fine as long as the Access database that is used as the
source is in the same location/path as when the Pivot Table was originally
created. My problem is that I am developing this for another user and the
location/path of the Access database will be different than that for my work.

I was under the impression that if I included the statement prior to the
"Refresh" that it would cause the refresh to point to a different location
and/or name of the Access dataabase. This does not seem to be working.

Is there a way to change the location and/or name of the external file that
the pivot table is pulling the data from? Or am I missing something obvious?

Thanks.

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array(Array(ConnText1), Array(ConnText2))
.CommandType = xlCmdSql
.CommandText = Array(CommText1, Commtext2)
End With
ActiveSheet.PivotTables("QUARTERVOLUME").PivotCach e.Refresh

I think I may have found the answer. My solution appears to work, but I have
not tested this with another user (yet). We'll see.

Here is the new refresh statement that seems to work.

With ActiveSheet.PivotTables("QUARTERVOLUME").PivotCach e
.Connection = Array(Array(ConnText1), Array(ConnText2))
.CommandType = xlCmdSql
.CommandText = Array(CommText1, Commtext2)
.Refresh
End With



"Martin" wrote:

I have the following statements in a script to update an existing Pivot
Table. They work fine as long as the Access database that is used as the
source is in the same location/path as when the Pivot Table was originally
created. My problem is that I am developing this for another user and the
location/path of the Access database will be different than that for my work.

I was under the impression that if I included the statement prior to the
"Refresh" that it would cause the refresh to point to a different location
and/or name of the Access dataabase. This does not seem to be working.

Is there a way to change the location and/or name of the external file that
the pivot table is pulling the data from? Or am I missing something obvious?

Thanks.

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array(Array(ConnText1), Array(ConnText2))
.CommandType = xlCmdSql
.CommandText = Array(CommText1, Commtext2)
End With
ActiveSheet.PivotTables("QUARTERVOLUME").PivotCach e.Refresh