Solved

Pivot table macro gives error subscript out of range

Posted on 2010-08-25
6
732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 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