How to get a list of open Excel workbooks in PowerShell

user_13038892 0
Viewed 301

When I use PowerShell, I only get one (Workbook3) of several window titles (Workbook1, Workbook2, Workbook3), but I want to get the entire list of all open Excel books. I am trying to use the following code:

[array]$Titles = Get-Process | Where-Object {$_.mainWindowTItle} |Foreach-Object {$_.mainwindowtitle}

ForEach ($item in $Titles){Write-Host $item}

enter image description here

UPD. (We get a list of books, but we don't see which ones only to read) If I open the book in read-only mode, it will not be visible in the output of the program. In the Task Manager this mark is in the name of the window.

$excel = [Runtime.Interopservices.Marshal]::
          GetActiveObject('Excel.Application')

ForEach ( $wkb in $excel.workbooks ) {
  $wkb.Name
}

enter image description here

excel powershell
2 Answers

This seems to do the trick:

Clear-Host

$excel = [Runtime.Interopservices.Marshal]::
          GetActiveObject('Excel.Application')

ForEach ( $wkb in $excel.workbooks ) {
  $wkb.Name
}

Sample Output:

PERSONAL.xlsm
Cash Count.xls
Check Calc.xls
Coke Price Comparison Sheet.xls
PS>

HTH

user_137022210

So, my solution:

Clear-Host
Remove-Variable * -ErrorAction SilentlyContinue
$excel = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')
$date = Get-Date -Format "d.MM.y HH:mm"
$person = $env:UserName

#arrays with books and status
ForEach ( $book in $excel.workbooks ) {$books_names = ,$book.Name + $books_names}
ForEach ( $book in $excel.workbooks ) {$books_reads = ,$book.ReadOnly + $books_reads}

#print
#ForEach ($item in $books_names){$item}
#ForEach ($item in $books_reads){$item}

#delete empty string
$books_names = $books_names[0..($books_names.Count-2)]
$books_reads = $books_reads[0..($books_reads.Count-2)]

#replace
$books_reads = $books_reads -replace "False" , "write."
$books_reads = $books_reads -replace "True" , "read"

# Table view
$t = $books_names |%{$i=0}{[PSCustomObject]@{person= $person; date= $date;book=$_; status=$books_reads[$i]};$i++}
#$t | ft
#$t | Out-GridView

# write to txt
Add-Content "C:\My\1.txt" $t | ft

#pause

1.txt looks like:

@{person=BelyaevKN; date=14.06.21 14:05; book=workbook1; status=write}
@{person=BelyaevKN; date=14.06.21 14:05; book=workbook2; status=read}
user_130388920
Related