Chandan008
New Member
What added code shall i put so that i can create the Pivot table in the new worksheet?
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")
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.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