locked
Powershell - Excel - Output all the values matching the string RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Experts,

    I am looking for a PowerShell script that will go through an excel file.

    If the date's column has today's date, the script needs to output the adjacent value of the matching string. (left side cell)

    I have partially succeeded in this. However, the script is just returning the very first value and not all the values across the file.

    I need to have some kind of loop setup in the script so that the script outputs all corresponding values matching the string. Not just very first value. Could you

    $File = "C:\temp\cert.xlsx"
$today = (get-date).ToString("d/MM/yyyy")

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($File)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(2)

$SearchString = $today

$Range = $Worksheet.Range("B1").EntireColumn


$Search = $Range.find($SearchString)

 $target = $Search.offset(0,-1)
 $target.text

    please advise. Thank you.

    Wednesday, February 13, 2019 6:15 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you JRV. Here is code  
    $File = "C:\temp\cert.xlsx"

$today = (get-date).ToString("d/MM/yyyy")

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Workbook = $Excel.workbooks.open($File)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(2)

$SearchString = $today

$Range = $Worksheet.Range("B1").EntireColumn
$Search = $Range.find($SearchString)

if ($search -ne $null) {
	$limit = $Search
	
do {
 $target = $Search.offset(0,-1)
 $target.text
 $Search = $Range.FindNext($Search)
} while ($search.row -ne $limit.row)

}

    • Marked as answer by jrprakash Wednesday, February 13, 2019 11:41 PM
    Wednesday, February 13, 2019 11:41 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    That is really not the correct way to do this as you are mixing two techniques.  Also "FindNext can fail.  Also the date is a date object and not a string.

    It is as simple as this:

    $range = $wb.Sheets[1].Range('B1').EntireColumn
if($cell = $range.find([datetime]::Today)){
    $cell.offset(0, -1).Text
    While($cell = $range.FindNext()){
        $cell.offset(0, -1).Text
    }
}else{
    Write-Host 'Not found' -fore Cyan
}

    Note that we use the date object as it will match any date format you may see in Excel.

    "FindNext will search once to the end of the column.  It will not search beyond the used range.

    \_(ツ)_/


    • Edited by jrv Thursday, February 14, 2019 12:01 AM
    • Marked as answer by jrprakash Thursday, February 14, 2019 12:45 AM
    Thursday, February 14, 2019 12:00 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Many issues.  Is the column a data type column?

    We would not use Excel to manage a CSV file.  A CSV file can be used by importing it into PowerShell.

    help import-csv -online

    \_(ツ)_/


    • Edited by jrv Wednesday, February 13, 2019 6:52 AM
    Wednesday, February 13, 2019 6:51 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Here is a starter that will get you closer,  Since I don't have your spreadsheet there is no way to give you an exact answer.

    $filename = 'C:\temp\cert.xlsx'

$xl = New-Object -ComObject Excel.Application
$xl.visible = $true
$wb = $xl.workbooks.open($filename)

$range = $wb.Sheets[1].Range('B1').EntireColumn
if($cell = $range.find([datetime]::Today)){
    $cell.offset(0, -1).Text
}else{
    Write-Host 'Not found' -fore Cyan
}

    \_(ツ)_/

    Wednesday, February 13, 2019 7:08 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JRV,

     Thank you for the quick response.

    However the script is still returning only the first value. It not going till the end of the cert file. Its stopping at the very first value it found and showing as output.

    It needs to output all the  values (which is $cell.offset(0,-1).text) matching the 'string' (today's date), not the just the first one it found. Could you please advice. Thank you. 


    Wednesday, February 13, 2019 8:36 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Then you need to do a $range.FindNex().

    See:
    https://docs.microsoft.com/en-us/office/vba/api/excel.range.findnext

    https://docs.microsoft.com/en-us/office/vba/api/excel.range.find

    \_(ツ)_/




    • Edited by jrv Wednesday, February 13, 2019 9:13 PM
    Wednesday, February 13, 2019 9:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you JRV. Here is code  
    $File = "C:\temp\cert.xlsx"

$today = (get-date).ToString("d/MM/yyyy")

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Workbook = $Excel.workbooks.open($File)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(2)

$SearchString = $today

$Range = $Worksheet.Range("B1").EntireColumn
$Search = $Range.find($SearchString)

if ($search -ne $null) {
	$limit = $Search
	
do {
 $target = $Search.offset(0,-1)
 $target.text
 $Search = $Range.FindNext($Search)
} while ($search.row -ne $limit.row)

}

    • Marked as answer by jrprakash Wednesday, February 13, 2019 11:41 PM
    Wednesday, February 13, 2019 11:41 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    That is really not the correct way to do this as you are mixing two techniques.  Also "FindNext can fail.  Also the date is a date object and not a string.

    It is as simple as this:

    $range = $wb.Sheets[1].Range('B1').EntireColumn
if($cell = $range.find([datetime]::Today)){
    $cell.offset(0, -1).Text
    While($cell = $range.FindNext()){
        $cell.offset(0, -1).Text
    }
}else{
    Write-Host 'Not found' -fore Cyan
}

    Note that we use the date object as it will match any date format you may see in Excel.

    "FindNext will search once to the end of the column.  It will not search beyond the used range.

    \_(ツ)_/


    • Edited by jrv Thursday, February 14, 2019 12:01 AM
    • Marked as answer by jrprakash Thursday, February 14, 2019 12:45 AM
    Thursday, February 14, 2019 12:00 AM