VBA MACRO

(0) Share

Report

Posted on by AVTS

538

Team ,

My Requirement is like this .

i have a file name name.xlsx and have got working macro VBA follows.

Sub PivotTable()

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Sheet1")


 LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="PivotTable")

Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable")


 Sheets("PivotTable").Select
 
Sheets("PivotTable").Select
 
 With ActiveSheet.PivotTables("PivotTable").PivotFields("Customer")
 .Orientation = xlRowField
 .Position = 1
 End With
 With ActiveSheet.PivotTables("PivotTable").PivotFields("Cost Element")
 .Orientation = xlRowField
 .Position = 2
 End With
 With ActiveSheet.PivotTables("PivotTable").PivotFields("Rebates")
 .Orientation = xlValuesField
 .Position = 1
 End With
 ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
 "PivotTable").PivotFields("Rebates"), "Sum of Rebates", xlSum
'Sub SetClassicPT()
 
 With ActiveSheet.PivotTables("PivotTable")
 
 .InGridDropZones = True
 .RowAxisLayout xlTabularRow
 End With
 
 ' ActiveSheet.PivotTables("PivotTable").InGridDropZones = True
 
 
'End Sub
 ActiveSheet.PivotTables("PivotTable").PivotFields("Customer").Subtotals = _
 Array(False, False, False, False, False, False, False, False, False, False, False, False)
 ActiveSheet.PivotTables("PivotTable").PivotFields("Product").Subtotals = _
 Array(False, False, False, False, False, False, False, False, False, False, False, False)
 ActiveSheet.PivotTables("PivotTable").PivotFields("Rebates").Subtotals = _
 Array(False, False, False, False, False, False, False, False, False, False, False, False)
 ActiveSheet.PivotTables("PivotTable").PivotFields("Cost Element").Subtotals _
 = Array(False, False, False, False, False, False, False, False, False, False, False, False _
 )
 ActiveSheet.PivotTables("PivotTable").PivotFields("Period").Subtotals = Array _
 (False, False, False, False, False, False, False, False, False, False, False, False)
 ActiveSheet.PivotTables("PivotTable").RepeatAllLabels xlRepeatLabels

End Sub

Following flow is created in power automate desktop.

I am having issue since the VBA macro is available in another file.

I need to run the macro vba from other sources against this file name name.xlsx. How to achieve this.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Open your macro workbook
use the window recorder to get File-> Open-> Browse -> wait for window title “Open” (you will have to add this after recorder) -> Send keys for file path {Enter} file name {Enter}
Attach to Excel (file name)

Now both files will be in the same Microsoft Excel instance and you can run the macro from the macro workbook.

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @AVTS

Please clarify a few things.
1. In line #3 you are passing a .vbs file in "Run Excel macro". That is not correct. VBS and VBA are two different things. It should be a .xlsm macro file (for newer office versions).

2. I need to run the macro vba from other sources against this file name name.xlsx
SapDatapivot.xlsx and Name.xlsx
- In order to suggest you with correct names please share which one is the macro file and which is the file where you want to generate the Pivot.
- Also are you sure of these extensions? Does your macro file not have .xlsm as the extension?

Was this reply helpful? Yes No
AVTS 538 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Macro is available in "Name.xslm" . And Pivot generate file name is "SapDatapivot.xlsx"

Was this reply helpful? Yes No
Verified answer

VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!
In your Name.xlsm in the beginning lines of code add the Workbooks.open as shown below

Dim LastCol As Long On Error Resume Next Application.DisplayAlerts = False Workbooks.Open ("C:\Path here\SapDatapivot.xlsx")

Then call this macro from PAD.
ExcelInstance is the instance returned after "Launch Excel" of Name.xlsm

For your future reference, the ideal way is to create a custom macro function with parameters like below and then pass these parameters from PAD while calling the macro.
Public Sub PivotCreate(Book, SheetName, SheetRange)

Was this reply helpful? Yes No