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

  • Question

  • 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