Solved

Pivot table macro gives error subscript out of range

Posted on 2010-08-25
6
716 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 6

Accepted Solution

by:
andreyman3d2k earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

929 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

12 Experts available now in Live!

Get 1:1 Help Now