locked
How to find and replace the matching pattern inside a Excel macro file RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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.


    $before				 	= @(Get-Process [e]xcel | %{$_.Id})
$excel					= New-Object -comobject Excel.Application
$ExcelId			 	= Get-Process excel | %{$_.Id} | ?{$before -notcontains $_}
$excel.Visible		 	= $false
$excel.DisplayAlerts	= $false
$excel.AskToUpdateLinks = $false
		
$workbook = $excel.Workbooks.Open('D:\dinesh\worksheetArea\workbook1.xlsm')
For($i = 1 ; $i -le $workbook.Sheets.count ; $i++)
{
	$worksheet = $workbook.sheets.item($i)  
	$search  = $worksheet.usedRange.Find('ServerId::50012')
		  If($search){
				$final_list += worksheet.Name						
			}
}
if (!$Workbook.saved){ 
	$workbook.save() 
 }
 $workbook.close()
 $excel.quit()
 $excel = $null
 [gc]::collect()
 [gc]::WaitForPendingFinalizers()


    • Changed type Dinesh Edvin Tuesday, February 18, 2020 2:33 PM
    • Edited by Dinesh Edvin Tuesday, February 18, 2020 2:36 PM
    • Changed type Dinesh Edvin Tuesday, February 18, 2020 2:36 PM
    Tuesday, February 18, 2020 11:03 AM

Answers

All replies