topic Re: MS Excel VBO - Extended Amend Pivot Table Data Sourceについて in Japan Community

MS Excel VBO - Extended Amend Pivot Table Data Sourceについて

ShotaHomma — Mon, 22 Nov 2021 03:08:07 GMT

MS Excel VBO - Extended Amend Pivot Table Data Sourceで、
添付資料のようにパラメータやExcelデータを入れても動作しません。

もし、動作させた方がいらっしゃいましたら、
パラメータや、前提条件等、教えていただけないでしょうか？

Re: MS Excel VBO - Extended Amend Pivot Table Data Sourceについて

michaeloneil — Tue, 23 Nov 2021 12:56:47 GMT

Hi

The code in this I found didn't work correctly and I had to change it. The code I used for this is below and works well so should work for you. Let me know if everything worked for you.

Inputs - Handle, Workbook, Worksheet, Range, PivotTableName, DataWorksheet
================================================================
Dim wb, ws, wd As Object
Dim excel, sheet As Object
Dim rng As String

wb = GetWorkbook(Handle, Workbook)
wd = wb.Worksheets(DataWorksheet)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()

excel = wb.Application
sheet = excel.ActiveSheet

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

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

Re: MS Excel VBO - Extended Amend Pivot Table Data Sourceについて

ewilson — Tue, 23 Nov 2021 15:24:39 GMT

@Michael ONeil,

Thank you for your contribution to the Community. 😉 We'll get your fix added to the Extended VBO on the DX.

Cheers,
Eric

Re: MS Excel VBO - Extended Amend Pivot Table Data Sourceについて

ewilson — Tue, 23 Nov 2021 15:59:31 GMT

FYI - The VBO has been updated on the DX. The latest version is v2.3.

https://digitalexchange.blueprism.com/dx/entry/9648/solution/ms-excel-vbo---extended

Cheers,
Eric

Re: MS Excel VBO - Extended Amend Pivot Table Data Sourceについて

michaeloneil — Tue, 23 Nov 2021 16:08:34 GMT

Great news @ewilson thank you :)

Re: MS Excel VBO - Extended Amend Pivot Table Data Sourceについて

ShotaHomma — Fri, 26 Nov 2021 08:16:54 GMT

@Michael ONeil
@ewilson

Thank you for your quick response.

The good news is that the latest version has been released.

I'll give try it.