Windows 10 and Office Excel

Another post for later use

ps1 PowerShell script text like files

Insert some lines without losing what’s there / Retain Comments / Shift what’s there to the right
A fairly simple problem
A common requirement for me is that I want to put some raw code lines into an existing script or VBA code, but I don’t want to change the line numbers referring to where they do in the existing code file.
This might occur if I want to replace some coding, but would like to keep the existing coding for future reference, possibly commented out to the right
What I might do typically for just a few lines is to manually put a comment at the start left of lines to be replaced, and then shift those now comments a few spaces to the right, then start typing the replacement lines from the left, which then pushed those comments to the left
Pictorially this is what I mean for the simple case of wanting to replace just one line

Code:

Sub Original() Dim Msg As String Let Msg = "Hello" MsgBox Prompt:="Hello shit World" End Sub Sub ModifiedByInsertingNewCodeLine() Dim Msg As String Let Msg = "Hello" Debug.Print "Hello shit World" ' MsgBox Prompt:="Hello shit World" End Sub

That’s easy for a few lines, but for a lot of lines it’s a tedious pain in the arse

.ps1 PowerShell text like code lines
Currently have some large .ps1 PowerShell files that I am inserting a lot of lines in and want to do it in the way I am talking about
In this post I will check out the format of the files first, in case there are any unusual “invisible” characters in them, since I have seen that in some PowerShell related text files ( https://excelfox.com/forum/showthrea...ll=1#post15357 https://excelfox.com/forum/showthrea...age3#post15357 )
Here is a File of 250 code line, that I want to put in,
Share ‘blockIPhostsRawAll250.ps1’ https://app.box.com/s/7019x59uvvxt7osvb0tojr0z4g7bfdgk ,
and here is just the first 4 lines for testing here,
Share ‘blockIPhosts4Lines.ps1’ https://app.box.com/s/37pjl9jfk9wue413xewgcjg1cdwk9dwb .
Here is a File I want to put that in from about line 200,
Share ‘Temp7BeforeIPhostsInsert.ps1’ https://app.box.com/s/fttlmwny6y4s5ub1q66kvqbrw2ppxdwz
, and here is just the first 4 lines of that for testing here,
Share ‘Temp7BeforeIPhostsInsertFirst4Lines.ps1’ https://app.box.com/s/bimxv550fpkoqndt8rz421s9vmcsorif .

Simple macro …_

Code:

'Here is a File of 250 code line, that I want to put in, 'Share ‘blockIPhostsRawAll250.ps1’ https://app.box.com/s/7019x59uvvxt7osvb0tojr0z4g7bfdgk , 'and here is just the first 4 lines for testing here, 'Share ‘blockIPhosts4Lines.ps1’ https://app.box.com/s/37pjl9jfk9wue413xewgcjg1cdwk9dwb . 'Here is a File I want to put that in from about line 200, 'Share ‘Temp7BeforeIPhostsInsert.ps1’ https://app.box.com/s/fttlmwny6y4s5ub1q66kvqbrw2ppxdwz ', and here is just the first 4 lines of that for testing here, 'Share ‘Temp7BeforeIPhostsInsertFirst4Lines.ps1’ https://app.box.com/s/bimxv550fpkoqndt8rz421s9vmcsorif . ' ' Sub LookInFirstBitOfTemp4andIPhosts() ' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16366&viewfull=1#post16366 ' Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "test4lines.txt" ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length Get #FileNum, , TotalFile 'Debug.Print TotalFile 'Let TotalFile = Replace(TotalFile, Chr(0), "", 1, -1, vbBinaryCompare) ' https://i.postimg.cc/t43HCQr9/Rather-a-lot-of-Chr-0-s.jpg 'Let TotalFile = Replace(TotalFile, Chr(255) & Chr(254) & vbCr & vbLf, "", 1, 1, vbBinaryCompare) Close #FileNum Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) End Sub

Here is a view in the text editor of those first 4 lines, followed by what my WtchaGot_Unic_NotMuchIfYaChoppedItOff( function gives me
https://i.postimg.cc/n9kqsv63/First4...2ps1-Files.jpg

Code:

Function BlochIPhosts {param([int]$Testie) $Check = $Testie Write-Host "Adding telemetry domains to hosts file" # Write-Output "Adding telemetry domains to hosts file" $hosts_file = "$env:systemroot\System32\drivers\etc\hosts" "Function BlochIPhosts " & Chr(123) & "param" & "(" & Chr(91) & "int" & Chr(93) & "$" & "Testie" & ")" & vbCr & vbLf & " " & "$" & "Check " & "=" & " " & "$" & "Testie" & vbCr & vbLf & "Write" & "-" & "Host " & """" & "Adding telemetry domains to hosts file" & """" & " " & "#" & " Write" & "-" & "Output " & """" & "Adding telemetry domains to hosts file" & """" & vbCr & vbLf & "$" & "hosts" & "_" & "file " & "=" & " " & """" & "$" & "env" & ":" & "systemroot" & "\" & "System32" & "\" & "drivers" & "\" & "etc" & "\" & "hosts" & """" Add-Type -AssemblyName System.Windows.Forms [System.Windows.Forms.Application]::EnableVisualStyles() # Remove-Variable * -ErrorAction SilentlyContinue # is needed or else a removed variable is still there when fucking about with variables. by default variables are persistant. $ErrorActionPreference = 'SilentlyContinue' "Add" & "-" & "Type " & "-" & "AssemblyName System" & "." & "Windows" & "." & "Forms" & vbLf & Chr(91) & "System" & "." & "Windows" & "." & "Forms" & "." & "Application" & Chr(93) & ":" & ":" & "EnableVisualStyles" & "(" & ")" & " " & "#" & vbLf & "Remove" & "-" & "Variable " & Chr(42) & " " & "-" & "ErrorAction SilentlyContinue " & "#" & " is needed or else a removed variable is still there when fucking about with variables" & "." & " by default variables are persistant" & "." & vbLf & "$" & "ErrorActionPreference " & "=" & " " & "'" & "SilentlyContinue" & "'"

Here is a screenshot of the two files in Notepad text editor
https://i.postimg.cc/n9kqsv63/First4...2ps1-Files.jpg

So, no major problems or surprises, ( or so I thought – see next post )
It looks like a different line separator is used sometimes. But we have seen this before.
I have a slight personal tendency to prefer the two character line separator, vbCr & vbLf , and also the first file was the main one in which the other will be inserted, so I will do a simple conversion to the file to be inserted to replace all vbLf with vbCr & vbLf

One small interesting note. I don’t have a last trailing line feed character or characters. That is not so usual, at least I have not experienced it yet so far in playing around with .csv and other text files

I hit a problem later
I hit a problem later…. The full main file actually looked like this in a text editor:
https://i.postimg.cc/4mzznGXy/Actual-Main-File.jpg
and it got me an 9 line array, where I was expecting one of 2102
https://i.postimg.cc/hfFLSvWN/arr-Rws-M-Bollox.jpg

I don’t know how that came about since the four lines file was made and saved in the same ISE environment in which the Main file was and was saved in. But it looks like unfortunately there may be a few, 9 , vbCr & vbLfs.
So, I took a look at a shortened vision of that file, as saved in a text editor, trying to capture some of the problem areas.
This is what I looked at
https://i.postimg.cc/G8VkThB8/Actual...blem-Areas.jpg

Here is the results:

Code:

"Function BlochIPhosts " & Chr(123) & "param" & "(" & Chr(91) & "int" & Chr(93) & "$" & "Testie" & ")" & vbCr & vbLf & " " & "$" & "Check " & "=" & " " & "$" & "Testie" & vbCr & vbLf & "Write" & "-" & "Host " & """" & "Adding telemetry domains to hosts file" & """" & " " & "#" & " Write" & "-" & "Output " & """" & "Adding telemetry domains to hosts file" & """" & vbCr & vbLf & "$" & "hosts" & "_" & "file " & "=" & " " & """" & "$" & "env" & ":" & "systemroot" & "\" & "System32" & "\" & "drivers" & "\" & "etc" & "\" & "hosts" & """" "Add" & "-" & "Type " & "-" & "AssemblyName System" & "." & "Windows" & "." & "Forms" & vbLf & Chr(91) & "System" & "." & "Windows" & "." & "Forms" & "." & "Application" & Chr(93) & ":" & ":" & "EnableVisualStyles" & "(" & ")" & " " & "#" & vbLf & "Remove" & "-" & "Variable " & Chr(42) & " " & "-" & "ErrorAction SilentlyContinue " & "#" & " is needed or else a removed variable is still there when fucking about with variables" & "." & " by default variables are persistant" & "." & vbLf & "$" & "ErrorActionPreference " & "=" & " " & "'" & "SilentlyContinue" & "'" "Add" & "-" & "Type " & "-" & "AssemblyName System" & "." & "Windows" & "." & "Forms " & "#" & " For ps1 in PowerShell running code lines in PowerShell with Admin rights of the following form are what you need to set this off Set" & "-" & "ExecutionPolicy Unrestricted cd " & "'" & "G" & ":" & "\" & "Temp Opt" & "\" & "GitHub" & "\" & "win10script" & "-" & "master" & "\" & "My ps1 file Folder" & "'" & " " & "." & "\" & "win10debloat6Dec" & "-" & "31Dec" & "." & "ps1 " & vbLf & Chr(91) & "System" & "." & "Windows" & "." & "Forms" & "." & "Application" & Chr(93) & ":" & ":" & "EnableVisualStyles" & "(" & ")" & " " & "#" & vbLf & "Remove" & "-" & "Variable " & Chr(42) & " " & "-" & "ErrorAction SilentlyContinue " & "#" & " is needed or else a removed variable is still there when fucking about with variables" & "." & " by default variables are persistant" & "." & vbLf & "$" & "ErrorActionPreference " & "=" & " " & "'" & "SilentlyContinue" & "'" & vbLf & "$" & "wshell " & "=" & " New" & "-" & "Object " & "-" & "ComObject Wscript" & "." & "Shell" & vbLf & "$" & "Button " & "=" & " " & Chr(91) & "System" & "." & "Windows" & "." & "MessageBoxButton" & Chr(93) & ":" & ":" & "YesNoCancel" & vbLf & "$" & "ErrorIco " & "=" & " " & Chr(91) & "System" & "." & "Windows" & "." & "MessageBoxImage" & Chr(93) & ":" & ":" & "Error" & vbLf & "If " & "(" & "!" & "(" & Chr(91) & "Security" & "." & "Principal" & "." & "WindowsPrincipal" & Chr(93) & Chr(91) & "Security" & "." & "Principal" & "." & "WindowsIdentity" & Chr(93) & ":" & ":" & "GetCurrent" & "(" & ")" & ")" & "." & "IsInRole" & "(" & Chr(91) & "Security" & "." & "Principal" & "." & "WindowsBuiltInRolpl" & vbLf & Chr(125) & ")" & vbLf & "$" & "oldcontrolpanel" & "." & "Add" & "_" & "Click" & "(" & Chr(123) & vbLf & " cmd " & "/" & "c control" & vbLf & Chr(125) & ")" & vbLf & "$" & "oldsystempanel" & "." & "Add" & "_" & "Click" & "(" & Chr(123) & vbLf & " cmd " & "/" & "c sysdm" & "." & "cpl" & vbLf & Chr(125 ) & ")" & vbLf & "#" & " " & "$" & "oldpower" & "." & "Add" & "_" & "Click" & "(" & Chr(123) & vbCr & vbLf & vbCr & vbLf & "#" & " " & Chr(125) & ")" & vbLf & "#" & " " & "$" & "restorepower" & "." & "Add" & "_" & "Click" & "(" & Chr(123) & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "#" & " " & Chr(125) & ")" & vbLf & "#" & " " & "$" & "NFS" & "." & "Add" & "_" & "Click" & "(" & Chr(123) & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & "#" & " " & Chr(125) & ")" & vbLf & vbLf & "#" & " " & "$" & "Virtualization" & "." & "Add" & "_" & "Click" & "(" & Chr(123) & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & vbLf & "#" & " " & Chr(125) & ")" & vbLf & vbLf & "$" & "windowsupdatefix" & "." & "Add" & "_" & "Click" & "(" & Chr(123) & vbLf & " Write" & "-" & "Host " & """" & "1" & "." & " Stopping Windows Update Services" & "." & "." & "." & """" & " " & vbLf & " Stop" & "-" & "Service " & "-" & "Name BITS " & vbLf & " " & " "

From that above output we can see 11, so that’s 10 lines rather than 9. This inconsistency by 1 is somewhat worrying.
I looked at the array produced from that shortened text file, and it is as before, 9
One possibility is that some length restriction is long some of the line feed pairs, or a line slipped in at my making of the shortened file

For the masochistic pleasure I tried the full main file in the first simple macro. To my surprise, it worked, after a few hours, the result came out.
Temp7BeforeIPhostsInsertFirst4Lines_ps1WtchaGot https://app.box.com/s/12zw9id42xrwc7qy70ul87bg0wfpb4uh
Temp7BeforeIPhostsInsertFirst4Lines_ps1WtchaGot https://app.box.com/s/771q1gzpycle7ccveu235j256n4l4xyb
It returned 8, making 9 lines.

So that discrepancy is perhaps sorted.
My initial conclusions may have based on slightly careless manipulations that brought some invisible characters in.

One possibility is that the original main coding came from GitHub: This for example talks about the line feed issue at GitHub
http://vcloud-lab.com/entries/devops...pos=0&at_tot=1

This is post https://excelfox.com/forum/showthrea...age8#post16491
https://excelfox.com/forum/showthrea...ll=1#post16491
https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA/page8#post16491
https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16491&viewfull=1#post16491

So I will make a function, just for convenience even though I am not so keen on those things
It will take in
_(i) The two file names , the main one, and then the one to be inserted,
_(ii) the line at which to insert, and optional the number of lines to insert, ( if no number is given, I assume all lines want to be inserted from the second file )
_ an optional name for the output file, ( if none is given I will add something like the date to the name of the original main file, and call it that)

_(iii) Based on the results from the last post, I probably will need to keep my eye on the line feed issue. For now the way to proceed could be to first replace all vbCr & vbLf with vbLf, to get them hopefully all to the same , and then after replace all vbLf with vbCr & vbLf
_ (iv) I assume all files are in the same folder

The techniques used to bring the files into 1 dimensional array of rows are tried and trusted, used many times before. I have the main file in a master array , arrRwsM() , and the file to be merged into it is in the array, arrRws()
At the point of the code development, where these arrays were made, I checked the contents, and there was perfect agreement between the rows in the ps1 files, and the elements of the arrays.

So what is new is to combine, merge, the files as required
A simple loop will do, that tacks on the extra coding from the left in the master code array.
We don’t need to add many spaces, since usually there is already many since the commented lines are over to the right. This is a simple coding bit to do that,
______ arrRwsM(LnNbr - 1) = arrRws(LnNbr - StRw) & arrRwsM(LnNbr - 1)

Code:

For LnNbr = StRw To Rws + StRw - 1 ' This - 1 is the usual “getting end row from start row and count of rows” issue - its always (the start row) + ( Rows count - 1 ) Let arrRwsM(LnNbr - 1) = arrRws(LnNbr - StRw) & arrRwsM(LnNbr - 1) ' The - 1 here is because the one dimensional arrays start at 0, so the lines we are intersted in are 1 element back from where we might have expected them Next LnNbr

Its extremely easy now to remake the modified text file by joining the modified array elements by a line separator,
____ TotalFile = Join(arrRwsM(), vbCr & vbLf)
( For now I will stay with the vbCr & vbLf , as my personal preference )

Code:

' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA/page8#post16491 Sub testieIt() ' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16491&viewfull=1#post16491 Call MergeScriptFiles("Temp7BeforeIPhostsInsert.ps1", "blockIPhostsRawAll250.ps1", 201, , "Temp8.ps1") End Sub ' ByVal TxtM As String, ByVal TxtInst As String, ByVal StRw As Long, Optional ByVal Rws As Long, Optional ByVal FlNmeOut As String Public Function MergeScriptFiles(ByVal TxtM As String, ByVal TxtInst As String, ByVal StRw As Long, Optional ByVal Rws As Long, Optional ByVal FlNmeOut As String) Rem 1 main file ' Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & TxtM ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length Get #FileNum, , TotalFile Close #FileNum 'Debug.Print TotalFile Let TotalFile = Replace(TotalFile, vbCr & vbLf, vbLf, 1, -1, vbBinaryCompare) Let TotalFile = Replace(TotalFile, vbLf, vbCr & vbLf, 1, -1, vbBinaryCompare) ' make a 1 D array of the Main text file Dim arrRwsM() As String: Let arrRwsM() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) Rem 2 file to insert Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & TxtInst ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length Get #FileNum, , TotalFile Close #FileNum 'Debug.Print TotalFile Let TotalFile = Replace(TotalFile, vbCr & vbLf, vbLf, 1, -1, vbBinaryCompare) Let TotalFile = Replace(TotalFile, vbLf, vbCr & vbLf, 1, -1, vbBinaryCompare) ' make a 1 D array of the file to insert Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) Rem 3 tack on lines from file to merge If Rws = 0 Then Let Rws = UBound(arrRws()) + 1 ' This will cause all lines to be merged if no number of lines given Dim LnNbr As Long For LnNbr = StRw To Rws + StRw - 1 ' This - 1 is the usual getting end row from start row and count of rows issue - its always the start row + ( Rows count - 1 ) Let arrRwsM(LnNbr - 1) = arrRws(LnNbr - StRw) & arrRwsM(LnNbr - 1) ' The - 1 here is because the one dimensional arrays start at 0, so the lines we are intersted in are 1 element back from where we might have expected them Next LnNbr ' LnNbr - StRw takes us from 0 to 1 less than our maximumn row number - that is exactly the 0 to (1 less than our maximumn row number) ' 3b That seems to have done it, now we just need to remake the merged text file, ' Make text file, alll we need to do is make the single long string including the line breaks that Let TotalFile = Join(arrRwsM(), vbCr & vbLf) Rem 4 Output file Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName2 As String If FlNmeOut = "" Then Let FlNmeOut = Left(TxtM, InStrRev(TxtM, ".") - 1) & "Merge" & Format(Now(), "dd,mmmyyyy") & ".ps1" Let PathAndFileName2 = ThisWorkbook.Path & "\" & FlNmeOut ' ' CHANGE TO SUIT ' Will be made if not there Open PathAndFileName2 For Output As #FileNum2 Print #FileNum2, TotalFile ' write out entire text file Close #FileNum2 End Function

That above coding has got me my Temp8.ps1 which is Temp7.ps1 with the merged IP hosts block function, along with a few other things done at the end of march, including a first look at a pretty coloured GUI

' Temp7BeforeIPhostsInsert https://app.box.com/s/fttlmwny6y4s5ub1q66kvqbrw2ppxdwz
https://i.postimg.cc/T2K3rjbC/hosts-Before-2022-04.jpg

' blockIPhostsRawAll250 https://app.box.com/s/7019x59uvvxt7osvb0tojr0z4g7bfdgk

https://i.postimg.cc/pV3Xk9Yx/hosts-After-2022-04.jpg
' Temp8.ps1 https://app.box.com/s/9b9li86s7dyysr7exdle30pckgp7vscz

Another post for later use

testing PowerShell Pretty colours

Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath\ 'C:\Users\acer\Desktop\test.txt' -Width 1000

Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath\ 'C:\Users\acer\Desktop\test.txt' -Width 1000

Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath\ 'C:\Users\acer\Desktop\test.txt' -Width 1000

Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath\ 'C:\Users\acer\Desktop\test.txt' -Width 1000

In support of these forum posts
https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776
https://excelfox.com/forum/showthrea...5356#post15356
https://eileenslounge.com/viewtopic.php?f=18&t=37740
https://eileenslounge.com/viewtopic.php?f=18&t=37712
https://eileenslounge.com/viewtopic.php?f=18&t=37707

Code:

'Version : 5.1.19041.1320 'InstanceId : e165cf30-9ddd-49ed-96c7-59cca98516ee 'UI: System.Management.Automation.Internal.Host.InternalHostUserInterface 'CurrentCulture: DE -DE 'CurrentUICulture: DE -DE 'PrivateData: Microsoft.PowerShell.Host.ISE.ISEOptions 'DebuggerEnabled : True 'IsRunspacePushed : False 'Runspace: System.Management.Automation.Runspaces.LocalRunspace 'https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776 'https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=15356#post15356 'https://eileenslounge.com/viewtopic.php?f=18&t=37740 'https://eileenslounge.com/viewtopic.php?f=18&t=37712 'https://eileenslounge.com/viewtopic.php?f=18&t=37707 Sub Services() ' https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776 https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=15356#post15356 ' PowerShell Dim PScmdLet As String, cmdLet As String 'Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\acer\Desktop\test.txt' -Width 1000" Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath '" & ThisWorkbook.Path & Application.PathSeparator & "test.txt' -Width 1000" Let PScmdLet = "powershell -command " & cmdLet ' https://www.devhut.net/vba-run-powershell-command/ CreateObject("WScript.Shell").Exec (PScmdLet) ' Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "test.txt" ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length Get #FileNum, , TotalFile 'Debug.Print TotalFile Let TotalFile = Replace(TotalFile, Chr(0), "", 1, -1, vbBinaryCompare) ' There seems to be a lot of Chr(0)s in the string https://i.postimg.cc/t43HCQr9/Rather-a-lot-of-Chr-0-s.jpg 'Let TotalFile = Replace(TotalFile, Chr(255) & Chr(254) & vbCr & vbLf, "", 1, 1, vbBinaryCompare) ' this would tsake the first bit of crap out, (alternatively we can just take out the first line when split later by Close #FileNum ' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) ' make a 1 D array of the text file lines Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) ' make array for output Dim arrOut() As String: ReDim arrOut(1 To UBound(arrRws()) - 2, 1 To 3) ' we are ignoring the first 3 lines. The UBound of the 1 dimensional array is already 1 less then the lines we need because a 1 dimensional array starts at 0 Dim Cnt As Long For Cnt = 1 To UBound(arrRws()) - 2 If arrRws(Cnt + 2) = "" Then ' This should occur at the last empty rows, so we could consider jumping out of the loop here Else ' time to split the line string Dim Pos1 As Long: Let Pos1 = InStr(1, arrRws(Cnt + 2), " ", vbBinaryCompare) Dim Nme As String: Let Nme = Left(arrRws(Cnt + 2), Pos1 - 1) Dim Pos3 As Long: Let Pos3 = Len(arrRws(Cnt + 2)) - InStrRev(arrRws(Cnt + 2), " ", -1, vbBinaryCompare) Dim StrtTyp As String: Let StrtTyp = Right(arrRws(Cnt + 2), Pos3) Dim DispNme As String: Let DispNme = Replace(arrRws(Cnt + 2), Nme, "", 1, -1, vbBinaryCompare) Let DispNme = Replace(DispNme, StrtTyp, "", 1, -1, vbBinaryCompare) Let DispNme = Trim(DispNme) ' fill the array for output Let arrOut(Cnt, 1) = Nme: arrOut(Cnt, 2) = DispNme: arrOut(Cnt, 3) = StrtTyp End If Next Cnt ' Chuck array into a spreadsheet Let ThisWorkbook.Worksheets("PowerShell").Range("A2").Resize(UBound(arrOut(), 1), 3).Value = arrOut() ThisWorkbook.Worksheets("PowerShell").Cells.Columns("A:C").EntireColumn.AutoFit End Sub

Edit Some issues…
I messed up with a few things.
_ the display name could be long and go up to the startuptype in the text file, which messed up the manipulation of a line of data from the text file a bit. For now I fiddled that by adding some spaces before the words used for the startuptype. A better solution will probably wait until I fully understand the PowerShell code line
_ There seems to be some strange effects with something somewhere working too slow, too fast or not giving accurate information about if a text file is present. For now that is fiddled with some Waits , Dirs and a Kill. That will do for now, but that need to be looked at again when I understand better wots going on

The next code version is in the next post
https://excelfox.com/forum/showthrea...ll=1#post16369

test

Code:

https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page52 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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

HTML Code:

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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

PHP Code:

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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

Code:

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

Macro for this post
https://excelfox.com/forum/showthrea...ge51#post12776

Code:

'In support of these forum posts 'https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776 'https://excelfox.com/forum/showthrea...5356#post15356 'https://eileenslounge.com/viewtopic.php?f=18&t=37740 'https://eileenslounge.com/viewtopic.php?f=18&t=37712 'https://eileenslounge.com/viewtopic.php?f=18&t=37707 Sub Services2() ' https://excelfox.com/forum/showthrea...ge51#post12776 https://excelfox.com/forum/showthrea...5356#post15356 ' kill the text file before I make it, because the code might otherwise use a previous one, as it takes a second or so to overwrite the old or make a new one Do While Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) <> "" If Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) <> "" Then Kill PathName:="" & ThisWorkbook.Path & Application.PathSeparator & "test.txt" Application.Wait (Now + TimeValue("0:00:01")) Loop DoEvents: DoEvents ' Application.Wait (Now + TimeValue("0:00:01")) ' I am not sure why I had to do this. It should not be necerssary, without it the text file is empty - maybe Dir says something is there before it is available to have??? ' PowerShell Dim PScmdLet As String, cmdLet As String 'Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\acer\Desktop\test.txt' -Width 1000" Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath '" & ThisWorkbook.Path & Application.PathSeparator & "test.txt' -Width 2000" Let PScmdLet = "powershell -command " & cmdLet ' https://www.devhut.net/vba-run-powershell-command/ CreateObject("WScript.Shell").Exec (PScmdLet) ' we need to wait a bit until the text file is made Do While Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) = "" Application.Wait (Now + TimeValue("0:00:01")) Loop DoEvents: DoEvents ' I chucked this in, but did not really have any reason Application.Wait (Now + TimeValue("0:00:02")) ' I am not sure why I had to do this. It should not be necerssary, without it the text file is empty - maybe Dir says something is there before it is available to have??? 01 seemed OK - I made it 2 ' Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba...efile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "test.txt" ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length Get #FileNum, , TotalFile 'Debug.Print TotalFile Let TotalFile = Replace(TotalFile, Chr(0), "", 1, -1, vbBinaryCompare) ' There seems to be a lot of Chr(0)s in the string https://i.postimg.cc/t43HCQr9/Rather...of-Chr-0-s.jpg 'Let TotalFile = Replace(TotalFile, Chr(255) & Chr(254) & vbCr & vbLf, "", 1, 1, vbBinaryCompare) ' this would tsake the first bit of crap out, (alternatively we can just take out the first line when split later by Close #FileNum ' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) ' make a 1 D array of the text file lines Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) ' make array for output Dim arrOut() As String: ReDim arrOut(1 To UBound(arrRws()) - 2, 1 To 3) ' we are ignoring the first 3 lines. The UBound of the 1 dimensional array is already 1 less then the lines we need because a 1 dimensional array starts at 0 Dim Cnt As Long For Cnt = 1 To UBound(arrRws()) - 2 If arrRws(Cnt + 2) = "" Then ' This should occur at the last empty rows, so we could consider jumping out of the loop here Else ' This is a bit of a temporary bodge as the Display name sometimes pushes up to the startuptype which screws up the string manipulation below Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Manual", " Manual", 1, 1, vbBinaryCompare): Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Automatic", " Auotomatic", 1, 1, vbBinaryCompare): Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Disabled", " Disabled", 1, 1, vbBinaryCompare) ' time to split the line string Dim Pos1 As Long: Let Pos1 = InStr(1, arrRws(Cnt + 2), " ", vbBinaryCompare) Dim Nme As String: Let Nme = Left(arrRws(Cnt + 2), Pos1 - 1) Dim Pos3 As Long: Let Pos3 = Len(arrRws(Cnt + 2)) - InStrRev(arrRws(Cnt + 2), " ", -1, vbBinaryCompare) Dim StrtTyp As String: Let StrtTyp = Right(arrRws(Cnt + 2), Pos3) Dim DispNme As String: Let DispNme = Replace(arrRws(Cnt + 2), Nme, "", 1, 1, vbBinaryCompare) Let DispNme = Replace(DispNme, StrtTyp, "", 1, 1, vbBinaryCompare) Let DispNme = Trim(DispNme) ' fill the array for output Let arrOut(Cnt, 1) = Nme: arrOut(Cnt, 2) = DispNme: arrOut(Cnt, 3) = StrtTyp End If Next Cnt ' Chuck array into a spreadsheet Let ThisWorkbook.Worksheets("PowerShell").Range("A2").Resize(UBound(arrOut(), 1), 3).Value = arrOut() ThisWorkbook.Worksheets("PowerShell").Cells.Columns("A:C").EntireColumn.AutoFit End Sub

test

Code:

https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page52 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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

HTML Code:

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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

PHP Code:

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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

Code:

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

Macro for this post
https://excelfox.com/forum/showthrea...ge51#post12776

Code:

'In support of these forum posts 'https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776 'https://excelfox.com/forum/showthrea...5356#post15356 'https://eileenslounge.com/viewtopic.php?f=18&t=37740 'https://eileenslounge.com/viewtopic.php?f=18&t=37712 'https://eileenslounge.com/viewtopic.php?f=18&t=37707 Sub Services2() ' https://excelfox.com/forum/showthrea...ge51#post12776 https://excelfox.com/forum/showthrea...5356#post15356 ' kill the text file before I make it, because the code might otherwise use a previous one, as it takes a second or so to overwrite the old or make a new one Do While Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) <> "" If Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) <> "" Then Kill PathName:="" & ThisWorkbook.Path & Application.PathSeparator & "test.txt" Application.Wait (Now + TimeValue("0:00:01")) Loop DoEvents: DoEvents ' Application.Wait (Now + TimeValue("0:00:01")) ' I am not sure why I had to do this. It should not be necerssary, without it the text file is empty - maybe Dir says something is there before it is available to have??? ' PowerShell Dim PScmdLet As String, cmdLet As String 'Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\acer\Desktop\test.txt' -Width 1000" Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath '" & ThisWorkbook.Path & Application.PathSeparator & "test.txt' -Width 2000" Let PScmdLet = "powershell -command " & cmdLet ' https://www.devhut.net/vba-run-powershell-command/ CreateObject("WScript.Shell").Exec (PScmdLet) ' we need to wait a bit until the text file is made Do While Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) = "" Application.Wait (Now + TimeValue("0:00:01")) Loop DoEvents: DoEvents ' I chucked this in, but did not really have any reason Application.Wait (Now + TimeValue("0:00:02")) ' I am not sure why I had to do this. It should not be necerssary, without it the text file is empty - maybe Dir says something is there before it is available to have??? 01 seemed OK - I made it 2 ' Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba...efile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "test.txt" ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length Get #FileNum, , TotalFile 'Debug.Print TotalFile Let TotalFile = Replace(TotalFile, Chr(0), "", 1, -1, vbBinaryCompare) ' There seems to be a lot of Chr(0)s in the string https://i.postimg.cc/t43HCQr9/Rather...of-Chr-0-s.jpg 'Let TotalFile = Replace(TotalFile, Chr(255) & Chr(254) & vbCr & vbLf, "", 1, 1, vbBinaryCompare) ' this would tsake the first bit of crap out, (alternatively we can just take out the first line when split later by Close #FileNum ' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) ' make a 1 D array of the text file lines Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) ' make array for output Dim arrOut() As String: ReDim arrOut(1 To UBound(arrRws()) - 2, 1 To 3) ' we are ignoring the first 3 lines. The UBound of the 1 dimensional array is already 1 less then the lines we need because a 1 dimensional array starts at 0 Dim Cnt As Long For Cnt = 1 To UBound(arrRws()) - 2 If arrRws(Cnt + 2) = "" Then ' This should occur at the last empty rows, so we could consider jumping out of the loop here Else ' This is a bit of a temporary bodge as the Display name sometimes pushes up to the startuptype which screws up the string manipulation below Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Manual", " Manual", 1, 1, vbBinaryCompare): Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Automatic", " Auotomatic", 1, 1, vbBinaryCompare): Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Disabled", " Disabled", 1, 1, vbBinaryCompare) ' time to split the line string Dim Pos1 As Long: Let Pos1 = InStr(1, arrRws(Cnt + 2), " ", vbBinaryCompare) Dim Nme As String: Let Nme = Left(arrRws(Cnt + 2), Pos1 - 1) Dim Pos3 As Long: Let Pos3 = Len(arrRws(Cnt + 2)) - InStrRev(arrRws(Cnt + 2), " ", -1, vbBinaryCompare) Dim StrtTyp As String: Let StrtTyp = Right(arrRws(Cnt + 2), Pos3) Dim DispNme As String: Let DispNme = Replace(arrRws(Cnt + 2), Nme, "", 1, 1, vbBinaryCompare) Let DispNme = Replace(DispNme, StrtTyp, "", 1, 1, vbBinaryCompare) Let DispNme = Trim(DispNme) ' fill the array for output Let arrOut(Cnt, 1) = Nme: arrOut(Cnt, 2) = DispNme: arrOut(Cnt, 3) = StrtTyp End If Next Cnt ' Chuck array into a spreadsheet Let ThisWorkbook.Worksheets("PowerShell").Range("A2").Resize(UBound(arrOut(), 1), 3).Value = arrOut() ThisWorkbook.Worksheets("PowerShell").Cells.Columns("A:C").EntireColumn.AutoFit End Sub

test

Code:

https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page52 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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

HTML Code:

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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

PHP Code:

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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

Code:

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

In support of this post
https://excelfox.com/forum/showthrea...ge51#post12783
The ps1 file, and also below the $WindowsSearch.Add_Click(

Code:

Share ‘ChrisSearchTweaks18-19July.ps1 https://app.box.com/s/cbs7go8i2tdxw4wguthgxcviecaxjn6b iex ((New-Object System.Net.WebClient).DownloadString(' https://raw.githubusercontent.com/ChrisTitusTech/win10script/71609526b132f5cd7e3b9167779af60051a80912/win10debloat.ps1')) $windowssearch.Add_Click({ Write-Host "Disabling Bing Search in Start Menu..." Set-ItemProperty -Path "HKCU:\Software\Microsoft\Windows\CurrentVersion\Search" -Name "BingSearchEnabled" -Type DWord -Value 0 <# Write-Host "Disabling Cortana" Set-ItemProperty -Path "HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Search" -Name "CortanaConsent" -Type DWord -Value 0 If (!(Test-Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\Windows Search")) { New-Item -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\Windows Search" -Force | Out-Null } #> Write-Host "Hiding Search Box / Button..." Set-ItemProperty -Path "HKCU:\Software\Microsoft\Windows\CurrentVersion\Search" -Name "SearchboxTaskbarMode" -Type DWord -Value 0 Write-Host "Removing Start Menu Tiles" Set-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -Value '<LayoutModificationTemplate xmlns:defaultlayout="http://schemas.microsoft.com/Start/2014/FullDefaultLayout" xmlns:start="http://schemas.microsoft.com/Start/2014/StartLayout" Version="1" xmlns="http://schemas.microsoft.com/Start/2014/LayoutModification">' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <LayoutOptions StartTileGroupCellWidth="6" />' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <DefaultLayoutOverride>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <StartLayoutCollection>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <defaultlayout:StartLayout GroupCellWidth="6" />' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' </StartLayoutCollection>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' </DefaultLayoutOverride>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <CustomTaskbarLayoutCollection>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <defaultlayout:TaskbarLayout>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <taskbar:TaskbarPinList>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <taskbar:UWA AppUserModelID="Microsoft.MicrosoftEdge_8wekyb3d8bbwe!MicrosoftEdge" />' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <taskbar:DesktopApp DesktopApplicationLinkPath="%APPDATA%\Microsoft\Windows\Start Menu\Programs\System Tools\File Explorer.lnk" />' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' </taskbar:TaskbarPinList>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' </defaultlayout:TaskbarLayout>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' </CustomTaskbarLayoutCollection>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value '</LayoutModificationTemplate>' $START_MENU_LAYOUT = @" <LayoutModificationTemplate xmlns:defaultlayout="http://schemas.microsoft.com/Start/2014/FullDefaultLayout" xmlns:start="http://schemas.microsoft.com/Start/2014/StartLayout" Version="1" xmlns:taskbar="http://schemas.microsoft.com/Start/2014/TaskbarLayout" xmlns="http://schemas.microsoft.com/Start/2014/LayoutModification"> <LayoutOptions StartTileGroupCellWidth="6" /> <DefaultLayoutOverride> <StartLayoutCollection> <defaultlayout:StartLayout GroupCellWidth="6" /> </StartLayoutCollection> </DefaultLayoutOverride> </LayoutModificationTemplate> "@ $layoutFile="C:\Windows\StartMenuLayout.xml" #Delete layout file if it already exists If(Test-Path $layoutFile) { Remove-Item $layoutFile } #Creates the blank layout file $START_MENU_LAYOUT | Out-File $layoutFile -Encoding ASCII $regAliases = @("HKLM", "HKCU") #Assign the start layout and force it to apply with "LockedStartLayout" at both the machine and user level foreach ($regAlias in $regAliases){ $basePath = $regAlias + ":\SOFTWARE\Policies\Microsoft\Windows" $keyPath = $basePath + "\Explorer" IF(!(Test-Path -Path $keyPath)) { New-Item -Path $basePath -Name "Explorer" } Set-ItemProperty -Path $keyPath -Name "LockedStartLayout" -Value 1 Set-ItemProperty -Path $keyPath -Name "StartLayoutFile" -Value $layoutFile } #Restart Explorer, open the start menu (necessary to load the new layout), and give it a few seconds to process Stop-Process -name explorer Start-Sleep -s 5 $wshell = New-Object -ComObject wscript.shell; $wshell.SendKeys('^{ESCAPE}') Start-Sleep -s 5 #Enable the ability to pin items again by disabling "LockedStartLayout" foreach ($regAlias in $regAliases){ $basePath = $regAlias + ":\SOFTWARE\Policies\Microsoft\Windows" $keyPath = $basePath + "\Explorer" Set-ItemProperty -Path $keyPath -Name "LockedStartLayout" -Value 0 Write-Host "Search and Start Menu Tweaks Complete" } # This was missing 12 July 2021 })

jADHKJASHDKJahdjkAHD

<div class="gmail_chip gmail_drive_chip"> </div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/1UFQySI4QjTTV0O5xnLnLfx5iJh_qDzct/view?usp=drive_web" target="_blank" rel="noopener" aria-label="5.50 8.18 Clean Up Windows 10 _ 3 Steps For A Faster Computer-mWHiP9K8fQ0_16 10 2019.wmv"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" alt="" data-upload="true" /> <span dir="ltr">5.50 8.18 Clean Up Windows 10 _ 3 Steps For A ...</span></a><img src="res/6c654bc40d912309e7cc090257628b4f/texteditor/void.gif" alt="" data-upload="true" /></div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/11mjTzHbcdY1EwVaV0-AIAmYgC2DCsp7X/view?usp=drive_web" target="_blank" rel="noopener" aria-label="7.56 8 (Ransome) How to Make Windows 10 Secure-pGcerfVqYyU_31 01 2020.wmv"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" alt="" data-upload="true" /> <span dir="ltr">7.56 8 (Ransome) How to Make Windows 10 Secur...</span></a><img src="res/6c654bc40d912309e7cc090257628b4f/texteditor/void.gif" alt="" data-upload="true" /></div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/1b_akgpleMvL4lD4qXYffzUEXh8WhUyZ7/view?usp=drive_web" target="_blank" rel="noopener" aria-label="8.45 Speed Up Windows 10 in 2020-8E6OT_QcHaU_20 06 2020.wmv"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" alt="" data-upload="true" /> <span dir="ltr">8.45 Speed Up Windows 10 in 2020-8E6OT_QcHaU_20...</span></a><img src="res/6c654bc40d912309e7cc090257628b4f/texteditor/void.gif" alt="" data-upload="true" /></div>
<div class="gmail_chip gmail_drive_chip"> </div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/1O63RdRCQS2OvWa50F56m6KyryVsBImIK/view?usp=drive_web" target="_blank" rel="noopener" aria-label="8.94 Creating New Windows 10 Debloat Scripts fo..."><img src="https://drive-thirdparty.googleusercontent.com/16/type/video/x-ms-wmv" alt="" data-upload="true" /> <span dir="ltr">8.94 Creating New Windows 10 Debloat Scripts fo...</span></a></div>
<div class="gmail_chip gmail_drive_chip"> </div>

Macro for this post
https://excelfox.com/forum/showthrea...ge51#post12776

Code:

'In support of these forum posts 'https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776 'https://excelfox.com/forum/showthrea...5356#post15356 'https://eileenslounge.com/viewtopic.php?f=18&t=37740 'https://eileenslounge.com/viewtopic.php?f=18&t=37712 'https://eileenslounge.com/viewtopic.php?f=18&t=37707 Sub Services2() ' https://excelfox.com/forum/showthrea...ge51#post12776 https://excelfox.com/forum/showthrea...5356#post15356 ' kill the text file before I make it, because the code might otherwise use a previous one, as it takes a second or so to overwrite the old or make a new one Do While Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) <> "" If Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) <> "" Then Kill PathName:="" & ThisWorkbook.Path & Application.PathSeparator & "test.txt" Application.Wait (Now + TimeValue("0:00:01")) Loop DoEvents: DoEvents ' Application.Wait (Now + TimeValue("0:00:01")) ' I am not sure why I had to do this. It should not be necerssary, without it the text file is empty - maybe Dir says something is there before it is available to have??? ' PowerShell Dim PScmdLet As String, cmdLet As String 'Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\acer\Desktop\test.txt' -Width 1000" Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath '" & ThisWorkbook.Path & Application.PathSeparator & "test.txt' -Width 2000" Let PScmdLet = "powershell -command " & cmdLet ' https://www.devhut.net/vba-run-powershell-command/ CreateObject("WScript.Shell").Exec (PScmdLet) ' we need to wait a bit until the text file is made Do While Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) = "" Application.Wait (Now + TimeValue("0:00:01")) Loop DoEvents: DoEvents ' I chucked this in, but did not really have any reason Application.Wait (Now + TimeValue("0:00:02")) ' I am not sure why I had to do this. It should not be necerssary, without it the text file is empty - maybe Dir says something is there before it is available to have??? 01 seemed OK - I made it 2 ' Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba...efile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "test.txt" ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length Get #FileNum, , TotalFile 'Debug.Print TotalFile Let TotalFile = Replace(TotalFile, Chr(0), "", 1, -1, vbBinaryCompare) ' There seems to be a lot of Chr(0)s in the string https://i.postimg.cc/t43HCQr9/Rather...of-Chr-0-s.jpg 'Let TotalFile = Replace(TotalFile, Chr(255) & Chr(254) & vbCr & vbLf, "", 1, 1, vbBinaryCompare) ' this would tsake the first bit of crap out, (alternatively we can just take out the first line when split later by Close #FileNum ' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) ' make a 1 D array of the text file lines Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) ' make array for output Dim arrOut() As String: ReDim arrOut(1 To UBound(arrRws()) - 2, 1 To 3) ' we are ignoring the first 3 lines. The UBound of the 1 dimensional array is already 1 less then the lines we need because a 1 dimensional array starts at 0 Dim Cnt As Long For Cnt = 1 To UBound(arrRws()) - 2 If arrRws(Cnt + 2) = "" Then ' This should occur at the last empty rows, so we could consider jumping out of the loop here Else ' This is a bit of a temporary bodge as the Display name sometimes pushes up to the startuptype which screws up the string manipulation below Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Manual", " Manual", 1, 1, vbBinaryCompare): Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Automatic", " Auotomatic", 1, 1, vbBinaryCompare): Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Disabled", " Disabled", 1, 1, vbBinaryCompare) ' time to split the line string Dim Pos1 As Long: Let Pos1 = InStr(1, arrRws(Cnt + 2), " ", vbBinaryCompare) Dim Nme As String: Let Nme = Left(arrRws(Cnt + 2), Pos1 - 1) Dim Pos3 As Long: Let Pos3 = Len(arrRws(Cnt + 2)) - InStrRev(arrRws(Cnt + 2), " ", -1, vbBinaryCompare) Dim StrtTyp As String: Let StrtTyp = Right(arrRws(Cnt + 2), Pos3) Dim DispNme As String: Let DispNme = Replace(arrRws(Cnt + 2), Nme, "", 1, 1, vbBinaryCompare) Let DispNme = Replace(DispNme, StrtTyp, "", 1, 1, vbBinaryCompare) Let DispNme = Trim(DispNme) ' fill the array for output Let arrOut(Cnt, 1) = Nme: arrOut(Cnt, 2) = DispNme: arrOut(Cnt, 3) = StrtTyp End If Next Cnt ' Chuck array into a spreadsheet Let ThisWorkbook.Worksheets("PowerShell").Range("A2").Resize(UBound(arrOut(), 1), 3).Value = arrOut() ThisWorkbook.Worksheets("PowerShell").Cells.Columns("A:C").EntireColumn.AutoFit End Sub

test

Code:

https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page52 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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

HTML Code:

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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

PHP Code:

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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

Code:

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

<div class="gmail_chip gmail_drive_chip">
<div class="gmail_chip gmail_drive_chip">
<p style="margin: 0px;"><span style="font-family: arial,helvetica,sans-serif; font-size: 10pt; color: #000000; text-decoration: none;"><a href="https://www.youtube.com/watch?v=5ckOWXGDL34&lc=UgyqZfLMydnVuNbtqTR4AaA BAg.9ZBy1PrRmM89ZEE6b4w03-">https://www.youtube.com/watch?v=5ckOWXGDL34&lc=UgyqZfLMydnVuNbtqTR4AaA BAg.9ZBy1PrRmM89ZEE6b4w03-</a>       7<br /></span></p>
<p style="margin: 0px;"><span style="font-family: arial,helvetica,sans-serif; font-size: 10pt; color: #000000; text-decoration: none;">https://www.youtube.com/watch?v=5ckOWXGDL34&lc=UgzW4-G9Rh2o5ljabrV4AaABAg</span>       13         </p>
</div>

In support of this post https://excelfox.com/forum/showthrea...ge51#post12782
Security tweaks

Code:

# Will like XP or Win7 Disable Windows Defender Disable Defender Updates Set UAC to Never Prompt Disable Meltdown Flag Disable Windows Malware Scan $securitylow.Add_Click({ Write-Host "Lowering UAC level..." Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System" -Name "ConsentPromptBehaviorAdmin" -Type DWord -Value 0 Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System" -Name "PromptOnSecureDesktop" -Type DWord -Value 0 Write-Host "Disabling Windows Defender..." If (!(Test-Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender")) { New-Item -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender" -Force | Out-Null } Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender" -Name "DisableAntiSpyware" -Type DWord -Value 1 If ([System.Environment]::OSVersion.Version.Build -eq 14393) { Remove-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" -Name "WindowsDefender" -ErrorAction SilentlyContinue } ElseIf ([System.Environment]::OSVersion.Version.Build -ge 15063) { Remove-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" -Name "SecurityHealth" -ErrorAction SilentlyContinue } Write-Host "Disabling Windows Defender Cloud..." If (!(Test-Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet")) { New-Item -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet" -Force | Out-Null } Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet" -Name "SpynetReporting" -Type DWord -Value 0 Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet" -Name "SubmitSamplesConsent" -Type DWord -Value 2 Write-Host "Disabling Meltdown (CVE-2017-5754) compatibility flag..." Remove-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\QualityCompat" -Name "cadca5fe-87d3-4b96-b7fb-a231484277cc" -ErrorAction SilentlyContinue Write-Host "Disabling Malicious Software Removal Tool offering..." If (!(Test-Path "HKLM:\SOFTWARE\Policies\Microsoft\MRT")) { New-Item -Path "HKLM:\SOFTWARE\Policies\Microsoft\MRT" | Out-Null } Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\MRT" -Name "DontOfferThroughWUAU" -Type DWord -Value 1 $wshell.Popup("Operation Completed",0,"Done",0x0) })

Code:

# Enable Windows Malware Scan Enable Meltdown Flag Disable Windows Defender Set UAC to Always Prompt Disable Defender Updates $securityhigh.Add_Click({ Write-Host "Raising UAC level..." Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System" -Name "ConsentPromptBehaviorAdmin" -Type DWord -Value 5 Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System" -Name "PromptOnSecureDesktop" -Type DWord -Value 1 Write-Host "Disabling SMB 1.0 protocol..." Set-SmbServerConfiguration -EnableSMB1Protocol $false -Force Write-Host "Enabling Windows Defender..." Remove-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender" -Name "DisableAntiSpyware" -ErrorAction SilentlyContinue If ([System.Environment]::OSVersion.Version.Build -eq 14393) { Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" -Name "WindowsDefender" -Type ExpandString -Value "`"%ProgramFiles%\Windows Defender\MSASCuiL.exe`"" } ElseIf ([System.Environment]::OSVersion.Version.Build -ge 15063) { Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" -Name "SecurityHealth" -Type ExpandString -Value "`"%ProgramFiles%\Windows Defender\MSASCuiL.exe`"" } Write-Host "Enabling Windows Defender Cloud..." Remove-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet" -Name "SpynetReporting" -ErrorAction SilentlyContinue Remove-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet" -Name "SubmitSamplesConsent" -ErrorAction SilentlyContinue Write-Host "Disabling Windows Script Host..." Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows Script Host\Settings" -Name "Enabled" -Type DWord -Value 0 Write-Host "Enabling Meltdown (CVE-2017-5754) compatibility flag..." If (!(Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\QualityCompat")) { New-Item -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\QualityCompat" | Out-Null } Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\QualityCompat" -Name "cadca5fe-87d3-4b96-b7fb-a231484277cc" -Type DWord -Value 0 Write-Host "Enabling Malicious Software Removal Tool offering..." Remove-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\MRT" -Name "DontOfferThroughWUAU" -ErrorAction SilentlyContinue $wshell.Popup("Operation Completed",0,"Done",0x0) })

In support of this post
https://excelfox.com/forum/showthrea...ge51#post12783
The ps1 file, and also below the $WindowsSearch.Add_Click(

Code:

Share ‘ChrisSearchTweaks18-19July.ps1 https://app.box.com/s/cbs7go8i2tdxw4wguthgxcviecaxjn6b iex ((New-Object System.Net.WebClient).DownloadString(' https://raw.githubusercontent.com/ChrisTitusTech/win10script/71609526b132f5cd7e3b9167779af60051a80912/win10debloat.ps1')) $windowssearch.Add_Click({ Write-Host "Disabling Bing Search in Start Menu..." Set-ItemProperty -Path "HKCU:\Software\Microsoft\Windows\CurrentVersion\Search" -Name "BingSearchEnabled" -Type DWord -Value 0 <# Write-Host "Disabling Cortana" Set-ItemProperty -Path "HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Search" -Name "CortanaConsent" -Type DWord -Value 0 If (!(Test-Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\Windows Search")) { New-Item -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows\Windows Search" -Force | Out-Null } #> Write-Host "Hiding Search Box / Button..." Set-ItemProperty -Path "HKCU:\Software\Microsoft\Windows\CurrentVersion\Search" -Name "SearchboxTaskbarMode" -Type DWord -Value 0 Write-Host "Removing Start Menu Tiles" Set-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -Value '<LayoutModificationTemplate xmlns:defaultlayout="http://schemas.microsoft.com/Start/2014/FullDefaultLayout" xmlns:start="http://schemas.microsoft.com/Start/2014/StartLayout" Version="1" xmlns="http://schemas.microsoft.com/Start/2014/LayoutModification">' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <LayoutOptions StartTileGroupCellWidth="6" />' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <DefaultLayoutOverride>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <StartLayoutCollection>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <defaultlayout:StartLayout GroupCellWidth="6" />' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' </StartLayoutCollection>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' </DefaultLayoutOverride>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <CustomTaskbarLayoutCollection>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <defaultlayout:TaskbarLayout>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <taskbar:TaskbarPinList>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <taskbar:UWA AppUserModelID="Microsoft.MicrosoftEdge_8wekyb3d8bbwe!MicrosoftEdge" />' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' <taskbar:DesktopApp DesktopApplicationLinkPath="%APPDATA%\Microsoft\Windows\Start Menu\Programs\System Tools\File Explorer.lnk" />' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' </taskbar:TaskbarPinList>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' </defaultlayout:TaskbarLayout>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value ' </CustomTaskbarLayoutCollection>' Add-Content -Path 'C:\Users\Default\AppData\Local\Microsoft\Windows\Shell\DefaultLayouts.xml' -value '</LayoutModificationTemplate>' $START_MENU_LAYOUT = @" <LayoutModificationTemplate xmlns:defaultlayout="http://schemas.microsoft.com/Start/2014/FullDefaultLayout" xmlns:start="http://schemas.microsoft.com/Start/2014/StartLayout" Version="1" xmlns:taskbar="http://schemas.microsoft.com/Start/2014/TaskbarLayout" xmlns="http://schemas.microsoft.com/Start/2014/LayoutModification"> <LayoutOptions StartTileGroupCellWidth="6" /> <DefaultLayoutOverride> <StartLayoutCollection> <defaultlayout:StartLayout GroupCellWidth="6" /> </StartLayoutCollection> </DefaultLayoutOverride> </LayoutModificationTemplate> "@ $layoutFile="C:\Windows\StartMenuLayout.xml" #Delete layout file if it already exists If(Test-Path $layoutFile) { Remove-Item $layoutFile } #Creates the blank layout file $START_MENU_LAYOUT | Out-File $layoutFile -Encoding ASCII $regAliases = @("HKLM", "HKCU") #Assign the start layout and force it to apply with "LockedStartLayout" at both the machine and user level foreach ($regAlias in $regAliases){ $basePath = $regAlias + ":\SOFTWARE\Policies\Microsoft\Windows" $keyPath = $basePath + "\Explorer" IF(!(Test-Path -Path $keyPath)) { New-Item -Path $basePath -Name "Explorer" } Set-ItemProperty -Path $keyPath -Name "LockedStartLayout" -Value 1 Set-ItemProperty -Path $keyPath -Name "StartLayoutFile" -Value $layoutFile } #Restart Explorer, open the start menu (necessary to load the new layout), and give it a few seconds to process Stop-Process -name explorer Start-Sleep -s 5 $wshell = New-Object -ComObject wscript.shell; $wshell.SendKeys('^{ESCAPE}') Start-Sleep -s 5 #Enable the ability to pin items again by disabling "LockedStartLayout" foreach ($regAlias in $regAliases){ $basePath = $regAlias + ":\SOFTWARE\Policies\Microsoft\Windows" $keyPath = $basePath + "\Explorer" Set-ItemProperty -Path $keyPath -Name "LockedStartLayout" -Value 0 Write-Host "Search and Start Menu Tweaks Complete" } # This was missing 12 July 2021 })

jADHKJASHDKJahdjkAHD

<div class="gmail_chip gmail_drive_chip"> </div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/1UFQySI4QjTTV0O5xnLnLfx5iJh_qDzct/view?usp=drive_web" target="_blank" rel="noopener" aria-label="5.50 8.18 Clean Up Windows 10 _ 3 Steps For A Faster Computer-mWHiP9K8fQ0_16 10 2019.wmv"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" alt="" data-upload="true" /> <span dir="ltr">5.50 8.18 Clean Up Windows 10 _ 3 Steps For A ...</span></a><img src="res/6c654bc40d912309e7cc090257628b4f/texteditor/void.gif" alt="" data-upload="true" /></div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/11mjTzHbcdY1EwVaV0-AIAmYgC2DCsp7X/view?usp=drive_web" target="_blank" rel="noopener" aria-label="7.56 8 (Ransome) How to Make Windows 10 Secure-pGcerfVqYyU_31 01 2020.wmv"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" alt="" data-upload="true" /> <span dir="ltr">7.56 8 (Ransome) How to Make Windows 10 Secur...</span></a><img src="res/6c654bc40d912309e7cc090257628b4f/texteditor/void.gif" alt="" data-upload="true" /></div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/1b_akgpleMvL4lD4qXYffzUEXh8WhUyZ7/view?usp=drive_web" target="_blank" rel="noopener" aria-label="8.45 Speed Up Windows 10 in 2020-8E6OT_QcHaU_20 06 2020.wmv"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" alt="" data-upload="true" /> <span dir="ltr">8.45 Speed Up Windows 10 in 2020-8E6OT_QcHaU_20...</span></a><img src="res/6c654bc40d912309e7cc090257628b4f/texteditor/void.gif" alt="" data-upload="true" /></div>
<div class="gmail_chip gmail_drive_chip"> </div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/1O63RdRCQS2OvWa50F56m6KyryVsBImIK/view?usp=drive_web" target="_blank" rel="noopener" aria-label="8.94 Creating New Windows 10 Debloat Scripts fo..."><img src="https://drive-thirdparty.googleusercontent.com/16/type/video/x-ms-wmv" alt="" data-upload="true" /> <span dir="ltr">8.94 Creating New Windows 10 Debloat Scripts fo...</span></a></div>
<div class="gmail_chip gmail_drive_chip"> </div>

khskjhkshhhfhfhfslhafhalfh

Macro for this post
https://excelfox.com/forum/showthrea...ge51#post12776

Code:

'In support of these forum posts 'https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776 'https://excelfox.com/forum/showthrea...5356#post15356 'https://eileenslounge.com/viewtopic.php?f=18&t=37740 'https://eileenslounge.com/viewtopic.php?f=18&t=37712 'https://eileenslounge.com/viewtopic.php?f=18&t=37707 Sub Services2() ' https://excelfox.com/forum/showthrea...ge51#post12776 https://excelfox.com/forum/showthrea...5356#post15356 ' kill the text file before I make it, because the code might otherwise use a previous one, as it takes a second or so to overwrite the old or make a new one Do While Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) <> "" If Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) <> "" Then Kill PathName:="" & ThisWorkbook.Path & Application.PathSeparator & "test.txt" Application.Wait (Now + TimeValue("0:00:01")) Loop DoEvents: DoEvents ' Application.Wait (Now + TimeValue("0:00:01")) ' I am not sure why I had to do this. It should not be necerssary, without it the text file is empty - maybe Dir says something is there before it is available to have??? ' PowerShell Dim PScmdLet As String, cmdLet As String 'Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\acer\Desktop\test.txt' -Width 1000" Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath '" & ThisWorkbook.Path & Application.PathSeparator & "test.txt' -Width 2000" Let PScmdLet = "powershell -command " & cmdLet ' https://www.devhut.net/vba-run-powershell-command/ CreateObject("WScript.Shell").Exec (PScmdLet) ' we need to wait a bit until the text file is made Do While Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) = "" Application.Wait (Now + TimeValue("0:00:01")) Loop DoEvents: DoEvents ' I chucked this in, but did not really have any reason Application.Wait (Now + TimeValue("0:00:02")) ' I am not sure why I had to do this. It should not be necerssary, without it the text file is empty - maybe Dir says something is there before it is available to have??? 01 seemed OK - I made it 2 ' Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba...efile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "test.txt" ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length Get #FileNum, , TotalFile 'Debug.Print TotalFile Let TotalFile = Replace(TotalFile, Chr(0), "", 1, -1, vbBinaryCompare) ' There seems to be a lot of Chr(0)s in the string https://i.postimg.cc/t43HCQr9/Rather...of-Chr-0-s.jpg 'Let TotalFile = Replace(TotalFile, Chr(255) & Chr(254) & vbCr & vbLf, "", 1, 1, vbBinaryCompare) ' this would tsake the first bit of crap out, (alternatively we can just take out the first line when split later by Close #FileNum ' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) ' make a 1 D array of the text file lines Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) ' make array for output Dim arrOut() As String: ReDim arrOut(1 To UBound(arrRws()) - 2, 1 To 3) ' we are ignoring the first 3 lines. The UBound of the 1 dimensional array is already 1 less then the lines we need because a 1 dimensional array starts at 0 Dim Cnt As Long For Cnt = 1 To UBound(arrRws()) - 2 If arrRws(Cnt + 2) = "" Then ' This should occur at the last empty rows, so we could consider jumping out of the loop here Else ' This is a bit of a temporary bodge as the Display name sometimes pushes up to the startuptype which screws up the string manipulation below Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Manual", " Manual", 1, 1, vbBinaryCompare): Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Automatic", " Auotomatic", 1, 1, vbBinaryCompare): Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Disabled", " Disabled", 1, 1, vbBinaryCompare) ' time to split the line string Dim Pos1 As Long: Let Pos1 = InStr(1, arrRws(Cnt + 2), " ", vbBinaryCompare) Dim Nme As String: Let Nme = Left(arrRws(Cnt + 2), Pos1 - 1) Dim Pos3 As Long: Let Pos3 = Len(arrRws(Cnt + 2)) - InStrRev(arrRws(Cnt + 2), " ", -1, vbBinaryCompare) Dim StrtTyp As String: Let StrtTyp = Right(arrRws(Cnt + 2), Pos3) Dim DispNme As String: Let DispNme = Replace(arrRws(Cnt + 2), Nme, "", 1, 1, vbBinaryCompare) Let DispNme = Replace(DispNme, StrtTyp, "", 1, 1, vbBinaryCompare) Let DispNme = Trim(DispNme) ' fill the array for output Let arrOut(Cnt, 1) = Nme: arrOut(Cnt, 2) = DispNme: arrOut(Cnt, 3) = StrtTyp End If Next Cnt ' Chuck array into a spreadsheet Let ThisWorkbook.Worksheets("PowerShell").Range("A2").Resize(UBound(arrOut(), 1), 3).Value = arrOut() ThisWorkbook.Worksheets("PowerShell").Cells.Columns("A:C").EntireColumn.AutoFit End Sub

test

Code:

https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page52 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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

HTML Code:

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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

PHP Code:

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 occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs

Code:

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

Macro for this post
https://excelfox.com/forum/showthrea...ge51#post12776

Code:

'In support of these forum posts 'https://excelfox.com/forum/showthread.php/2408-Windows-10-and-Office-Excel/page51#post12776 'https://excelfox.com/forum/showthrea...5356#post15356 'https://eileenslounge.com/viewtopic.php?f=18&t=37740 'https://eileenslounge.com/viewtopic.php?f=18&t=37712 'https://eileenslounge.com/viewtopic.php?f=18&t=37707 Sub Services2() ' https://excelfox.com/forum/showthrea...ge51#post12776 https://excelfox.com/forum/showthrea...5356#post15356 ' kill the text file before I make it, because the code might otherwise use a previous one, as it takes a second or so to overwrite the old or make a new one Do While Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) <> "" If Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) <> "" Then Kill PathName:="" & ThisWorkbook.Path & Application.PathSeparator & "test.txt" Application.Wait (Now + TimeValue("0:00:01")) Loop DoEvents: DoEvents ' Application.Wait (Now + TimeValue("0:00:01")) ' I am not sure why I had to do this. It should not be necerssary, without it the text file is empty - maybe Dir says something is there before it is available to have??? ' PowerShell Dim PScmdLet As String, cmdLet As String 'Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\acer\Desktop\test.txt' -Width 1000" Let cmdLet = "Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath '" & ThisWorkbook.Path & Application.PathSeparator & "test.txt' -Width 2000" Let PScmdLet = "powershell -command " & cmdLet ' https://www.devhut.net/vba-run-powershell-command/ CreateObject("WScript.Shell").Exec (PScmdLet) ' we need to wait a bit until the text file is made Do While Dir("" & ThisWorkbook.Path & Application.PathSeparator & "test.txt", vbNormal) = "" Application.Wait (Now + TimeValue("0:00:01")) Loop DoEvents: DoEvents ' I chucked this in, but did not really have any reason Application.Wait (Now + TimeValue("0:00:02")) ' I am not sure why I had to do this. It should not be necerssary, without it the text file is empty - maybe Dir says something is there before it is available to have??? 01 seemed OK - I made it 2 ' Get the text file as a long single string Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba...efile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "test.txt" ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input... Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length Get #FileNum, , TotalFile 'Debug.Print TotalFile Let TotalFile = Replace(TotalFile, Chr(0), "", 1, -1, vbBinaryCompare) ' There seems to be a lot of Chr(0)s in the string https://i.postimg.cc/t43HCQr9/Rather...of-Chr-0-s.jpg 'Let TotalFile = Replace(TotalFile, Chr(255) & Chr(254) & vbCr & vbLf, "", 1, 1, vbBinaryCompare) ' this would tsake the first bit of crap out, (alternatively we can just take out the first line when split later by Close #FileNum ' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) ' make a 1 D array of the text file lines Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) ' make array for output Dim arrOut() As String: ReDim arrOut(1 To UBound(arrRws()) - 2, 1 To 3) ' we are ignoring the first 3 lines. The UBound of the 1 dimensional array is already 1 less then the lines we need because a 1 dimensional array starts at 0 Dim Cnt As Long For Cnt = 1 To UBound(arrRws()) - 2 If arrRws(Cnt + 2) = "" Then ' This should occur at the last empty rows, so we could consider jumping out of the loop here Else ' This is a bit of a temporary bodge as the Display name sometimes pushes up to the startuptype which screws up the string manipulation below Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Manual", " Manual", 1, 1, vbBinaryCompare): Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Automatic", " Auotomatic", 1, 1, vbBinaryCompare): Let arrRws(Cnt + 2) = Replace(arrRws(Cnt + 2), "Disabled", " Disabled", 1, 1, vbBinaryCompare) ' time to split the line string Dim Pos1 As Long: Let Pos1 = InStr(1, arrRws(Cnt + 2), " ", vbBinaryCompare) Dim Nme As String: Let Nme = Left(arrRws(Cnt + 2), Pos1 - 1) Dim Pos3 As Long: Let Pos3 = Len(arrRws(Cnt + 2)) - InStrRev(arrRws(Cnt + 2), " ", -1, vbBinaryCompare) Dim StrtTyp As String: Let StrtTyp = Right(arrRws(Cnt + 2), Pos3) Dim DispNme As String: Let DispNme = Replace(arrRws(Cnt + 2), Nme, "", 1, 1, vbBinaryCompare) Let DispNme = Replace(DispNme, StrtTyp, "", 1, 1, vbBinaryCompare) Let DispNme = Trim(DispNme) ' fill the array for output Let arrOut(Cnt, 1) = Nme: arrOut(Cnt, 2) = DispNme: arrOut(Cnt, 3) = StrtTyp End If Next Cnt ' Chuck array into a spreadsheet Let ThisWorkbook.Worksheets("PowerShell").Range("A2").Resize(UBound(arrOut(), 1), 3).Value = arrOut() ThisWorkbook.Worksheets("PowerShell").Cells.Columns("A:C").EntireColumn.AutoFit End Sub