Power Platform Community Forum Thread Details

I am running the following VBScript in Power Automate Desktop. The cell "H2" and the range "H2:H17" will have a dynamic column (i.e. column H in this example), so how do I make the column dynamic in this VBScript action?

Categories:

Power Automate Desktop

@MCubedMama

Simply define two variables for the starting index and ending index, then incorporate them into your VB script as shown below:

Complete flow screenshot:

Code:

SET ExcelPathStats TO $'''C:\\Deenu\\Dates.xlsx'''
SET StartingIndex TO $'''D3'''
SET EndingIndex TO $'''D17'''
@@copilotGeneratedAction: 'False'
Scripting.RunVBScript.RunVBScript VBScriptCode: $'''\' VBScript to open, manipulate, and close an Excel workbook

\' Create an Excel application object
Set objExcel = CreateObject(\"Excel.Application\")
objExcel.Visible = True \' Make Excel visible

\' Open the workbook
Set objWorkbook = objExcel.Workbooks.Open(\"%ExcelPathStats%\")
Set objWorksheet = objWorkbook.Worksheets(\"Stats\") \' Specify the worksheet name

\' Copy formula from H2 and paste it to the range H2:H17
objWorksheet.Range(\"%StartingIndex%:%EndingIndex%\").Formula = objWorksheet.Range(\"H2\").Formula

\' Save and close the workbook
objWorkbook.Save
objWorkbook.Close

\' Quit Excel
objExcel.Quit

\' Release objects
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing''' ScriptOutput=> VBScriptOutput

How to copy and paste the above code in your PAD?

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀. If you'd like to appreciate me, please write a LinkedIn recommendation 🙏