Excell Pivot Table Coding

viraj

Member
Hi,

I tried to create custom VBO by Using VB code shown Below.But It doesn't work.
Can someone help me to figure out the issue.

Dim ws as object=Getworksheet(handle,workbookname,Worksheetname)
Dim r as object=ws.Range(range).Select
Dim ns as Object = GetWorksheet(handle,workbookname,worksheetnamea,createifmissing)
sheetexists = ws IsNot Nothing
If sheetexists then ws.Activate()
Dim PivotCaches as object=ns.Create(SourceType:="xlDatabase", SourceData:= r, Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:=ns, TableName:="PivotTable1"
 

viraj

Member
Internal : Could not execute code stage because exception thrown by code stage: The given key was not present in the dictionary.
 

VJR

Well-Known Member
1. Correct the spelling in the red text below

Dim ws as object=Getworksheet(handle,workbookname,Worksheetname)
Dim r as object=ws.Range(range).Select
Dim ns as Object = GetWorksheet(handle,workbookname,worksheetnamea,createifmissing)


2. Remove this portion including the comma
Dim PivotCaches as object=ns.Create(SourceType:="xlDatabase", SourceData:= r , Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:=ns, TableName:="PivotTable1"
 

VJR

Well-Known 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.

1522564554342.png
 
Last edited:

jreddy

New Member
Hi @VJR

I used your code to create pivot tables, but added the line to create a DataField:

new_ws.PivotTables("PivotTable1").AddDataField(new_ws.PivotTables("PivotTable1").PivotFields(valueField), "Anzahl", -4112)

I'm trying to create a pivot table, which uses the same column for row fields and data fields (e.g. show all the statuses and calculate count for each of them). But it doesn't work - it simply overwrites the field used for row fields and uses it for data field, which yields quite useless results (see screenshots)

Can you help me make it work?
 

Attachments

  • What I get.png
    34.1 KB · Views: 161
  • What I need.png
    38.2 KB · Views: 157

VJR

Well-Known Member
Hi jreddy,


Code:
with ws.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = 1
.Position = 1    
end with

with ws.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = 4 'xlDataField
.Position = 1   
.Function = -4112 'xlCount
end with

The above code outputs the Status and its count as follows.

View attachment 1534496250583.png
 

waziaks

Member
I am creating custom tab in MS excel vbo using above pivot code. I am getting error to declare Createifmissing & sheetsexist? Which I don't know.

Please help me to resolve attached error

thanks
 

Attachments

  • Pivot.JPG
    45.3 KB · Views: 113

VJR

Well-Known Member
Hi waziaks,

Assuming you are appropriately adding a new action in the existing Ms Excel VBO as you have mentioned in your post.
Can you check whether the Global Code tab in the Initialise tab has got the GetWorksheet function importantly with four parameters.

Also for the 'createifmissing' error make sure you have followed the instructions as below and the tab you are developing has all the necessary parameters and data items.
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.
 

waziaks

Member
Thanks
I am able to create a pivot. However, I am getting count of data what if I need to sum the data.
Where i can find material to learn functions like (.Function = -4112). -4112 is for count need to know what other functions can be used instead of count.

Thanks
 

Fmandani

New Member
Hi, I have followed the instructions but I am getting internal error when calling from process stating "Number of parameters specified does not match the expected number." Strange because my input parameters are: handle, workbookname, worksheetname, createifmissing, reference (for ranges of cells).
 

VJR

Well-Known Member
Hi, I have followed the instructions but I am getting internal error when calling from process stating "Number of parameters specified does not match the expected number." Strange because my input parameters are: handle, workbookname, worksheetname, createifmissing, reference (for ranges of cells).
Can you tell which among the above posts you are referring the instructions from? You can mention the post #.
Until then from your error message if you have created a new action by making a copy of some other action then check whether a parameter requires adding/deleting in the action Start and End stages and check the same in the Input/Output parameters while calling from the process.
 

Fmandani

New Member
Can you tell which among the above posts you are referring the instructions from? You can mention the post #.
Until then from your error message if you have created a new action by making a copy of some other action then check whether a parameter requires adding/deleting in the action Start and End stages and check the same in the Input/Output parameters while calling from the process.
Hey VJR! Thanks for the response. The code from post 8 is what I used and I added an input “reference” which goes inside Used ranfe for the corresponding cells. I am passing a range such as “A1:C200” in from the process level. I actually created a new action instead of copying the activate worksheet page, so there are no leftover arguments that need to be passed on which I have no added.
 

VJR

Well-Known Member
Hi Fmandani,

Definitely there is somewhere a mismatch in the sending and accepting of the parameters, either Input or Output parameters.
Check the Process Input and Output parameters, the Object Start stage Input and Output parameters, the End stage Input and Output parameters, the Code stage Input and Output parameters.
 

Balerion1

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
I am using this same code and i keep getting the error
Internal : Could not execute code stage because exception thrown by code stage: The given key was not present in the dictionary.
Any help would be great
 
Top