Windows 10 and Office Excel

[DocAElstein]

Post #502 URL link
https://excelfox.com/forum/showthrea...ge51#post12777
https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12777

---

#501b

Power Shell Services list via Power Shell COM Object

I have been watching a series of videos from Ashish Raj, and here he does something similar with Services in PowerShell via COM Object Excel.Application
https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s

His example is not exactly like my requirement, so I make some notes here of an attempt for a direct comparison.

To refresh what I did before, from Excel
I came up with a single PowerShell script code line to give me all the info I wanted in a text string, that is to say a text file. https://eileenslounge.com/viewtopic....292453#p292453
I did some initial work to check out the exact format of the final text file, which turned out to be a good idea, as there were some “invisible” characters that were best off got rid of.
https://excelfox.com/forum/showthrea...ll=1#post15357
https://excelfox.com/forum/showthrea...age3#post15357
, and here is a final Excel VBA solution that I ended up with
https://excelfox.com/forum/showthrea...l=1#post163689
https://excelfox.com/forum/showthrea...ge52#post16369
Summary of that final macro
There are a few fiddles to do with Waits and related coding in the macro. I don’t yet understand the problems there, so for now, no further comments on those. I concentrate on the main thing the macro is doing. Briefly: It:-
_ Does the PowerShell CmdLet to make a text file with all the info in it
_ Brings the text file into the Excel VBA coding
_ manipulates and pastes the data out in three columns

The final macro in more detail:-
The macro itself performs the PowerShell command
__ Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\acer\Desktop\test.txt' -Width 1000
The macro then brings ( imports ) that text file into VBA as a single string stream, and manipulates the string in standard ways , to organise it into a 2 dimensional VBA array convenient to paste into a worksheet such that it comes out in 3 simple columns of info, as shown in the last post above
https://excelfox.com/forum/showthrea...ge51#post12776

_.________
Script similar to Ashish Raj’s
I am interested in the headings of name,displayname,starttype
Ashish had display name and status. He also collared a cell green or red to indicate running status or stopped status.
I will use my headings, but do a similar colouring.
This is the basic simple coding. ( Note the strange requirement to have an extra () in some places )
Share ‘AshishRajFeedbackCOMObjectExcel.ps1
’https://app.box.com/s/mnvyiln2xqs7elizhjassl1m74trmuve
Share ‘AshishRajFeedbackCOMObjectExcelAlanPreferredFor mat.ps1’
https://app.box.com/s/gjve7vriwgp4vu3q0l0vec0cvobccx9e

Remove-Variable * -ErrorAction SilentlyContinue      #   This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess.    In technical terms: By default variables are persistant.   https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/                                                                                                                                                                                                       https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
#    Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt'    here full line      Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
#              and for Excel example file      C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a  try  section  ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application     #    https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() #  It seems that in PowerShell script an extra  ()  is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname"  ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
    ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString()  ;  $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString()   ;   $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
        If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range(”A1:C1").Font.ColorIndex = 10 }
        elseif($Service.Status -eq "Stopped")  { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
    $rowWS++      }  
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() #  Tidy up column widths
# $excelWB.SaveAs($excelPath)
 $excelWB.Save()     #  It seems that in PowerShell script an extra  ()  is needed
 $excel.Quit()       } # =============================================================================================================
catch   {  Write-Host  "some error occurred"  }  
finally {  $excel.Quit() }    #  this section will be done if an error occurs