You’re offline. This is a read only version of the page.
64
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?
@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:
Share
Report
All responses (
Answers (
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 🙏
