Search code examples
excelvbablueprism

Excel VBO - Pivot Table Report Layout in Blue Prism

I'm trying to arrange my pivot table's report layout as tabular form.

In the offical document of Microsoft Excel, the related code for changing report layout is:

pvt.RowAxisLayout xlTabularRow

I wrote this code in Blue Prism as:

PvtTbl.RowAxisLayout = "xlTabularRow"

Also tried,

PvtTbl.RowAxisLayout("xlTabularRow")

However, somehow I'm facing with the exception. The message is about type mismatch. xlTabularRow is the child of XlLayoutRow (Source)

Can you please help me?

The whole code:


Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()

excel = ws.Application
sheet = excel.ActiveSheet

PvtTbl = ws.PivotTables(PivotTableName)
PvtTbl.RowAxisLayout = "xlTabularRow"

Success = True

Catch e As Exception
Success = False
Message = e.Message()

Finally

PvtTbl = Nothing
ws = Nothing

End Try```
Solution

  • I found the solution! Just simply change the

    PvtTbl.RowAxisLayout = "xlTabularRow"

    to

    PvtTbl.RowAxisLayout(1)