Hi @rickvvaldez
I have written a vbscript which can be utilised as a customised function and can work for ANY excel file path, ANY sheet, From ANY cell, To ANY cell, ANY number of decimal places (one or two as you needed these two options).
This is how the overall Flow looks like.
Before running the Flow
After running the Flow for 2 decimal places
Simply change your inputs as per your needs in the Set variable statements.
Below is the script for the vbscript action.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFilePath%")
objWorkbook.Sheets("%SheetName%").Activate
If "%SingleDecimal%" = True then
objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "0.0%%"
Else
objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "0.00%%"
End if
objWorkbook.save
objWorkbook.close
objExcel.Quit
set objWorkbook=nothing
set objExcel=nothing
Alternatively:
Paste the below code in a blank flow editor, change the Excel path, set the values of the desired variables and run the flow
SET ExcelFilePath TO $'''C:\\test\\filename.xlsx'''
SET SheetName TO $'''Sheet1'''
SET FromRange TO $'''A1'''
SET ToRange TO $'''A3'''
SET SingleDecimal TO $'''False'''
Scripting.RunVBScript VBScriptCode: $'''Set objExcel = CreateObject(\"Excel.Application\")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(\"%ExcelFilePath%\")
objWorkbook.Sheets(\"%SheetName%\").Activate
If \"%SingleDecimal%\" = True then
objExcel.Range(\"%FromRange%\" & \":\" & \"%ToRange%\").NumberFormat = \"0.0%%\"
Else
objExcel.Range(\"%FromRange%\" & \":\" & \"%ToRange%\").NumberFormat = \"0.00%%\"
End if
objWorkbook.save
objWorkbook.close
objExcel.Quit
set objWorkbook=nothing
set objExcel=nothing''' ScriptOutput=> VBScriptOutput ScriptError=> ScriptError
