Answered by:
Powershell - Excel - Output all the values matching the string

Question
-
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
-
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 -
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.
\_(ツ)_/
Thursday, February 14, 2019 12:00 AM
All replies
-
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 -
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 -
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 -
Then you need to do a $range.FindNex().
See:
https://docs.microsoft.com/en-us/office/vba/api/excel.range.findnexthttps://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 -
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 -
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.
\_(ツ)_/
Thursday, February 14, 2019 12:00 AM