Hi @rifij ,
After so much time as per my understanding i have written VBscript for this.
The VBScript provided opens an Excel file and scans through each row in the first sheet. When it detects "FP3000" in a cell of column A, it replaces only that substring with the corresponding value from column G. The script saves the changes, closes Excel.
Please Copy the below code directly to your flow after writing the data table to excel and closing excel as a save document.
SET Excel_Filepath TO $'''C:\\Users\\Desktop\\Date_Format_Test.xlsx'''
@@copilotGeneratedAction: 'False'
Scripting.RunVBScript.RunVBScript VBScriptCode: $'''Option Explicit
Dim objExcel, objWorkbook, objWorksheet
Dim iRow, lastRow
\' Excel file path
Const filePath = \"%Excel_Filepath%\"
\' Create Excel object
Set objExcel = CreateObject(\"Excel.Application\")
\' Disable alerts to prevent confirmation prompts
objExcel.DisplayAlerts = False
\' Open the workbook
Set objWorkbook = objExcel.Workbooks.Open(filePath)
\' Set the first worksheet as the active sheet
Set objWorksheet = objWorkbook.Worksheets(1)
\' Find the last row with data in column A
lastRow = objWorksheet.Cells(objWorksheet.Rows.Count, \"A\").End(-4162).Row \' -4162 corresponds to xlUp
\' Loop through each row in column A
For iRow = 1 To lastRow
\' Check if the cell in column A contains \"FP3000\" (case-insensitive)
If InStr(1, objWorksheet.Cells(iRow, 1).Value, \"FP3000\", vbTextCompare) > 0 Then
\' Replace \"FP3000\" with the corresponding value from column G
Dim originalValue, replacedValue
originalValue = objWorksheet.Cells(iRow, 1).Value
replacedValue = ReplaceWithG(originalValue, objWorksheet.Cells(iRow, 1).Offset(0, 6).Value)
objWorksheet.Cells(iRow, 1).Value = replacedValue
End If
Next
\' Save and close the workbook
objWorkbook.Save
objWorkbook.Close
\' Quit Excel application
objExcel.Quit
\' Release the objects
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
WScript.Echo \"Replacement completed successfully.\"
Function ReplaceWithG(originalValue, replacement)
Dim parts, part, i
parts = Split(originalValue, \",\")
For i = LBound(parts) To UBound(parts)
If InStr(1, parts(i), \"FP3000\", vbTextCompare) > 0 Then
parts(i) = replacement
Exit For
End If
Next
ReplaceWithG = Join(parts, \",\")
End Function''' ScriptOutput=> VBScriptOutput3
(Note:- if you got your solution you can mark as solution and gives kudos)
Thanks & Regards
Vishnu Reddy
