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

Welcome Forums General PowerShell Q&A How to find and replace the matching pattern inside a Excel macro file.

Viewing 1 reply thread
  • Author
    Posts
    • #202967
      Participant
      Topics: 11
      Replies: 4
      Points: 52
      Rank: Member

      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

      • This topic was modified 1 month, 2 weeks ago by grokkit. Reason: please format your code as per the forum posting instructions
    • #202979
      Participant
      Topics: 4
      Replies: 1908
      Points: 4,259
      Helping Hand
      Rank: Community Hero

      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

Viewing 1 reply thread
  • You must be logged in to reply to this topic.