Pivot table data source

[algazaras]

Hi @yogeshc99 could you please show your input and output for the final object. I am having trouble replicating the work you did..

Thanks a lot

 

[yogeshc99]

Here you go -

View attachment 1542824970421.png

 

[algazaras]

Thanks a lot @yogeshc99
However I can't seem to make it work. I am using the code you posted in #99 but without results, and I am etting an exception all the time.

The code compiler throws the next errors:

Description: Compiler warning at line 2: Unused local constant: 'xlToRight'.
Description: Compiler warning at line 1: Unused local constant: 'xlDown'.
Description: Compiler warning at line 7: Unused local variable: 'pvtTbl'.
Description: Compiler warning at line 8: Unused local variable: 'srcData'.

Also, where do I input the new Source data range. I am not an expert in VBS but it seems to me you don't use an input for that, is that so?


Thanks a lot for your help, I spent yesterday quite a lot of hours with this

 

[SrinivasPabba]

Thank you for all the help guys. Here is final code. You can have pivot and source data in different sheets.

Const xlDown as long = -4121
Const xlToRight as long = -4161

Dim sBook As Object
Dim dsSheet, dpSheet As Object
Dim rng As String
Dim pvtTbl As Object
Dim srcData As String
Dim excel, sheet As Object

Try

sBook = GetWorkbook(Handle, SourceWorkbook)
dsSheet = sBook.Worksheets(DataSourceWorksheet)
dpSheet = sBook.Worksheets(PivotWorksheet)


sBook.Activate()
dsSheet.Activate()
excel = sBook.Application
sheet = excel.ActiveSheet

rng = sheet.Range("A1:C12").Address(ReferenceStyle:=-4150)
rng = dsSheet.Name & "!" & rng


dpSheet.Activate()
sheet = excel.ActiveSheet
sheet.PivotTables(PivotTableName).SourceData = rng

Success = True
oRange = Cstr(rng)
Catch e As Exception
Success = False
Message = e.Message
Finally
sBook = Nothing
dsSheet = Nothing
dpSheet = Nothing
End Try

Hi Yogesh/All

**Regarding Pivotal table update using Code Stage**

When i tried to use above code, initailly i am getting compile error due to these 3 lines as it showing these variables declared but not used
Const xlDown as long = -4121
Const xlToRight as long = -4161

Dim srcData As String
Hence i removed them and updated the code . Now No Compile errors still not able to update the code when step through the Code
received below Error Message :
The given key was not present in the dictionary.
________________________________

Dim sBook As Object
Dim dsSheet, dpSheet As Object
Dim rng As String
Dim pvtTbl As Object
Dim excel, sheet As Object

Try

sBook = GetWorkbook(Handle, SourceWorkbook)
dsSheet = sBook.Worksheets(DataSourceWorksheet)
dpSheet = sBook.Worksheets(PivotWorksheet)

sBook.Activate()
dsSheet.Activate()
excel = sBook.Application
sheet = excel.ActiveSheet
pvtTbl = sheet.PivotTables(PivotTableName)
rng = sheet.Range(Range).Address(ReferenceStyle:=-4150)
rng = dsSheet.Name & "!" & rng


dpSheet.Activate()
sheet = excel.ActiveSheet
sheet.PivotTables(pvtTbl).SourceData = rng

Success = True
oRange = Cstr(rng)
Catch e As Exception
Success = False
Message = e.Message
Finally
sBook = Nothing
dsSheet = Nothing
dpSheet = Nothing
End Try
___________

I created all Data items and given the sheet names and range, getting error

View attachment 1554126077367.png
View attachment 1554126174840.png

******I also want to find other pivotal tables in the Worksheet and update the Pivotal table range same
How to find all pivotal tables in Sheet and refresh all Pivotal Tables after the update *****

Please guide me in this regards

 

[SrinivasPabba]

Thanks a lot @yogeshc99
However I can't seem to make it work. I am using the code you posted in #99 but without results, and I am etting an exception all the time.

The code compiler throws the next errors:

Description: Compiler warning at line 2: Unused local constant: 'xlToRight'.
Description: Compiler warning at line 1: Unused local constant: 'xlDown'.
Description: Compiler warning at line 7: Unused local variable: 'pvtTbl'.
Description: Compiler warning at line 8: Unused local variable: 'srcData'.

Also, where do I input the new Source data range. I am not an expert in VBS but it seems to me you don't use an input for that, is that so?


Thanks a lot for your help, I spent yesterday quite a lot of hours with this

 

[SrinivasPabba]

Thank you for all the help guys. Here is final code. You can have pivot and source data in different sheets.

Const xlDown as long = -4121
Const xlToRight as long = -4161

Dim sBook As Object
Dim dsSheet, dpSheet As Object
Dim rng As String
Dim pvtTbl As Object
Dim srcData As String
Dim excel, sheet As Object

