-
In support of this forum post
https://excelfox.com/forum/showthrea...ll=1#post16376
After
_____ Workbook: Work_file.xlsm ( Using Excel 2007 32 bit )
Row\Col |
A |
B |
C |
D |
E |
F |
G |
1 |
Name |
Project |
Task |
01-22 |
02-22 |
03-22 |
04-22 |
2 |
aaa |
Project1 |
Task1 |
|
|
|
|
3 |
aaa |
Project1 |
Task2 |
|
|
|
|
4 |
aaa |
Project2 |
Task1 |
|
|
|
|
5 |
aaa |
Project2 |
Task2 |
|
|
|
|
6 |
aaa |
Project3 |
Task1 |
|
|
|
|
7 |
aaa |
Project3 |
Task2 |
|
|
500 |
|
8 |
aaa |
Project4 |
Task1 |
|
|
|
|
9 |
aaa |
Project4 |
Task2 |
|
|
|
|
10 |
aaa |
Project5 |
Task1 |
|
|
|
|
11 |
aaa |
Project5 |
Task2 |
|
|
|
|
12 |
bbb |
Project1 |
Task1 |
|
|
|
|
13 |
bbb |
Project1 |
Task2 |
|
|
|
|
14 |
bbb |
Project2 |
Task1 |
|
|
|
|
15 |
bbb |
Project2 |
Task2 |
100 |
|
|
|
16 |
bbb |
Project3 |
Task1 |
|
|
|
|
17 |
bbb |
Project3 |
Task2 |
|
|
|
|
18 |
bbb |
Project4 |
Task1 |
|
|
|
|
19 |
bbb |
Project4 |
Task2 |
|
|
|
|
20 |
bbb |
Project5 |
Task1 |
|
|
|
|
21 |
bbb |
Project5 |
Task2 |
|
|
|
|
22 |
ccc |
Project1 |
Task1 |
|
|
|
|
23 |
ccc |
Project1 |
Task2 |
|
|
|
|
24 |
ccc |
Project2 |
Task1 |
|
|
|
|
25 |
ccc |
Project2 |
Task2 |
|
|
|
|
26 |
ccc |
Project3 |
Task1 |
|
|
|
|
27 |
ccc |
Project3 |
Task2 |
|
|
|
|
28 |
ccc |
Project4 |
Task1 |
|
|
|
|
29 |
ccc |
Project4 |
Task2 |
|
|
|
|
30 |
ccc |
Project5 |
Task1 |
|
200 |
|
|
31 |
ccc |
Project5 |
Task2 |
|
|
|
|
32 |
ddd |
Project1 |
Task1 |
|
|
|
|
Worksheet: Database1
-
In support of this forum post
https://excelfox.com/forum/showthrea...ll=1#post16376
After
_____ Workbook: Work_file.xlsm ( Using Excel 2007 32 bit )
Row\Col |
A |
B |
C |
D |
E |
F |
G |
1 |
Name |
Project |
Task |
01-22 |
02-22 |
03-22 |
04-22 |
2 |
aaa |
Project1 |
Task1 |
|
|
|
|
3 |
aaa |
Project1 |
Task2 |
|
|
|
|
4 |
aaa |
Project2 |
Task1 |
|
|
|
|
5 |
aaa |
Project2 |
Task2 |
|
|
|
|
6 |
aaa |
Project3 |
Task1 |
|
|
|
|
7 |
aaa |
Project3 |
Task2 |
|
|
500 |
|
8 |
aaa |
Project4 |
Task1 |
|
|
|
|
9 |
aaa |
Project4 |
Task2 |
|
|
|
|
10 |
aaa |
Project5 |
Task1 |
|
|
|
|
11 |
aaa |
Project5 |
Task2 |
|
|
|
|
12 |
bbb |
Project1 |
Task1 |
|
|
|
|
13 |
bbb |
Project1 |
Task2 |
|
|
|
|
14 |
bbb |
Project2 |
Task1 |
|
|
|
|
15 |
bbb |
Project2 |
Task2 |
100 |
|
|
|
16 |
bbb |
Project3 |
Task1 |
|
|
|
|
17 |
bbb |
Project3 |
Task2 |
|
|
|
|
18 |
bbb |
Project4 |
Task1 |
|
|
|
|
19 |
bbb |
Project4 |
Task2 |
|
|
|
|
20 |
bbb |
Project5 |
Task1 |
|
|
|
|
21 |
bbb |
Project5 |
Task2 |
|
|
|
|
22 |
ccc |
Project1 |
Task1 |
|
|
|
|
23 |
ccc |
Project1 |
Task2 |
|
|
|
|
24 |
ccc |
Project2 |
Task1 |
|
|
|
|
25 |
ccc |
Project2 |
Task2 |
|
|
|
|
26 |
ccc |
Project3 |
Task1 |
|
|
|
|
27 |
ccc |
Project3 |
Task2 |
|
|
|
|
28 |
ccc |
Project4 |
Task1 |
|
|
|
|
29 |
ccc |
Project4 |
Task2 |
|
|
|
|
30 |
ccc |
Project5 |
Task1 |
|
200 |
|
|
31 |
ccc |
Project5 |
Task2 |
|
|
|
|
32 |
ddd |
Project1 |
Task1 |
|
|
|
|
Worksheet: Database1
-
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
-
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
-
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
-
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
-
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