Excell Pivot Table Coding

Ranjitha.molugu

New Member
Hi VJR,

Even I am getting the same error as "The given Key was not present in the dictionary"

Below is the code, I have added an Action Page to MS Excel VBO, also placed the screenshots, please help.
Code:
Dim wb as Object
Dim ws as Object
Dim rng as Object

wb= GetWorkbook(Handle,SWorkBook)
ws= GetWorksheet(Handle, SWorkBook,SWorkSheet)

rng=ws.UsedRange()

Dim PCache as Object = wb.PivotCaches.Create(SourceType:=1,SourceData:=rng).CreatePivotTable _
(TableDestination:=ws.Range("J15"), TableName:="PivotTable1")
 

Attachments

  • Action Page in Excel VBO.JPG
    64.2 KB · Views: 20

to_mas_re

New Member
Have you made sure that the Initialise tab (Information stage) has the same code as the MS Excel VBO if you are adding this code in a separate VBO. Also, the code in the Initialise tab calls other pages in the Excel VBO. So you need to have all of the required ones in order to get it working.

Below code is created by adding a new Action page after creating a copy of the 'Activate Worksheet' action of the Ms Excel VBO and adjusting the required Input and Output parameters.

This is a working code. If it is not working at your end then you will need to make sure you have included and interlinked all the codes and pages as mentioned above.

Code:
Dim rng as Object

Dim wb as Object = GetWorkbook(handle,workbookname)
Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname,createifmissing)

sheetexists = ws IsNot Nothing

If sheetexists then ws.Activate()

rng = ws.UsedRange()  'Change this to include any specific range

Dim PCache as object = wb.PivotCaches.Create(SourceType:=1, SourceData:= rng).CreatePivotTable _
(TableDestination:=ws.Range("F2"), TableName:="PivotTable1")


With ws.PivotTables("PivotTable1").PivotFields("Name")
.Orientation = 1    'For Row field
.Position = 1
End With

With ws.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = 1
.Position = 2
End With

With ws.PivotTables("PivotTable1").PivotFields("Age")
.Orientation = 2 ' For Column Field
.Position = 1
End with


You will need to add code to clear/delete any existing Pivot tables so it doesn't throw an error the next time when the above code tries to re-generate a Pivot Table using the same name as PivotTable1.

View attachment 410
Hi VJR, is there any way to change the hardcoded names and attributes to dynamic parameters? I want developer to define name of the column, its position, orientation and function, but I don't want him to change the VBO code everytime for that.

I am able to easily create whole "with-statement" but how do I put this statement dynamically into code stage?

Thx
 
Top