Execute Excel Macro within specific sheet of workbook

tmcwade12 · August 10, 2023, 5:29pm

I’ve created a Macro in Excel that I want to run using UiPath. My Excel doc contains multiple sheets. Is there a way to specify the sheet that I want to run the Excel Macro for?

The problem I’m having is that when I execute the Macro in UiPath, the Macro is only being run on the first sheet in the workbook but I need it to be run on other sheets in the workbook as well.

Thanks!

raja.arslankhan · August 10, 2023, 5:35pm

@tmcwade12 Do you want to run it in all sheets?

raja.arslankhan · August 10, 2023, 5:43pm

@tmcwade12

Use Excel Application Scope:

Drag and drop an “Excel Application Scope” activity onto your UiPath workflow.
Configure the activity to open the Excel workbook containing the sheet where you want to run the macro.

Use Execute Macro Activity:

Within the “Excel Application Scope,” add an “Execute Macro” activity.
In the “Macro Name” property of the activity, enter the name of the macro you want to run.

Run Macro with ActiveSheet:

In your Excel macro code, refer to the active sheet using the ActiveSheet object. This will ensure that the macro runs within the sheet that is currently active.

tmcwade12 · August 10, 2023, 6:05pm

Hi @raja.arslankhan ,

Yes I’d like to be able to run it in all sheets. I’ve pasted my code below. Can you see anything I should change that would allow me to have this Macro run for each tab in the workbook instead of just the first sheet?

Thanks!

Sub CompleteMacro()
’
’ CompleteMacro Macro
’

’
Columns(“A:A”).Select
Selection.TextToColumns Destination:=Range(“A1”), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns(“G:G”).Select
Selection.TextToColumns Destination:=Range(“G1”), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns(“H:H”).Select
Selection.TextToColumns Destination:=Range(“H1”), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range(“L2”).Select
ActiveCell.Formula2R1C1 = “=RC[-5]:R[32]C[-4]-INT(RC[-5]:R[32]C[-4])”
Range(“L2:M34”).Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-2
Range(“O2”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=0
Selection.Replace What:=“0”, Replacement:=“”, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Application.CutCopyMode = False
Selection.NumberFormat = “[$-en-US]h:mm AM/PM;@”
Selection.Copy
Range(“G2”).Select
ActiveSheet.Paste
Columns(“L:P”).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns(“G:G”).Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range(“G1:G34”) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range(“A2:J34”)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns(“A:A”).Select
Selection.NumberFormat = “[$-x-sysdate]dddd, mmmm dd, yyyy”
Columns(“I:I”).Select
Selection.NumberFormat = “$#,##0”
Range(“A1:J34”).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range(“A1:J1”).Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Columns(“A:J”).Select
Selection.Columns.AutoFit
End Sub

Anil_G · August 10, 2023, 6:49pm

@tmcwade12

add sub completemacro(sheetname As String)

And inside you macro use the sheetname to select the sheet and then perform the activities

Sheetname can be passed from UiPath as argument

Cheers

supermanPunch · August 10, 2023, 6:56pm

Hi @tmcwade12 ,

Could you check with the below Modified Code :

Sub CompleteMacro()
’
’ CompleteMacro Macro
’

’
For Each ws In ThisWorkbook.Sheets

ws.Activate

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range("L2").Select
ActiveCell.Formula2R1C1 = "=RC[-5]:R[32]C[-4]-INT(RC[-5]:R[32]C[-4])"
Range("L2:M34").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-2
Range("O2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=0
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Application.CutCopyMode = False
Selection.NumberFormat = "[$-en-US]h:mm AM/PM;@"
Selection.Copy
Range("G2").Select
ActiveSheet.Paste
Columns("L:P").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range("G1:G34") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A2:J34")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("A:A").Select
Selection.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"
Columns("I:I").Select
Selection.NumberFormat = "$#,##0"
Range("A1:J34").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1:J1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Columns("A:J").Select
Selection.Columns.AutoFit

Next ws

End Sub

The Modification was just the Looping of the Sheets with For Each, with a Smaller version tested something in the below manner :

We loop through each sheet, and we activate the sheet, and as your code already does the operations for the activated sheet, we would not need to implement more operations here.

Let us know if this does not work.

tmcwade12 · August 10, 2023, 7:07pm

@supermanPunch ,

Thank you so much that did it!

system · August 13, 2023, 7:08pm

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.

Most Active Users - Yesterday
mpraveen1902
Palaniyappan
Parvathy
Anil_G
Swathi_Raju
Sara_Piana
Yoichi
Nitya1
ashwin.ashok
Rishita_aggarwal
More details...