Try

sBook = GetWorkbook(Handle, SourceWorkbook)
dsSheet = sBook.Worksheets(DataSourceWorksheet)
dpSheet = sBook.Worksheets(PivotWorksheet)


sBook.Activate()
dsSheet.Activate()
excel = sBook.Application
sheet = excel.ActiveSheet

rng = sheet.Range("A1:C12").Address(ReferenceStyle:=-4150)
rng = dsSheet.Name & "!" & rng


dpSheet.Activate()
sheet = excel.ActiveSheet
sheet.PivotTables(PivotTableName).SourceData = rng

Success = True
oRange = Cstr(rng)
Catch e As Exception
Success = False
Message = e.Message
Finally
sBook = Nothing
dsSheet = Nothing
dpSheet = Nothing
End Try

 

[SrinivasPabba]

Hi Yogesh, Neeraj, VJR

Can you share the full code which is working for *****updating pivot table data source range**** where the pivot table and data source are from different work sheets
Sheet 1 has Pivot table1, Pivot Table 2 , Pivot Table 3 , Pivot Table 4, Pivot Table 7
Sheet 2 has Data source range related to pivot tables
I need to update the data source for all the pivot tables and refresh all the tables

Please share the code,
The Above Yogesh code not working, hence asking

 

[naveedraza]

The best answer I would recommend this.

1.create a dynamic range using offset nd counta formula in excel itself.
2. Create a define name using 1st step range.
3. In pivot table data source enter the define name.
4. Refresh the data that's it.

In blue prism code stage , just add the code for refreshing.

 

[SrinivasPabba]

Thank you for your response Naveed
If you have full working code , can you share that please

Here is my code, This is working for a small single pivot table used for testing but not for a complex excel pivot table with sheet with multiple Pivot tables, ****Can you able to Tweek this code to work for multiple pivot tables, I am looping each pivot table , thats fine , but some how this code not updating the range in pivot table for a complex excel sheet with multiple pivot tables which i am working on ***
Currently i created a code using global send keys and events by passing key board short keys and its working for me , but want to use VBA code stage to do this purpose

Usecase:
*****updating pivot table data source range**** where the pivot table and data source are from different work sheets
Sheet 1 has Pivot table1, Pivot Table 2 , Pivot Table 3 , Pivot Table 4, Pivot Table 7
Sheet 2 has Data source range related to pivot tables
I need to update the data source for all the pivot tables and refresh all the tables


Dim wb as Object = GetWorkbook(handle, WorkbookName)
Dim ws as Object = GetWorksheet(handle, WorkbookName, WorksheetName, false)

if wb is Nothing Or ws is Nothing then
Throw New Exception("WorkbookName or WorksheetName is incorrect")
end if
wb.Activate()
ws.Activate()

If SourceWorksheetName="" Then
SourceWorksheetName = Left(ws.PivotTables(1).SourceData,Len(ws.PivotTables(1).SourceData)-InStr(ws.PivotTables(1).SourceData,"!")-2)
Else
SourceWorksheetName="'"+SourceWorksheetName+"'"
End If

SourceNewRange = SourceWorksheetName+"!"+ws.Range(SourceNewRange).Address(ReferenceStyle:=-4150)
'SourceNewRange = SourceWorksheetName+"!"+ws.Range(SourceNewRange).Address(ReferenceStyle:=-4150)
rangeoutput = Cstr(SourceNewRange)


ws.PivotTables(PivotTableName).SourceData=SourceNewRange
ws.PivotTables(PivotTableName).RefreshTable

 

[Himlove Miradwal]

Hi Yogesh, i used your code but it is removing the current pivot columns which are already set

 

[ADITYA_RPA]

This is the error that I get with this solution - attached screenshot

hi i am also getting same error please any leads help
thank you

 

[Herminio217]

Hi guys, I had the same problem, and here is my solution:

Dim wb, ws, nws As Object
Dim rangeR1C1 As String
Dim excel, sheet As Object

Try
wb = GetWorkbook(Handle, WorkbookName)
ws = wb.Worksheets(WorksheetPivotTable) 'Worksheet that contains the pivot table
nws = wb.Worksheets(NewWorksheetDataSource) 'Worksheet taht contains the new range for the data source

wb.Activate()
nws.Activate()
excel = wb.Application
sheet = excel.ActiveSheet

rangeR1C1 = sheet.Range(NewRangeDataSource).Address(ReferenceStyle:=-4150) 'this step convert reference like A1:B2 to R1C1:R2C2
rangeR1C1 = nws.Name & "!" & rangeR1C1

ws.Activate()
sheet = excel.ActiveSheet
sheet.PivotTables(PivotTableName).SourceData = rangeR1C1

Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
nws = Nothing
ws = Nothing
End Try