CODEWITHSUNDEEP
excelpowershell
Dani
Dani

Reputation: 43

Finding content of Excel file in Powershell

I am currently working on a fairly large powershell script. However, I got stuck at one part. The issue is the following.

I have various reports with the same file name, they just have a different time stamp at the end. Within the report, I have a field displaying the date from when to when the report is from.

---> 2/1/2015 5:00:00AM to 3/1/2015 5:00:00AM <--- This is what it looks like.

This field is randomly placed on the Excel Sheet. Pretty much in the range of A5 to Z16. What I would like the script to do is:

Read the file / Check the range of cells for the dates, if the date is found and it matches my search criteria, close the sheet and move it to a different folder / If date does not 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)

Upvotes: 4

Views: 27424

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36277

If you want it to search the entire column for A to Z you would specify the range:

$Range = $Worksheet.Range("A:Z")

Then you should be able to execute a $Range.Find($SearchText) and if the text is found it will spit back the first cell it finds it in, otherwise it returns nothing. So start Excel like you did, then do a ForEach loop, and inside that open a workbook, search for your text, if it is found close it, move it, stop the loop. If it is not found close the workbook, and move to the next file. The following worked just 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 even got ambitious enough to add a progress bar in there so you can see how many files it has to potentially look at, how many it's done, and what file it's looking at right then.

Now this does all assume that you know exactly what the string is going to be (at least a partial) in that cell. If you're wrong, then it doesn't work. Checking for ambiguous things takes much longer, since you can't use Excel's matching function and have to have PowerShell check each cell in the range one at a time.

Upvotes: 6

Related Questions