locked
I have two different Excel files.I am trying to create powershell script to find data from column 2 (first file) and replace data into column 1(first file) also need to modify second excel files. RRS feed

  • Question

  • function excel_search_replace ( $worksheet, $column_name, $search_str, $replace_str ){
    
    echo "Replacing all '$search_str' with '$replace_str' in column '$Name'"
    
    $range = $worksheet.Range( "$($column_name)1" ).EntireColumn
    
    #$range = $worksheet.Range( "$($column_name)1","$($column_name)2" ).EntireColumn
    
    #$Range = $Worksheet.Range("A1","B1").EntireColumn
    
    $search = $range.find( $search_str )
    
    $i = 0
    
    if ( $search -ne $null ) {   
    
       $i += 1
    
           $first_addr = $search.Address
    
           do {
    
               $i += 1
    
             $search.value() = $replace_str
    
             $search = $range.FindNext( $search )
    
       } while ( $search -ne $null -and $search.Address -ne $first_addr )
    
        }
    
        echo "...Found and replaced $i instances of '$search_str'"
    
        return $void
    
    }
    
    $source_file = 'C:\test.xlsm'
    
    $excel_obj = New-Object -ComObject 'Excel.Application'
    
    $excel_obj.DisplayAlerts = $false
    
    $excel_obj.Visible = $false
    
    $workbook = $excel_obj.Workbooks.Open( $source_file ) # Open the file
    
    #$Range = $Worksheet.Range("A1","B1").EntireColumn
    
    $sheet = $workbook.Sheets.Item( 1 ) # select target worksheet by index
    
    excel_search_replace $sheet 'B' '14444' '22225'
    
    [void]$workbook.save() # Save file
    
    [void]$workbook.close() # Close file
    
    [void]$excel_obj.quit() # Quit Excel
    
    [Runtime.Interopservices.Marshal]::ReleaseComObject( $excel_obj ) >$null # Release COM



    • Edited by ShahPARTH Thursday, October 3, 2019 6:54 AM
    Thursday, October 3, 2019 4:58 AM

All replies

  • Please edit you original post and fix the code. See: How to post code in Technet Forums  for instructions.


    \_(ツ)_/

    Thursday, October 3, 2019 5:16 AM
  • Please read the link I posted and post your code correctly using the code posting tool. We ccannot read or copy your code and nmo one will help you until you fix your post.


    \_(ツ)_/


    • Edited by jrv Thursday, October 3, 2019 5:45 AM
    Thursday, October 3, 2019 5:43 AM
  • When you find an item in a column the object returned is the Range. The Range is a single cell. TO get the ROw and associated cells just do this:

    $search.Rows[1].EntireRow.Cells[2].value2

    This gives you the second column of the row.


    \_(ツ)_/

    Thursday, October 3, 2019 5:44 AM
  • Thanks man. I have updated the query as per your instructions. 
    Thursday, October 3, 2019 5:57 AM
  • Thanks but you left the old stuff in and it is still unreadable. Try to post code that is correctly formatted when you want to post large blocks.

    Here is how to correctly search a worksheet and access all columns in a row that you have found.  This is also how to format code so that it is readable and makes sense.

    $source_file = 'C:\test.xlsm'
    $search_str = '14444'
    $replace_str = '22225' 
    
    $xl = New-Object -ComObject Excel.Application
    $xl.DisplayAlerts = $false
    $xl.Visible = $true
    $wb = $xl.Workbooks.Open($source_file)
    
    $range = $wb.Worksheets[1].Columns[2].EntireColumn
    if($search = $range.Find($search_str)){
    	$search.EntireRow.Cells[1].value2 = $replace_str
    	$address = $search.Address()
    	while($true){
    		$search =  $range.FindNext($search)
    		if($address -eq $search.Address()){ break }
    		$search.EntireRow.Cells[1].value2 = $replace_str
    	}
    }
    
    $wb.save()
    $wb.close()
    $xl.quit()

    There is no point to using a function  as it only makes things harder.

    Note that I search column 1 and replace in column 2.

    The code you are using is from old VBS and VBA sources and does not work well in PowerShell.  In PS  we are using Interop for access.


    \_(ツ)_/

    Thursday, October 3, 2019 6:37 AM
  • Thanks. The above code is working but still have an issue. Is it possible to change on different file if the file has same content but in different format.

    Ex: ID:Name:Value1:Value2

    Thursday, October 3, 2019 7:14 AM
  • You can use that method on any Excel workbook. Just provide the name and other information.


    \_(ツ)_/

    Thursday, October 3, 2019 7:19 AM
  • I have second excel file in which data are stored in different format.

    Ex: 25555;parth;60;60;50;170 in second excel file in only one column

    and 

    Ex: Name: parth ID 25555 in two different columns in first excel file

    I am trying to change entry into second excel file from ID and Name from first excel file.

    Thanks for your help. I really appreciate it.

    Regards,

    Parth

    Thursday, October 3, 2019 11:44 PM
  • You can use any colu8mns you want. Just change the column numbers to the ones you need. It takes only two keystrokes.


    \_(ツ)_/

    • Marked as answer by ShahPARTH Friday, October 4, 2019 12:11 AM
    • Unmarked as answer by ShahPARTH Friday, October 4, 2019 12:12 AM
    Thursday, October 3, 2019 11:48 PM
  • Hi Sorry. Can you please tell me how to change destination file because here source file is different and destination file is different.

    Thanks.

    Friday, October 4, 2019 12:14 AM
  • Just type a new name. You need to learn basic PowerShell before pursuing this any further. We cannot write your script one line at a time.


    \_(ツ)_/

    Friday, October 4, 2019 12:18 AM
  • Thanks buddy for your help and time.
    Friday, October 4, 2019 12:21 AM
  • Hi buddy, I am using function for pop-up box instead of direct search. Please tell me how it works in below code.

    $source_file = 'C:\test.xlsm' $search_str = '' $replace_str = '' function New-InputBox { param ( [Parameter(Mandatory)] [string]$search_str, [Parameter(Mandatory)] [string]$replace_str ) $search_str, $replace_str } Show-Command -Name New-InputBox $xl = New-Object -ComObject Excel.Application $xl.DisplayAlerts = $false $xl.Visible = $true $wb = $xl.Workbooks.Open($source_file) $range = $wb.Worksheets[1].Columns[2].EntireColumn if($search = $range.Find($search_str)){ $search.EntireRow.Cells[3].value2 = $replace_str $address = $search.Address() while($true){ $search = $range.FindNext($search) if($address -eq $search.Address()){ break } $search.EntireRow.Cells[3].value2 = $replace_str } } $wb.save() $wb.close() $xl.quit()

    Thanks in advance.

    Friday, October 4, 2019 2:45 AM