Hello!

Hope someone out there can help me. I need to update or
refresh a Pivot Table in code. Here's the scenario:

I am importing from an Access DB to Columns A through H in
Excel. That spreadsheet has a Pivot Table at K4 through
Q33.

I tried recording a macro to get the language, but it just
ignores it's own language when I put it
in "Worksheet_Activate" in Excel. I did notice, however,
that on two different occasions, it referred to the Pivot
Table as PivotTable2 and PivotTable4. I don't know where
it gets that name from. Is there any way of looking this
up? And why does it change? Why didn't it start with
PivotTable1, since it's the only Pivot Table in the
spreadsheet?

At any rate, I would rather, if possible, just put the
language in Access, but if I can't even get the correct
language for Excel . . .

Sandy

Sub TestRefresh()
Dim pvt As PivotTable
For Each pvt In ActiveSheet.PivotTables
MsgBox pvt.Name
pvt.RefreshTable
Next


End Sub

--
Regards,
Tom Ogilvy



Sandy wrote in message
...
Hello!

Hope someone out there can help me. I need to update or
refresh a Pivot Table in code. Here's the scenario:

I am importing from an Access DB to Columns A through H in
Excel. That spreadsheet has a Pivot Table at K4 through
Q33.

I tried recording a macro to get the language, but it just
ignores it's own language when I put it
in "Worksheet_Activate" in Excel. I did notice, however,
that on two different occasions, it referred to the Pivot
Table as PivotTable2 and PivotTable4. I don't know where
it gets that name from. Is there any way of looking this
up? And why does it change? Why didn't it start with
PivotTable1, since it's the only Pivot Table in the
spreadsheet?

At any rate, I would rather, if possible, just put the
language in Access, but if I can't even get the correct
language for Excel . . .

Sandy

I had the same need.
Found the only reliable way was to remove the existing
pivot table (by deleting all the rows). Then apply code
to create a new pivot table from scratch. It is always
PivotTable1.
You can also do interesting sorting and formatting in
column headers (at the very least).
-----Original Message-----
Sub TestRefresh()
Dim pvt As PivotTable
For Each pvt In ActiveSheet.PivotTables
MsgBox pvt.Name
pvt.RefreshTable
Next


End Sub

--
Regards,
Tom Ogilvy



Sandy wrote in message
...
Hello!

Hope someone out there can help me. I need to update
or
refresh a Pivot Table in code. Here's the scenario:

I am importing from an Access DB to Columns A through
H in
Excel. That spreadsheet has a Pivot Table at K4
through
Q33.

I tried recording a macro to get the language, but it
just
ignores it's own language when I put it
in "Worksheet_Activate" in Excel. I did notice,
however,
that on two different occasions, it referred to the
Pivot
Table as PivotTable2 and PivotTable4. I don't know
where
it gets that name from. Is there any way of looking
this
up? And why does it change? Why didn't it start with
PivotTable1, since it's the only Pivot Table in the
spreadsheet?

At any rate, I would rather, if possible, just put the
language in Access, but if I can't even get the correct
language for Excel . . .

Sandy


.

Thanks!
-----Original Message-----
I had the same need.
Found the only reliable way was to remove the existing
pivot table (by deleting all the rows). Then apply code
to create a new pivot table from scratch. It is always
PivotTable1.
You can also do interesting sorting and formatting in
column headers (at the very least).
-----Original Message-----
Sub TestRefresh()
Dim pvt As PivotTable
For Each pvt In ActiveSheet.PivotTables
MsgBox pvt.Name
pvt.RefreshTable
Next


End Sub

--
Regards,
Tom Ogilvy



Sandy wrote in message
...
Hello!

Hope someone out there can help me. I need to update
or
refresh a Pivot Table in code. Here's the scenario:

I am importing from an Access DB to Columns A through
H in
Excel. That spreadsheet has a Pivot Table at K4
through
Q33.

I tried recording a macro to get the language, but it
just
ignores it's own language when I put it
in "Worksheet_Activate" in Excel. I did notice,
however,
that on two different occasions, it referred to the
Pivot
Table as PivotTable2 and PivotTable4. I don't know
where
it gets that name from. Is there any way of looking
this
up? And why does it change? Why didn't it start with
PivotTable1, since it's the only Pivot Table in the
spreadsheet?

At any rate, I would rather, if possible, just put the
language in Access, but if I can't even get the correct
language for Excel . . .

Sandy


.

.

Thanks, Myrna!
-----Original Message-----
On Tue, 2 Sep 2003 18:57:59 -0700, "Sandy"
wrote:

I did notice, however,
that on two different occasions, it referred to the
Pivot
Table as PivotTable2 and PivotTable4. I don't know
where
it gets that name from.

Each time you create a new table, it's assigned the next
name in sequence, even though you've
deleted the prior table(s).

.

Thanks, Tom!
-----Original Message-----
Sub TestRefresh()
Dim pvt As PivotTable
For Each pvt In ActiveSheet.PivotTables
MsgBox pvt.Name
pvt.RefreshTable
Next


End Sub

--
Regards,
Tom Ogilvy



Sandy wrote in message
...
Hello!

Hope someone out there can help me. I need to update or
refresh a Pivot Table in code. Here's the scenario:

I am importing from an Access DB to Columns A through H
in
Excel. That spreadsheet has a Pivot Table at K4 through
Q33.

I tried recording a macro to get the language, but it
just
ignores it's own language when I put it
in "Worksheet_Activate" in Excel. I did notice,
however,
that on two different occasions, it referred to the
Pivot
Table as PivotTable2 and PivotTable4. I don't know
where
it gets that name from. Is there any way of looking
this
up? And why does it change? Why didn't it start with
PivotTable1, since it's the only Pivot Table in the
spreadsheet?

At any rate, I would rather, if possible, just put the
language in Access, but if I can't even get the correct
language for Excel . . .

Sandy


.

If you want to change the name of the pivot table, you can right click
on it, and then choose options. A screen will appear, with a field
called name, that you can change.

Sandy, if you want to update your pivot table whenever you change your
data, put the following code at worksheet_change:

activesheet.pivotables("PivotTable1").refreshtable

where PivotTable1 is the name you chose to your pivot table.


"Sandy" wrote in message ...
Thanks, Tom!
-----Original Message-----
Sub TestRefresh()
Dim pvt As PivotTable
For Each pvt In ActiveSheet.PivotTables
MsgBox pvt.Name
pvt.RefreshTable
Next


End Sub

--
Regards,
Tom Ogilvy



Sandy wrote in message
...
Hello!

Hope someone out there can help me. I need to update or
refresh a Pivot Table in code. Here's the scenario:

I am importing from an Access DB to Columns A through H
in
Excel. That spreadsheet has a Pivot Table at K4 through
Q33.

I tried recording a macro to get the language, but it
just
ignores it's own language when I put it
in "Worksheet_Activate" in Excel. I did notice,
however,
that on two different occasions, it referred to the
Pivot
Table as PivotTable2 and PivotTable4. I don't know
where
it gets that name from. Is there any way of looking
this
up? And why does it change? Why didn't it start with
PivotTable1, since it's the only Pivot Table in the
spreadsheet?

At any rate, I would rather, if possible, just put the
language in Access, but if I can't even get the correct
language for Excel . . .

Sandy


.