?
Solved

Pivot table macro gives error subscript out of range

Posted on 2010-08-25
6
Medium Priority
?
775 Views
Last Modified: 2012-05-10
When I call this macro from access I get the error subscript out of range. When I run this in excel it runs fine It stops at this row     Set WSD = Worksheets("tblEXPORTMETRICS")


Sub CreatePivot()
   
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Set WSD = Worksheets("tblEXPORTMETRICS")
   
    ' Delete any prior pivot tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT
   
    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(65536, 1).End(xlUp).Row
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 12)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
       
    Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets.Add.Cells(5, 1), TableName:="PivotTableStates")
    PT.ManualUpdate = True
    ' Set up the row  & column fields
    PT.AddFields RowFields:=Array("state"), ColumnFields:="CHKVCHRCD"

       
    ' Set up the data fields
    With PT.PivotFields("CHECKVIEWCHK.")
        .Orientation = xlDataField
        .Function = xlCount
        .Position = 1
    End With
   
    ' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
   
    ' Added for the project files
    WSD.Select
0
Comment
Question by:Serena2345
  • 3
  • 3
6 Comments
 

Author Comment

by:Serena2345
ID: 33522246
this is how I call the macro
Public Sub RunXlMacro()

Dim fname As String
fname = "Ipaymetrics by state_" & Format(Date, "yyyymmdd") & ".xls"
Dim exbook As Excel.Workbook
Dim ws As Worksheets
Dim appXL As Excel.Application
Set appXL = New Excel.Application
Set exbook = appXL.Workbooks.Open("C:\" & fname)
Set exbook = appXL.Workbooks.Open("S:\Payroll\XLTEMPLATE\Personal.xls")
appXL.Visible = True
appXL.Interactive = True
appXL.Run "Personal.xls!CreatePivot" ' run the excel macro
appXL.ActiveWorkbook.Save
appXL.Quit
MsgBox "Done!", vbOKOnly
0
 
LVL 6

Expert Comment

by:andreyman3d2k
ID: 33522309
I think the syntax is something like

Set WSB = exbook.sheets("tblEXPORTMETRICS")

Assuming exbook is the name of the workbook...
0
 

Author Comment

by:Serena2345
ID: 33522464
Sorry does not help
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 6

Accepted Solution

by:
andreyman3d2k earned 2000 total points
ID: 33522478
Also, if I understand correctly, the macro in in personal.xls, but you wish to run it on the other file.

Try swapping the order of these two lines of code:

Set exbook = appXL.Workbooks.Open("C:\" & fname)
Set exbook = appXL.Workbooks.Open("S:\Payroll\XLTEMPLATE\Personal.xls")

so make it:

Set exbook = appXL.Workbooks.Open("S:\Payroll\XLTEMPLATE\Personal.xls")
Set exbook = appXL.Workbooks.Open("C:\" & fname)
0
 

Author Closing Comment

by:Serena2345
ID: 33522555
Most humble thanks you guys are the best
0
 
LVL 6

Expert Comment

by:andreyman3d2k
ID: 33522629
No problem. Amazed that my utterly mediocre VBA skills (or lack thereof) actually resulted in something useful : )  Thanks for the grade & points!

Andrey
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question