Pivotcache sourcedata change 1004 error

[magnet]

I would really appreciate any help on this. Have tried various ways to update the pivotcache source data and consistently get the ]1004 Application or object-defined error' when it gets to the red code where it tries to change the sourcedata.

The various methods I have tried are remmed out. I am running this from MS Access. Thank you for any help.

[SIZE=1]Sub TestPivot()[/SIZE]
[SIZE=1]On Error GoTo Err_FormatSpreadsheets_Click[/SIZE]
[SIZE=1]Dim db As Database
Dim xl As Excel.Application
Dim xlw As Excel.Workbook
Dim xlsh As Excel.Worksheet
Dim xlshp As Excel.Worksheet
Dim filenm As String
Dim rng As Range
Dim pt As PivotTable
Dim ptc As PivotCache
Dim straddr As String[/SIZE]
[SIZE=1][/SIZE] 
[SIZE=1]Set db = CodeDb[/SIZE]
[SIZE=1]Set xl = CreateObject("Excel.Application")
Set xlw = xl.Workbooks.Open("C:\WORKINGFOLDERS\Office\AppTest\EasternPiv.xls")[/SIZE]
[SIZE=1]
xl.Visible = True[/SIZE]
[SIZE=1]Set xlsh = xlw.Worksheets("ProvList")[/SIZE]
[SIZE=1][/SIZE] 
[SIZE=1]With xlsh
    .Select
    .Cells.Select[/SIZE]
[SIZE=1]Set rng = xlsh.Range(.Cells(1, 1), xlsh.UsedRange.Cells.SpecialCells(xlLastCell))
[COLOR=seagreen][/COLOR][/SIZE]
[SIZE=1][COLOR=seagreen]'rng.Name = "PivRangeName"
'MsgBox rng.Address
'MsgBox rng.Address(1, 1, xlR1C1)
'straddr = "'" & xlsh.Name & "'!" & rng.Address(1, 1, xlR1C1)

[/COLOR][/SIZE][SIZE=1]straddr = xlsh.Name & "!" & rng.Address(1, 1, xlR1C1)

[/SIZE][SIZE=1][COLOR=seagreen]'MsgBox straddr[/COLOR][/SIZE]
[SIZE=1]    Set xlshp = xlw.Worksheets("ProvPT")
    
 For Each pt In xlshp.PivotTables
    [COLOR=seagreen]'Sheets(1).PivotTables(1).PivotCache.SourceData = "'" & Sheets(2).Name & "'!" & Range("A5:T40000").Address
    'Sheets(1).PivotTables(1).PivotCache.SourceData = Sheets(2).Name & "!" & Range("A5:T40000").Address(ReferenceStyle:=xlR1C1)
    
[/COLOR]    Set ptc = pt.PivotCache
    
   [COLOR=seagreen] 'ptc.SourceData = Worksheets("ProvList")!Range("A1:C5000").Address(ReferenceStyle:=xlR1C1)
    'ptc.SourceData = Worksheets(3).Name & "!" & Range("A1:C5000").Address(ReferenceStyle:=xlR1C1)
    'ptc.SourceData = xlsh.UsedRange.Address(1, 1, xlR1C1)
    'ptc.SourceData = "'ProvList'!" & Range("$A:$C").Address
    'ptc.SourceData = rng.Address(1, 1, xlR1C1)
    'ptc.SourceData = rng.CurrentRegion.Address
    
     'xlshp.PivotTables(1).PivotCache.SourceData = xlsh.UsedRange.Address
     'xlshp.PivotTables(1).PivotCache.SourceData = "'ProvList'!" & Range("$A:$C").Address
     'xlshp.PivotTables(1).PivotCache.SourceData = xlsh.UsedRange.Address(1, 1, xlR1C1)
     'xlshp.PivotTables(1).PivotCache.SourceData = xlsh.UsedRange.Address(1, 1, xlR1C1)
     'xlshp.PivotTables(1).PivotCache.SourceData = xlsh.UsedRange.Address(True, True, xlR1C1)
     'xlshp.PivotTables(1).PivotCache.SourceData = xlw.Worksheets("ProvList") & "!" & Range("A1:C5000").Address(1, 1, xlR1C1)
     'xlshp.PivotTables(1).PivotCache.SourceData = xlw.Worksheets("ProvList") & "!" & Range("A1:C5000").Address(ReferenceStyle:=xlR1C1)[/COLOR][/SIZE]
[SIZE=1][COLOR=seagreen]    'MsgBox "The data source connection is: " & xlw.PivotCaches.Item(1).SourceDataFile
    '    Set ptc = xlw.PivotCaches.Item(1)
    '    MsgBox "The data source connection is: " & ptc.SourceDataFile[/COLOR][/SIZE]
[SIZE=1][COLOR=seagreen][/COLOR][/SIZE] 
[SIZE=1][COLOR=seagreen][COLOR=#008000] 'xlshp.PivotTableWizard SourceType:=xlPivotTable, SourceData:=straddr[/COLOR]

[/COLOR]    
  [COLOR=red] ptc.SourceData = straddr
[/COLOR]    
[COLOR=green]  [/COLOR][/SIZE]
[SIZE=1]    pt.RefreshTable
    pt.Update
    
     
 Next
   
End With[/SIZE]
[SIZE=1]xlw.Save[/SIZE]
[SIZE=1]Exit_FormatSpreadsheets_Click:
Set xl = Nothing
xlw.Close
Set xlw = Nothing
Set xlsh = Nothing
Set xlshp = Nothing[/SIZE]
[SIZE=1]    Exit Sub[/SIZE]
[SIZE=1]Err_FormatSpreadsheets_Click:
    MsgBox Err.Description
    Resume Exit_FormatSpreadsheets_Click[/SIZE]
[SIZE=1]End Sub[/SIZE]
[SIZE=1]

[/SIZE]