While using VBA script to refresh Excel Pivot Tables from Access I am getting
error
message 1004 if the command button is pressed several times. I understand
that this is because the local variables are holding a reference to the Pivot
Tables. (I have referred to "Excel automation fails second time code runs -
http://support.microsoft.com/kb/178510/en-us")

<----Quoting /kb/178510/

Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello"
xlBook.Saved = True
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

*------Quoting /kb/178510/

8. Stop the project and change the following line:

xlSheet.Range(Cells(1,1),Cells(10,2)).Value = "Hello"

to: xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(10, 2)).Value = "Hello"
----

The example there uses a worksheet that is built from scratch. I am having
trouble
getting the right syntax for an existing Pivot Table within an existing
Worksheet.

Thanks in advance.