Finding the contents of an Excel file in Powershell
I am currently working on a fairly large powershell script. However, I am stuck on one side. The problem is as follows.
I have different reports with the same file name, they just have a different timestamp at the end. Inside the report, I have a field that displays the date from when the report comes.
---> 2/1/2015 5:00:00 AM to 1/1/2015 5:00:00 AM <--- This is how it looks.
This field is randomly placed on the Excel sheet. Practically in the range from A5 to Z16. I would like the script:
Read file / Check cell range for dates, if date found and matches my search criteria, close sheet and move it to another folder / If date doesn't match, close and check next XLS file
This is what I got so far:
$File = "C:\test.XLS"
$SheetName = "Sheet1"
# Setup Excel, open $File and set the the first worksheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($file)
$Worksheets = $Workbooks.worksheets
$WorkSheet = $WorkBook.sheets.item($SheetName)
$SearchString = "AM" #just for test purposes since it is in every report
$Range = $Worksheet.Range("A1:Z1").EntireColumn
$Search = $Range.find($SearchString)
source to share
If you want it to scan the entire column from A to Z, you specify a range:
$Range = $Worksheet.Range("A:Z")
Then you should be able to execute $Range.Find($SearchText)
and if the text is found it will return the first cell it finds it, otherwise it will return nothing. So start Excel like you did, then loop through ForEach
and inside that open the workbook, find the text, if found, close it, move it, stop the loop. If not found, close the workbook and move on to the next file. The following worked fine for me:
$Destination = 'C:\Temp\Backup'
$SearchText = '3/23/2015 10:12:19 AM'
$Excel = New-Object -ComObject Excel.Application
$Files = Get-ChildItem "$env:USERPROFILE\Documents\*.xlsx" | Select -Expand FullName
$counter = 1
ForEach($File in $Files){
Write-Progress -Activity "Checking: $file" -Status "File $counter of $($files.count)" -PercentComplete ($counter*100/$files.count)
$Workbook = $Excel.Workbooks.Open($File)
If($Workbook.Sheets.Item(1).Range("A:Z").Find($SearchText)){
$Workbook.Close($false)
Move-Item -Path $File -Destination $Destination
"Moved $file to $destination"
break
}
$workbook.close($false)
$counter++
}
I'm even ambitious enough to add a progress bar so you can see how many files it can potentially look at, how much it has done, and what file it is looking at right now.
Now, this all assumes that you know exactly which row will be (at least partial) in that cell. If you are wrong, this will not work. Checking for ambiguous things takes much more time because you cannot use Excel's match function and must have PowerShell to check each cell in the range one at a time.
source to share