Need help creating excel pivot table via vba

Hello,

I am trying to create a pivot table but am having problems with the create pivot table method.  I am creating it within Access via vba.  Below is the relevant code.  I can create the source data worksheet and used a debug.print rng.rows count to verify that the source data range contains data.

ws.cells.EntireColumn.AutoFit
    ws.Range("A2").Select
    objExcelApp.ActiveWindow.SplitRow = 1
    objExcelApp.ActiveWindow.SplitColumn = 0
    objExcelApp.ActiveWindow.FreezePanes = True
    
    'Set up pivot table
    Dim rng As Excel.Range
    Dim rngPiv As Excel.Range
    Dim sPT As Excel.PivotTable
    
    Set rng = ws.UsedRange
    Debug.Print rng.Rows.Count
    'Set up newest version pivot table
    wb.Sheets.Add
    Set wsPiv = wb.Sheets("Signal_Code")
    wsPiv.Activate
    
    Dim PTCache As Excel.PivotCache
    Dim PivotDataRange As Excel.Range

    Set PivotDataRange = rng
    Set PTCache = wb.PivotCaches.Create _
        (SourceType:=xlDatabase, _
        SourceData:=PivotDataRange, _
        Version:=xlPivotTableVersion12)
    Set sPT = PTCache.CreatePivotTable(TableDestination:=wsPiv.cells(4, 1), TableName:="SignalCode", DefaultVersion:=xlPivotTableVersion12)
    

     With sPT.PivotFields("Signal_Code")
        .Orientation = xlRowField
        .Position = 1
    End With
   sPT.AddDataField ActiveSheet.PivotTables("Total Value").PivotFields("Total Value"), "Sum of Total Value", xlSum
    'Change number VBA.Format
    With sPT.PivotFields("CountOfItemCode")
        .NumberFormat = "0"
    End With

Open in new window

Juan VelasquezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Juan VelasquezAuthor Commented:
Hello,

I found some mistakes and have corrected them but still cannot create the pivot table.

Dim rng As Excel.Range
    Dim rngPiv As Excel.Range
    Dim sPT As Excel.PivotTable
    
    Set rng = ws.UsedRange
    Debug.Print rng.Rows.Count
    'Set up newest version pivot table
    wb.Sheets.Add
    Set wsPiv = wb.Sheets("Signal_Code")
    wsPiv.Activate
    
    Dim PTCache As Excel.PivotCache
    Dim PivotDataRange As Excel.Range

    Set PivotDataRange = rng
    Set PTCache = wb.PivotCaches.Create _
        (SourceType:=xlDatabase, _
        SourceData:=PivotDataRange, _
        Version:=xlPivotTableVersion12)
    Set sPT = PTCache.CreatePivotTable(TableDestination:=wsPiv.cells(4, 1), TableName:="SignalCode", DefaultVersion:=xlPivotTableVersion12)
    

     With sPT.PivotFields("Signal_Code")
        .Orientation = xlRowField
        .Position = 1
    End With
   sPT.AddDataField ActiveSheet.PivotTables("SignalCode").PivotFields("Total Value"), "Sum of Total Value", xlSum
    'Change number VBA.Format
    With sPT.PivotFields("SumOfTotalValues")
        .NumberFormat = "0"
    End With

Open in new window

0
Juan VelasquezAuthor Commented:
I found the problem, I wasn't setting wsPiv to the correct sheets.  It needs to be set to sheet1. I was setting it to Signal Code instead.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Great, ...you can accept your own post now...
0
Rory ArchibaldCommented:
BTW you also need to fix the unqualified use of ActiveSheet, or you'll find that Excel is left running.
0
Juan VelasquezAuthor Commented:
I was able to figure out the problem on my own.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.