Solved

Pivot table macro gives error subscript out of range

Posted on 2010-08-25
6
714 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
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry does not help
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 6

Accepted Solution

by:
andreyman3d2k earned 500 total points
Comment Utility
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
Comment Utility
Most humble thanks you guys are the best
0
 
LVL 6

Expert Comment

by:andreyman3d2k
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now