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

intsoccersuperstar

New Member
Joined
Feb 3, 2013
Messages
30
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!

VBA Code:
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

Well-known Member
Joined
Jan 11, 2009
Messages
1,170
> 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

New Member
Joined
Feb 3, 2013
Messages
30
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:

VBA Code:
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
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

New Member
Joined
Feb 3, 2013
Messages
30
Thanks, Micron. I set everything to Nothing:

VBA Code:
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:

Code:
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
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

New Member
Joined
Feb 3, 2013
Messages
30
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.

VBA Code:
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
"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??
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
677
"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'?
 

Forum statistics

Threads
1,077,865
Messages
5,336,849
Members
399,106
Latest member
anoufal

Some videos you may like

This Week's Hot Topics

Top