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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks man. I have updated the query as per your instructions. 
    Thursday, October 3, 2019 5:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    You can use that method on any Excel workbook. Just provide the name and other information.

    \_(ツ)_/

    Thursday, October 3, 2019 7:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks buddy for your help and time.
    Friday, October 4, 2019 12:21 AM
  • Question
    Sign in to vote
    0
    Sign in to vote 
    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