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

 

[james_lankford]

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

don't know if this is the problem, but I always prefix my objects with the object they belong to

so ActiveSheet.Name would be wb.ActiveSheet.Name

and Range("A1:D249").Address(ReferenceStyle:=xlR1C1)
would be wb.ActiveSheet.Range("A1:D249").Address(ReferenceStyle:=xlR1C1)

and are you sure your range values are correct
Range("A1:D249")

 

[intsoccersuperstar]

Thanks James,

Yup the range is correct. It works perfectly the first time I run it, but on subsequent runs I get the 1004 error.

When I run it with your changes I get a

"Run-time error '438':
Object doesn't support this property or method"

Error when I try to run it with your changes. The debugger highlights the line you asked me to edit:

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

So I think it's something there? What is "Name", exactly? If that's the name of the tab, does it have to be "qryManagerQuery", as was established early in the DoCmd.TransferSpreadsheet line?

 

[Micron]

Excel automation with pivot tables isn't my strong suit, but...
you only SET xl To Nothing - all other SET objects should get the same treatment. Memory ought to be released when the procedure finishes, or at least the re - instantiation of the object next time around probably shouldn't create a conflict but why take the chance? I have read a whole lot of commentary on whether or not one needs to set objects to Nothing with the end result being that whoever is right, it doesn't matter if I do it anyway.

Then I would put a break on the code and step through both the first and subsequent calls and see if a) there is any deviation in the flow, and b) if all variables are as expected. I would also check Task Manager to ensure there is no instance of Excel remaining open after the code has run. If so, it could be retaining the pivot table name value. This is all predicated on the assumption that it will work if you close the db and try it again with the same name. If not, then perhaps you need a way to over-write - or remove the pivot spec first, then add it again.

 

[intsoccersuperstar]

Thanks, Micron. I set everything to Nothing:

wb.Save
xl.quit
Set xl = Nothing
Set wb = Nothing
Set ws = Nothing
Set sht = Nothing
Set pvtCache = Nothing
Set pvt = Nothing

But now I get a Run-Time error '91': Object variable or With block variable not set on the following line:

Do While ActiveSheet.Range("A" & i).Value <> ""

I will take your suggestion and step through. Additionally, you were right - there was an instance of Excel shown running in the Task Manager even though it was not "open" from the task bar's point of view (i.e., I can see the application icon in the task bar with no indication that Excel was open.

 

[Micron]

Not really possible to comment much on the latest error as your original code doesn't contain such a loop. Seems like you've made other changes as well so you ought to post the entire revised code. Just so anyone knows, I don't do much of this, so feel free to interject. When I need to use automation for Excel, I often go here. Also, this line looks suspect to me
Set sht = Sheets.Add

There is no reference to the parent object (the workbook). If this was inside a With block, I could see .Sheets.Add, but as written I would expect exactly the error you're getting - but on that line. Perhaps your code changes are such that it's now not getting there yet.
I notice that you don't have Option Explicit. IMHO, if this isn't turned on, you deserve the trouble you get!

 

[intsoccersuperstar]

Ah yes, Option Explicit...the guy training me on this has mentioned several times to me to always use it. He's out right now but I'm sure he would be very disappointed in me if he knew I forgot to use it this time!

Turning it on seems to require that I define strInputFile, so I did. Now, I am getting a Compile Error: Object Not Defined at Set sht = Sheets.Add.

Here is the code as it presently is. I tried to put wb. before sht but that gave an error, and I tried to define sht as a String as well but I got an invalid qualifier error so I removed that.

Option Compare Database
Option Explicit

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

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryManagerQuery", "R:\Reporting and Analysis\RA Dev\RA Dev for Workflow Dashboard\Manager Query.xlsx", True

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

Dim myRange As Excel.Range

Set xl = CreateObject("Excel.Application")
strInputFile = "R:\Reporting and Analysis\RA Dev\RA Dev for Workflow Dashboard\Manager Query.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
Dim i As Integer
Dim pf As String
Dim pf_Name As String

pf = "Number of Records"
pf_Name = "Sum of Number of Records"

i = 2
Do While ws.Range("A" & i).Value <> ""
i = i + 1
Loop

SrcData = ws.Name & "!" & Range("A1:D" & i - 1).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")
    
pvt.PivotFields("1st Level Complete Date").Orientation = xlColumnField
pvt.PivotFields("1st Level Analyst").Orientation = xlRowField
pvt.AddDataField pvt.PivotFields("Number of Records"), pf_Name, xlSum


wb.Save
wb.Close
xl.quit
Set xl = Nothing
Set wb = Nothing
Set ws = Nothing
Set sht = Nothing
Set pvtCache = Nothing
Set pvt = Nothing

MsgBox "Export complete.  Files located at R:\Reporting and Analysis\RA Dev\RA Dev for Workflow Dashboard", vbInformation, "Export Complete"

End Sub

 

[Micron]

"I tried to put wb. before sht but that gave an error, " uh, no - wb is the parent to Sheets so wb.Sheets.Add.
I wonder whatever happened to the quotation function (quote tags) that were in the older version of the forum? I couldn't cite your comment in a quote block as we used to be able to??

 

[intsoccersuperstar]

So like this?

Set sht = wb.Sheets.Add

That still gives a Compile error.

Sorry, I am very new at this. ?

 

[welshgasman]

"I tried to put wb. before sht but that gave an error, " uh, no - wb is the parent to Sheets so wb.Sheets.Add.
I wonder whatever happened to the quotation function (quote tags) that were in the older version of the forum? I couldn't cite your comment in a quote block as we used to be able to??

The reply link (bottom right of message) appears to do it automatically?
If you hover over it, it shows 'Reply qioting this message'?