How to find and replace the matching pattern inside a Excel macro file.

PowerShell Help
1

Hi,

I have an excel macro file which contain multiple sheets in it.
What I want is I have to search for a matching string in all the sheets and replace them if match found.

Note : The matching text will be present in a random cell and random sheet.
It would be like ‘\RS-DISCISDG01\dshare\runwork\deploy_area’ and it should be replaced to ‘\DF-USTRYFYG897\dshare\runwork\deploy_area’

In the below code, am just trying to find the matching pattern and its not working as expected.
Please suggest a way to find and replace the content in excel macro file.

$File = 'D:\myproject\JVDT\Workbook\RunMaster.xlsm'
$SearchText = 'RS-DISCISDG01'
$replacetext = 'DF-USTRYFYG897'
$Excel = New-Object -ComObject Excel.Application 

$Workbook = $Excel.Workbooks.Open($File)
foreach ($WorkSheet in $workbook.Worksheets) {
	$val = $Worksheet.usedrange.Find($SearchText)
	Write-Host "val : $val"
    
	If($Worksheet.usedrange.Find($SearchText))
    { 
	    $Workbook.Close($false)
	    write-host "Search text Exists"
		#	$workbook.close($false)		
	    break 
	    
    }
  }
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

Thanks
Dinesh

2

When you crosspost the same question at the same time in different forums you should at least post links to the other crossposts along with your question to avoid for people willing to help you to make their work twice or more …
https://social.technet.microsoft.com/Forums/en-US/3d7e3a61-e651-45a9-a0b3-c9e6bf7edaa0/how-to-find-and-replace-the-matching-pattern-inside-a-excel-macro-file?forum=winserverpowershell