Using VBA to create a PivotTable: Code runs once successfully, then gives Run-Time 1004 error

[intsoccersuperstar]

First off, this is in Access, but I think the issue is more of a VBA one than an Access one so I'm posting it here. I will move it if need be.

When I first open access and run this, it works fine and generates a tab in the output file with an empty pivot table. Any subsequent runs generate a Run-time error '1004', the PivotTable field name is not valid.

If I change the name of the output file, it will run again but not generate the new tab with an empty pivot table.

Any help would be greatly appreciated. Thanks!

Option Compare Database

Private Sub cmdRunManagerQuery_Click()
Dim mySQL As String
Dim Temp As Variant

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryManagerQuery", "C:\Storage\Manager Query Mod.xlsx", True

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim lRowCount As Long

Dim myRange As Excel.Range

Set xl = CreateObject("Excel.Application")
strInputFile = "C:\Storage\Manager Query Mod.xlsx"
Set wb = xl.Workbooks.Open(strInputFile)
Set ws = wb.Sheets("qryManagerQuery")

'Test that I can edit the file
'ws.Range("C250") = "=SUM(C2:C249)"

Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String

SrcData = ActiveSheet.Name & "!" & Range("A1:D249").Address(ReferenceStyle:=xlR1C1)

Set sht = Sheets.Add

StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)
    
Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")


wb.Save
xl.quit
Set xl = Nothing

MsgBox "Export complete.  Files located at C:\Storage", vbInformation, "Export Complete"

End Sub

 

[Micron]

Did you visit Ken Snell's site to see if your issue is covered there? As I mentioned, this isn't my strong suit. Now I'm thinking you can't use SET and invoke a method (Add) on the same line, so maybe it's wb.Sheets.Add. Then you ought to be able to work with the object. I suspect that researching Access vba automation Excel sheets.add will give you 1 or 2 million hits.
BTW - just saying you got an error isn't of much help.