Appendix Thread. 3 TEST COPY

In support of this post:
https://www.eileenslounge.com/viewto...289526#p289526




http://www.excelforum.com/excel-prog...ml#post4315748
http://www.excelforum.com/excel-prog...ml#post4316662
http://www.mrexcel.com/forum/excel-q...xcel-file.html
http://www.excelforum.com/excel-prog...ing-excel.html
https://eileenslounge.com/viewtopic....222840#p222840
https://eileenslounge.com/viewtopic....226939#p226939
https://eileenslounge.com/viewtopic....241142#p241142
https://eileenslounge.com/viewtopic....246079#p246079
https://eileenslounge.com/viewtopic....247028#p247028
https://eileenslounge.com/viewtopic....241204#p241204
Range referencing with and without $s https://eileenslounge.com/viewtopic....241197#p241197
https://eileenslounge.com/viewtopic....271415#p271415

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
})

---

Coding for these Threads
https://eileenslounge.com/viewtopic....290229#p290229
https://excelfox.com/forum/showthrea...otes-and-Tests

Code:

---

Option Explicit ' “Window"s is a name for a programming idea which might result in something we “see” as what we conceive as Windows. Manipulating of the actual “Windows” seems the key to pseudo “making my own” InputBox with range selection. Direct linked libraries (dll) are available to run as and when required, hence the wording of direct link: They are used as an efficient means to organise Microsoft’s software generally allowing different Applications to share smaller programs which are shipped as standard with the Microsoft Windows Operating system. They are however also available to programmers , programming the applications. They are usually contained in Folder with name similar to User 32. "API calls”: just means usually that you are using those things and related “Windows” concept-all gets gets bundled up in imprecise intimidating term API, for Application Programming interface
Public Declare Function APIsinUserDLL_MsgBox Lib "user32.dll" Alias "MessageBoxTimeoutA" (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal uType As Long, Optional ByVal wLanguageID As Long, Optional ByVal Delay_ms As Long) As Long
Private Sub WhatsInMyGlobies() '
Dim WndNumber As Long
APIsinUserDLL_MsgBox hWnd:=WndNumber, Prompt:="Using  DateSerial(2021, 12, 1)  inside VBA will give" & vbCr & vbLf & vbCr & DateSerial(2021, 12, 1), Title:="NonModalPopUpThingy", uType:=4, wLanguageID:=0, Delay_ms:=5000  ' ' The error will occur if I do not have a pro open so the MsgBox line will error at ProWb.Name
End Sub
Sub MessinMitdates()
Rem 1 Attempt to get the  sShortDate  from registry via DateSerial( ) implification way
On Error GoTo Bed
Dim TestDateSerial As String
 Let TestDateSerial = DateSerial(9, 3, 4) '    DateSerial(year, month, day)  https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dateserial-function
'  By experimenting, it appears that for the day and month, if the  sShortDate  in the registry needs more than one character then a zero is added to that retuned by  DateSerial(9, 3, 4)  For the  year  any missing  characters seem to be relplaced by  a  0  2  or  1  - For this reason I don't use  1  or  2  for the  day  or  month  becuse if i do , there may be some wrong determination below: We use the number to determine if we have a  day  or  month  or  year
    If InStr(1, TestDateSerial, "9", vbBinaryCompare) = 0 Then MsgBox Prompt:="You have no Year in your short date format": Exit Sub
    If InStr(1, TestDateSerial, "3", vbBinaryCompare) = 0 Then MsgBox Prompt:="You have no Month number in your short date format": Exit Sub
    If InStr(1, TestDateSerial, "4", vbBinaryCompare) = 0 Then MsgBox Prompt:="You have no Day number in your short date format": Exit Sub
Dim C As Variant ' This is each character. It can be a number or letter, so I used  Variant  but string would work also
Dim Cnt As Long: Let Cnt = 1
 Let C = Mid(TestDateSerial, Cnt, 1) ' =========  start of returned string
    Do While IsNumeric(C) = True
    Let Cnt = Cnt + 1  ' we are counting through the characters, (numbers), from the start,  while we have a number. So we are counting the first number section
    Let C = Mid(TestDateSerial, Cnt, 1)
    Loop '  While IsNumeric(C) = True
Dim Cnt1 As Long: Let Cnt1 = Cnt - 1 ' this will be the count of the characters ( numbers ) in the first number section
'  =============================================
Dim Sep1 As String
    Do While IsNumeric(C) = False
    Let Sep1 = Sep1 & C            '  In the first seperator
    Let Cnt = Cnt + 1 ' we are counting through the character in the thing used as the first seperator. (These should not be numbers)
    Let C = Mid(TestDateSerial, Cnt, 1)
    Loop ' While IsNumeric(C) = False
Dim DMY As String ' At this point we have reached the end of the first seperator,
' the next lines search the numbers in the first number section to determine if they are a  day  or  month  or  year
    If InStr(1, Left(TestDateSerial, Cnt - 1), "4", vbBinaryCompare) <> 0 Then: Let DMY = "d"  ' we are actually looking at the first number and first seperator, but never ming
    If InStr(1, Left(TestDateSerial, Cnt - 1), "3", vbBinaryCompare) <> 0 Then Let DMY = "m"
    If InStr(1, Left(TestDateSerial, Cnt - 1), "9", vbBinaryCompare) <> 0 Then Let DMY = "y"
Dim sShortDate As String ' the next line gives us a character string where the fist numbers are replaced by characters representing if they are a  day  or  month  or year
 Let sShortDate = Evaluate("=REPT(""" & DMY & """," & Cnt1 & ")") & Sep1 ' =======  make first bit of output ==
' =====  xx & sep1  ******************************************
Dim Pos2 As Long ' ============================================================================================
Let Pos2 = Len(sShortDate) + 1 '  This should be the position where the second number section starts
    Do While IsNumeric(C) = True
    Let Cnt = Cnt + 1 ' we are counting the characters ( numbers ) in the second ( middle ) character ( number ) section
    Let C = Mid(TestDateSerial, Cnt, 1)
    Loop
Dim Cnt2 As Long: Let Cnt2 = Cnt - Pos2 ' this will be the count of the characters ( numbers ) in the second ( middle )  number section
'  ==========================  at first character in second seperator ========================================
Dim Sep2 As String
    Do While IsNumeric(C) = False
    Let Sep2 = Sep2 & C  '
    Let Cnt = Cnt + 1 ' we are counting through the characters making up the second seperator
    Let C = Mid(TestDateSerial, Cnt, 1)
    Loop ' While IsNumeric(C) = False
'  the  Cnt    C    counting stops at the  start of the last number section
    ' the next lines search the numbers in the second ( middle)  number section to determine if they are a  day  or  month  or  year
'  at this point we have  got the second  seperator string,  Sep2  and are at the start of the last number ===
    If InStr(1, Mid(TestDateSerial, Pos2, Cnt2), "4", vbBinaryCompare) <> 0 Then: Let DMY = "d" ' we are looking in the middle number section
    If InStr(1, Mid(TestDateSerial, Pos2, Cnt2), "3", vbBinaryCompare) <> 0 Then Let DMY = "m"
    If InStr(1, Mid(TestDateSerial, Pos2, Cnt2), "9", vbBinaryCompare) <> 0 Then Let DMY = "y"
  Let sShortDate = sShortDate & Evaluate("=REPT(""" & DMY & """," & Cnt2 & ")") & Sep2 ' this adds to the  sShortDate  string  the characters to indicate the  type ( day month or year )  of the middle section numbers  and the second seperator
'  ==== xx & Sep1 & yy & Sep2  *******************************************
Dim Pos3 As Long: Let Pos3 = Len(sShortDate) + 1 ' this should be the start position of the last number section
Dim Cnt3 As Long        '
 Let Cnt3 = Len(TestDateSerial) - Pos3 + 1 ' this should be the number of characters ( numbers ) in the last number section
 ' we don't bother to loop through the last number section
    ' the next lines search the numbers in the third ( last )  number section to determine if they are a  day  or  month  or  year
    If InStr(1, Mid(TestDateSerial, Pos3, Cnt3), "4", vbBinaryCompare) <> 0 Then: Let DMY = "d"  ' we look from the start  of the last number section,  Pos3  , for a length of its count of its  characters ,  Cnt3
    If InStr(1, Mid(TestDateSerial, Pos3, Cnt3), "3", vbBinaryCompare) <> 0 Then Let DMY = "m"
    If InStr(1, Mid(TestDateSerial, Pos3, Cnt3), "9", vbBinaryCompare) <> 0 Then Let DMY = "y"
 Let sShortDate = sShortDate & Evaluate("=REPT(""" & DMY & """," & Cnt3 & ")") ' finally we add the characters representing the characters to indicate the  type ( day month or year )  of the last section
' ===== xx & Sep1 & yy & Sep2 & zzz  *************************************************************************
' =============================================================================================================
GoTo Rem2
Bed: ' Error handling code section for if the above coding errored
Let sShortDate = "Error getting  sShortDate"
On Error GoTo -1
Rem2: ' Rem 2 Some other computer and user info
On Error Resume Next ' In case info below  is somehow protected
Dim UsrNme As String: Let UsrNme = Application.UserName: Let UsrNme = Environ("username")
Dim CmprNme As String: Let CmprNme = Environ("computername")
Dim WOS As String: Let WOS = Environ("OS")
Dim VersXl As String: Let VersXl = " ( " & ExcelVersion & " ) "
On Error GoTo 0
Rem 3 Shift a space for some info, and some formating
    With Worksheets("Dates")
    .Range("A1:B15").Insert , shift:=xlShiftDown
    .Range("A1:B15").Clear
    .Columns("A").Font.Size = 12
    .Columns("A").ColumnWidth = 16
    .Columns("B").Font.Size = 8
    .Columns("B").ColumnWidth = 190
    End With
   
    With Worksheets("Dates").Range("A1:B1000")
    .Item(1).Value = "  " & UsrNme & "      " & Format(Now(), "dddd, dd, mmm, yyyy.        ") & Format(Now(), "hh") & "hr " & Right(Format(Now(), "hh mm"), 2) & "min" & "      " & VersXl
    .Item(3).Value = sShortDate: .Item(4).Value = " <----  " & CmprNme & "    Registry sShortDate"
    .Item(5).Value = DateSerial(2021, 12, 1): .Item(6).Value = "DateSerial(2021, 12, 1) for 1st dec 2021 was assigned to  .Value  of virgin cell. The  .Value  of the cell is returned as  " & .Item(5).Value & "  The  .Value2  is returned as  " & .Item(5).Value2 & ""
    .Item(7).NumberFormat = "m/d/yyyy"
    .Item(7).Value = DateSerial(2021, 12, 1): .Item(8) = "DateSerial(2021, 12, 1) for 1st Dec 2021 was assigned to  .Value  of cell formated in Short Date, using    .NumberFormat = ""m/d/yyyy""      The  .Value  of the cell is " & .Item(7).Value & "  The  .Value2  is  " & .Item(7).Value2 & ""
    .Item(7).NumberFormat = "m/d/yyyy"
    .Item(9).NumberFormat = "@"
    .Item(9).Value = DateSerial(2021, 12, 1): .Item(10) = "DateSerial(2021, 12, 1) for 1st Dec 2021 was assigned to  .Value  of cell formated in Text, using    .NumberFormat = ""@""  The  .Value  of the cell is " & .Item(9).Value & "  The  .Value2  is  " & .Item(9).Value2 & ""
    .Item(9).NumberFormat = "@"
    Dim DteSerial As String: Let DteSerial = DateSerial(2021, 12, 1)
    .Item(11).Value = DteSerial: .Item(12).Value = "I have not done anything to the format of the cell, its a virgin cell, but I first put what came from  DateSerial(2021, 12, 1)  into a string variable,  DteSerial , and then asigned that string variable to the cell  .Value  The  .Value  of the cell is " & .Item(11).Value & "  The  .Value2  is  " & .Item(11).Value2 & ""
    .Item(13).NumberFormat = "m/d/yyyy"
    .Item(13).Value = DteSerial: .Item(14).Value = "The cell is formattted as short date format using  .NumberFormat = ""m/d/yyyy""  I am assigning the filled variable  Dteserial  to it.  The  .Value  of the cell is " & .Item(13).Value & " the  .Value2  is " & .Item(13).Value2 & ""
    .Item(13).NumberFormat = "m/d/yyyy"
   
    ' Say in a self cancelling meassage box what the  DateSerial(2021, 12, 1)  returns in VBA
    Dim WndNumber As Long:
    APIsinUserDLL_MsgBox hWnd:=WndNumber, Prompt:="Using  DateSerial(2021, 12, 1)  inside VBA will give" & vbCr & vbLf & vbCr & DateSerial(2021, 12, 1), Title:="This will close itself after a few seconds", uType:=4, wLanguageID:=0, Delay_ms:=4000  ' ' The error will occur if I do not have a pro open so the MsgBox line will error at ProWb.Name
    DoEvents: DoEvents
    .Item(17).Value2 = 44531: .Item(17).NumberFormat = "m/d/yyyy": .Item(18).Value = "a  .Value2  of  44531(this is the Excel number for 1stDec,2021)  was put in a virgin cell, which then was given the cell format of Short Date, using    .NumberFormat = ""m/d/yyyy"" "
    .Item(19).Value2 = 44531: .Item(19).NumberFormat = "m\/d\/yyyy": .Item(20).Value = "a  .Value2  of  44531  was put in a virgin cell, which then was given the cell format of    .NumberFormat = ""m\/d\/yyyy"" "
    .Item(21).Value = Format("12    1 2021", "dd_mm_yyyy"): .Item(22).Value = "For a virgin cell I assigned like this  .Item(21).Value = Format(""12    1 2021"", ""dd_mm_yyyy"")    The  .Value frtom the cell is  " & .Item(21).Value & " and the  .Value2  is " & .Item(21).Value2 & ""
    .Item(23).Value = Format(#12/1/2021#, "dd_mm_yyyy"): .Item(24).Value = "For a virgin cell I tried to assign this  .Item(23).Value = Format(#12  - 1  .2021#, ""dd_mm_yyyy"") m  When I hit  Enter  I did not get any error, but that was changed to  Format(#12/1/2021#, ""dd_mm_yyyy"")  What seems to be happeming there is that Excel tries to coerce the string inside a  # pair  into the standard English American format. So it sees  #12/1/2021#  as December 1st, 2021  and gives me the date in the format I ask for.  The  .Value from the cell is " & .Item(23).Value & "  and the  .Value2  is " & .Item(23).Value2 & ""
    .Item(25).Value = Format(#12/1/2021#, "m/d/yyyy"): .Item(26).Value = "For a virgin cell I assigned .Item(25).Value = Format(#12/1/2021#, ""m/d/yyyy"")  The  .Value from the cell is " & .Item(25).Value & " and the  .Value2  is " & .Item(25).Value2 & ""
    .Item(27).Value = Format(#12/1/2021#, "m\/d\/yyyy"): .Item(28).Value = "For a virgin cell I assigned .Item(27).Value = Format(#12/1/2021#, ""m\/d\/yyyy"")  The  .Value from the cell is " & .Item(27).Value & " and the  .Value2  is " & .Item(27).Value2 & ""
    End With
'______________________________________________________________________________________________________________

ThisWorkbook.Save

End Sub
' From Rory's A Tool
Public Function ExcelVersion() As String
    Dim temp                  As String
    'On Error Resume Next
#If Mac Then
    Select Case CLng(Val(Application.Version))
        Case 11: temp = "Excel 2004"
        Case 12: temp = "Excel 2008" ' this should NEVER happen!
        Case 14: temp = "Excel 2011"
        Case 15: temp = "Excel 2016 (Mac)"
        Case Else: temp = "Unknown"
    End Select
#Else
    Select Case CLng(Val(Application.Version))
        Case 9: temp = "Excel 2000"
        Case 10: temp = "Excel 2002"
        Case 11: temp = "Excel 2003"
        Case 12: temp = "Excel 2007"
        Case 14: temp = "Excel 2010"
        Case 15: temp = "Excel 2013"
        Case 16: temp = "Excel 2016, 2019 0r 365 (Windows)"
        Case Else: temp = "Unknown"
    End Select
#End If
#If Win64 Then
    temp = temp & " 64 bit"
#Else
    temp = temp & " 32 bit"
#End If
 Let ExcelVersion = temp
End Function

---

In support of this Thread post
https://eileenslounge.com/viewtopic....290499#p290499


Method a)
This is the way you do it, just in case it is more successful in the future for anything else .
I use here the example of what you are looking for. As you can see, we almost get there, but just crap out at the very last step :(

_1 Go to the Site of the search/ archive engine, web archive org - http://web.archive.org/
That should bring up their “WayBackMachine”
https://i.postimg.cc/XYB2tJgs/Site-web-archive-org.jpg

_ 2 Put that link given from the book in the search bar of their “WayBackMachine”
https://i.postimg.cc/RVFpQGJ5/Put-li...search-bar.jpg
Attachment 3785

_3 Hit Enter
You will then see something like this if it found anything.
https://i.postimg.cc/DZxTzgMv/Lots-o...rcaive-Bot.jpg
In this case it seems to have found a lot, - so at this stage it looks very hopeful

_ 4 Pick one of the years where there are hits shown . That is just guess work – try to think of a year that what you are looking for may have been available. In this next screen shot I chose randomly the year 2003
https://i.postimg.cc/ZYjDdXRx/Chose-...hits-in-it.jpg
Now you will see the dates ringed.
https://i.postimg.cc/q781M5Sn/Ringed...e-was-done.jpg
Those are the dates on which the search/ archive engine, web org, made a random capture of the web site

_ 5 Hover over any of them there ringed dates, and click on a time shown
https://i.postimg.cc/hvCyRQw5/Pick-a-date-and-time.jpg
Attachment 3786

_6 After this you have to take it as it comes and see how far you get.
In this example, I got this, and noticed a link to Examples :
https://i.postimg.cc/3wMLhTSY/Examples-1.jpg
On another attempt after picking a different time and date I got something different but once again found a link to the examples.
https://i.postimg.cc/ncPRd7qs/Examples-2.jpg
Attachment 3787

When I clicked on either of those links I see this, which is still looking hopeful: We are almost there.
https://i.postimg.cc/mkZXnyd9/Almost-there.jpg
Attachment 3788

Unfortunately, every attempt so far by me craps out when I hit that zip link
https://i.postimg.cc/PxQVKRMh/Craps-...st-attempt.jpg
Attachment 3789

I tried a couple of dozen times, and so far it always craps out at the last step. Shame we almost got there- I would take a guess that you won’t find the file this way. When I tried the same with the examples for the Excel book, I got the file on about every second attempt.
As I mentioned it’s a bits hit and miss.
I will look again randomly today when I have a few minutes and let you know if I strike lucky. But I don’t hold out much hope in this case.
Always worth a try. More times than not the web archive works for me. I often use it when passing a link to a current web site and pass the link to the archived stored capture in preference to the actual link because then I know they get what I want them to get. Its invaluable for example, for Microsoft links, since they seem to have nothing better to do then change what actually appears at their help links, some times for the better, sometimes for the worse.

---

Edit
Method b) Frantic net search

Examples.zip - https://app.box.com/s/w75bf0yb8dv875u0myyyg6xxmr9csiw3
RomanWortdBook.dot - https://app.box.com/s/t09e5w0hrr71slos3tlnb5ds9byw5s3p
BobsdBook.doc - https://app.box.com/s/2wwnfuxupruw8jzfmlhlown9trv8yx3t

In support of this Thread
http://www.eileenslounge.com/viewtopic.php?f=41&t=37540


These were the simple steps:
_ Start at free personal account page,( I did give you before ( https://www.box.com/pricing/individual
https://i.postimg.cc/dtqHX8z3/Choose-free-personal.jpg
https://account.box.com/signup/personal?tc=annual ) )

_ https://i.postimg.cc/vTg6c2KX/box-jo...x-password.jpg
You may have some errors if you do not check all boxes
https://i.postimg.cc/W34ZP16B/error-...x-password.jpg
https://i.postimg.cc/0yxCrmYm/error2...x-password.jpg

_ With all boxes checked , it should be successful
https://i.postimg.cc/nLp70ksc/box-jo...x-password.jpg

_ by a successful registration, a confirmation Email will be sent to your given Email address
https://i.postimg.cc/RhFLGTDn/They-s...nfirmation.jpg

The confirmation should arrive at your given Email address, but note it might arrive in spam folder sometimes
https://i.postimg.cc/mZXwk0P3/box-co...ve-in-spam.jpg

_ You must click to confirm your registration
https://i.postimg.cc/x1KMv714/box-co...rify-EMail.jpg

( _ sometime you may receive a warning from your Email provider, but not always
https://i.postimg.cc/Wzh001dG/box-co...il-Warning.jpg )

_ After you confirmation you should arrive at box login
https://i.postimg.cc/FFdp6hFf/Arrive...og-in-page.jpg

_ To login .. first Email address
https://i.postimg.cc/m2n8X0RZ/box-Log-in-EMail.jpg
, then password
https://i.postimg.cc/wjS4vnb4/box-Log-in-password.jpg

_ If all has gone well, you now are logged in to your new account, and can begin using
https://i.postimg.cc/76qVJ419/box-Log-Sucess.jpg

That’s it! You have an account!
_._______________

_ Because I already know all about using, I did skip introduction and set up. But you may chose not to do that
https://i.postimg.cc/Nfs3vhW1/Skipped-Set-up.jpg

_._____________________-

Example Upload and get share link
_New File(s) upload
https://i.postimg.cc/VNqgNvt5/Test-upload-New-File.jpg

( _ you can upload many files at once
https://i.postimg.cc/PrJSMdgF/Upload...es-at-once.jpg )

The files will be uploaded
https://i.postimg.cc/ydKfhcKF/Files-...e-uploaded.jpg
https://i.postimg.cc/HLXZ73qY/All-Fi...e-uploaded.jpg

_ I can get a share link quickly
https://i.postimg.cc/YqDscvNH/I-can-...share-link.jpg

_ I click to copy share link into my clipboard
https://i.postimg.cc/hGQFZ9N6/Copy-s...-Clipboard.jpg


Now that share link is in my clipboard

So finally you have the share link for that File, and can paste it any where
Here is that link:
https://app.box.com/s/gac9uzmfeudlo37hsj4hfim5wb7e3l3z

The peson with that share link can click on it.
Then they will see it and/ or can download it
https://i.postimg.cc/VspVYmYt/I-can-...ownload-it.jpg

Alan


Alan

_._____________________-

P.S. For Image posting share links I also use https://postimages.org/
This does not need any registering. I do use this have for example, for all the image links I did use here.

_ start here: https://postimages.org/
_ https://i.postimg.cc/GtNWPKjX/postimages.jpg

_ Select one or more files to upload https://i.postimg.cc/vZwjQc2x/postim...lect-files.jpg

Files will be uploaded
https://i.postimg.cc/MK1wbkhR/post-images.jpg
https://i.postimg.cc/nhKWMpNR/post-images.jpg

_ you can select what type of link you want
https://i.postimg.cc/SsbjdLgN/post-i...-available.jpg

Finally all links are available for you to copy
https://i.postimg.cc/9zHh51qr/post-i...-available.jpg

In support of this Thread https://excelfox.com/forum/showthrea...ed-on-Criteria
https://excelfox.com/forum/showthread.php/2774-Summarize-Data-from-Dates-to-Months-based-on-Criteria
https://i.postimg.cc/14gZ3Xtb/Case-Tracker.jpg
_____ Workbook: Project Tracker.xlsx ( Using Excel 2007 32 bit )

Row\Col	B	C	D
2	DATE	CONTRACT NO.	STATUS
3	01-Jul-21		NOT STARTED
4	01-Aug-21		IN PROGRESS
5	02-Aug-21		COMPLETE
6	09-Sep-21		ON HOLD
7	21-Oct-21		NOT STARTED
8	22-Oct-21		IN PROGRESS
9	03-Nov-21		COMPLETE
10	05-Nov-21		ON HOLD
11	12-Dec-21		COMPLETE
12	22-Dec-21		ON HOLD

Worksheet: Case Tracker


https://i.postimg.cc/H87GZZRT/Summary.jpg
_____ Workbook: Project Tracker.xlsx ( Using Excel 2007 32 bit )

Row\Col	B	C	D	E	F
2	DATE	NOT STARTED	IN PROGRESS	ON HOLD	COMPLETE
3	Aug-21
4	Sep-21
5	Oct-21
6	Nov-21
7	Dec-21
8	Jan-22
9	Feb-22
10	Mar-22
11	Apr-22
12	May-22
13	Jun-22
14	Jul-22
15	Aug-22
16	Sep-22
17	Oct-22
18	Nov-22
19	Dec-22

Worksheet: Summary

---

results after running macro here https://excelfox.com/forum/showthread.php/2774-Summarize-Data-from-Dates-to-Months-based-on-Criteria?p=16306&viewfull=1#post16306 https://excelfox.com/forum/showthrea...ll=1#post16306
_____ Workbook: Project Tracker.xls ( Using Excel 2007 32 bit )

Row\Col	B	C	D	E	F
2	DATE	NOT STARTED	IN PROGRESS	ON HOLD	COMPLETE
3	Aug-21		1		1
4	Sep-21			1
5	Oct-21	1	1
6	Nov-21			1	1
7	Dec-21			1	1
8	Jan-22
9	Feb-22
10	Mar-22
11	Apr-22
12	May-22
13	Jun-22
14	Jul-22
15	Aug-22
16	Sep-22
17	Oct-22
18	Nov-22
19	Dec-22

Worksheet: Summary

In support of the forum post:

NOT POSTED YET – DRAFT COPY

---

---

Hi
I am new to PowerShell script since a few weeks

I hit my first major coding problem, I have got over some smaller ones.

I have a GUI with lots of buttons on it. Each Button has some various things behind it. Some do some quite major things to the computer, such as registry changes, others download stuff. With one exception all is working as it should**.

Problem Summary

This coding does what it should. I checked it on a few computers with different Windows 10 versions. It checks for installed winget on the computer, and if not there attempts to download it. ( That download might not work for other reasons, but that is a separate issue which I am not concerned with here – as it happens I have it installed on all my computers )
On all my current computers that have winget, the message comes up saying 'winget already installed', and the coding moves on. All is well

Code:

---

Write-Host "Checking winget..." 
if (Test-Path ~\AppData\Local\Microsoft\WindowsApps\winget.exe){  # Check if winget is installed
    'Winget Already Installed'
} 
else{
    # Installing winget from the Microsoft Store
        Write-Host "Winget not found, installing it now."
    $ResultText.text = "`r`n" +"`r`n" + "Installing Winget... Please Wait"
        Start-Process "ms-appinstaller:?source=https://aka.ms/getwinget"  # If I paste that link in Browser URL I get this offered as if I hit the download button somewhere    Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle
        $nid = (Get-Process AppInstaller).Id
        Wait-Process -Id $nid
        Write-Host Winget Installed
    $ResultText.text = "`r`n" +"`r`n" + "Winget Installed - Ready for Next Task"
}  #  })

---

I put that same coding behind a button on a GUI. It seems to work initially, the GUI comes up,
https://i.postimg.cc/xJ3g9CzX/GUI-comes-up.jpg
, and on clicking the button it appears initially to start OK, , but on the same computers, the coding always hangs at
"checking winget…"

Code:

---

#
Add-Type -AssemblyName System.Windows.Forms
# Create a new form
$Form = New-Object system.Windows.Forms.Form
# Define the size
$Form.ClientSize        = '800, 600'

# Range to put button in
$Panel10 = New-Object system.Windows.Forms.Panel ; $Panel10.height = 50 ; $Panel10.width = 250 ; $Panel10.location = New-Object System.Drawing.Point(1, 25)

# function to create sinple botton
function Create-Button {param([string]$Text, [int]$FntSz, [int]$Width, [int]$Height, [int]$ClmX, [int]$RwY)#As Object  ' This function allows us to make a buttons in one line. (Those later single lines do not make the button appear)                                                                     
 $Btn = New-Object System.Windows.Forms.Button                                                                                          #                                                                                             
 $Btn.Text = $Text                                                                            #                                                         
 $Btn.Width = $Width  ;  $Btn.Height = $Height                                                                          #                                                                   
                                                                    #                                                                                   
 $Btn.Location = New-Object System.Drawing.Point($ClmX, $RwY)                                                        #                                                                             
 $Btn.Font = New-Object System.Drawing.Font('Arial', $FntSz)  #  ('Microsoft Sans Serif', 9)
                                                                      #                                                                                 
 return $Btn  }                                                      #                                                                             
# Make button
$GetWinGet = Create-Button -Text "winget" -FntSz 9 -Width 117 -Height 21 -ClmX 3 -RwY 1                                                                                                           



$GetWinGet.Add_Click({                                                                                     
Write-Host "Checking winget..."    # PROBLEM!!!!  This wont work in a button - it freezes here?                                                                     
if (Test-Path ~\AppData\Local\Microsoft\WindowsApps\winget.exe){  # Check if winget is installed           
 'Winget Already Installed'                                                                               
}                                                                                                         
else{                                                                                                       
 # Installing winget from the Microsoft Store
Write-Host "Winget not found, installing it now."                                                           
 $ResultText.text = "`r`n" +"`r`n" + "Installing Winget... Please Wait"                                     
Start-Process "ms-appinstaller:?source=https://aka.ms/getwinget"  # If I paste that link in Browser URL I get this offered as if I hit the download button somewhere    Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle         
$nid = (Get-Process AppInstaller).Id                                                                       
Wait-Process -Id $nid                                                                                       
Write-Host Winget Installed                                                                                 
$ResultText.text = "`r`n" +"`r`n" + "Winget Installed - Ready for Next Task"
}    })                                                                                                   


# Add Button to range
$Panel10.controls.AddRange(@($GetWinGet))

# Add ranbge to Form
$Form.controls.AddRange(@($Panel10))

# Display the form
[void]$Form.ShowDialog()

---

On this same GUI I can put lots of other buttons, all doing different things, and they always do what they should**

What am I missing?


Alan

Test

HTML Code:

---

#
Add-Type -AssemblyName System.Windows.Forms
# Create a new form
$Form = New-Object system.Windows.Forms.Form
# Define the size, title and background color
$Form.ClientSize        = '800, 600'

# Range to put button in
$Panel10 = New-Object system.Windows.Forms.Panel ; $Panel10.height = 50 ; $Panel10.width = 250 ; $Panel10.location = New-Object System.Drawing.Point(1, 25)

# function to create sinple botton
function Create-Button {param([string]$Text, [int]$FntSz, [int]$Width, [int]$Height, [int]$ClmX, [int]$RwY)#As Object  ' This function allows us to make a buttons in one line. (Those later single lines do not make the button appear)                                                                     
 $Btn = New-Object System.Windows.Forms.Button                                                                                          #                                                                                             
 $Btn.Text = $Text                                                                            #                                                         
 $Btn.Width = $Width  ;  $Btn.Height = $Height                                                                          #                                                                   
                                                                    #                                                                                   
 $Btn.Location = New-Object System.Drawing.Point($ClmX, $RwY)                                                        #                                                                             
 $Btn.Font = New-Object System.Drawing.Font('Arial', $FntSz)  #  ('Microsoft Sans Serif', 9)
                                                                      #                                                                                 
 return $Btn  }                                                      #                                                                             
# Make button
$GetWinGet = Create-Button -Text "winget" -FntSz 9 -Width 117 -Height 21 -ClmX 3 -RwY 1                                                                                                            #                                                                $firefox.width                  = 212



$GetWinGet.Add_Click({                                                                                     
Write-Host "Checking winget..."    # PROBLEM!!!!  This wont work in a button - it freezes here?                                                                     
if (Test-Path ~\AppData\Local\Microsoft\WindowsApps\winget.exe){  # Check if winget is installed           
 'Winget Already Installed'                                                                               
}                                                                                                         
else{                                                                                                       
 # Installing winget from the Microsoft Store
Write-Host "Winget not found, installing it now."                                                           
 $ResultText.text = "`r`n" +"`r`n" + "Installing Winget... Please Wait"                                     
Start-Process "ms-appinstaller:?source=https://aka.ms/getwinget"  # If I paste that link in Browser URL I get this offered as if I hit the download button somewhere    Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle            #$vscode.width                    = 211
$nid = (Get-Process AppInstaller).Id                                                                       
Wait-Process -Id $nid                                                                                       
Write-Host Winget Installed                                                                                 
$ResultText.text = "`r`n" +"`r`n" + "Winget Installed - Ready for Next Task"
}    })                                                                                                   


# Add Button to range
$Panel10.controls.AddRange(@($GetWinGet))

# Add ranbge to Form
$Form.controls.AddRange(@($Panel10))

# Display the form
[void]$Form.ShowDialog()

---

PHP Code:

---

 # 
Add-Type -AssemblyName System.Windows.Forms
# Create a new form
$Form = New-Object system.Windows.Forms.Form
# Define the size, title and background color
$Form.ClientSize         = '800, 600'

# Range to put button in
$Panel10 = New-Object system.Windows.Forms.Panel ; $Panel10.height = 50 ; $Panel10.width = 250 ; $Panel10.location = New-Object System.Drawing.Point(1, 25)

# function to create sinple botton
function Create-Button {param([string]$Text, [int]$FntSz, [int]$Width, [int]$Height, [int]$ClmX, [int]$RwY)#As Object   ' This function allows us to make a buttons in one line. (Those later single lines do not make the button appear)                                                                       
 $Btn = New-Object System.Windows.Forms.Button                                                                                           #                                                                                               
 $Btn.Text = $Text                                                                             #                                                           
 $Btn.Width = $Width   ;  $Btn.Height = $Height                                                                           #                                                                     
                                                                     #                                                                                    
 $Btn.Location = New-Object System.Drawing.Point($ClmX, $RwY)                                                        #                                                                              
 $Btn.Font = New-Object System.Drawing.Font('Arial', $FntSz)   #   ('Microsoft Sans Serif', 9)
                                                                       #                                                                                   
 return $Btn   }                                                      #                                                                              
# Make button
$GetWinGet = Create-Button -Text "winget" -FntSz 9 -Width 117 -Height 21 -ClmX 3 -RwY 1                                                                                                            #                                                                 $firefox.width                   = 212



$GetWinGet.Add_Click({                                                                                      
Write-Host "Checking winget..."     # PROBLEM!!!!   This wont work in a button - it freezes here?                                                                       
if (Test-Path ~\AppData\Local\Microsoft\WindowsApps\winget.exe){  # Check if winget is installed            
 'Winget Already Installed'                                                                                 
}                                                                                                           
else{                                                                                                        
 # Installing winget from the Microsoft Store
Write-Host "Winget not found, installing it now."                                                            
 $ResultText.text = "`r`n" +"`r`n" + "Installing Winget... Please Wait"                                      
Start-Process "ms-appinstaller:?source=https://aka.ms/getwinget"   # If I paste that link in Browser URL I get this offered as if I hit the download button somewhere     Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle             #$vscode.width                    = 211
$nid = (Get-Process AppInstaller).Id                                                                         
Wait-Process -Id $nid                                                                                        
Write-Host Winget Installed                                                                                  
$ResultText.text = "`r`n" +"`r`n" + "Winget Installed - Ready for Next Task"
}     })                                                                                                     


# Add Button to range
$Panel10.controls.AddRange(@($GetWinGet)) 

# Add ranbge to Form
$Form.controls.AddRange(@($Panel10))

# Display the form
[void]$Form.ShowDialog() 


---

In support of this forum post
https://excelfox.com/forum/showthrea...ll=1#post16373





_____ Workbook: Work_file.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	S.No.	Year	Month	Name	Project	Task	Amount	Submitted By
2	1	2022	January	bbb	Project2	Task2	100	Liviu Popescu
3	2	2022	February	ccc	Project5	Task1	200	Liviu Popescu
4	3	2022	March	aaa	Project3	Task2	500	Liviu Popescu
5

Worksheet: Database



_____ 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
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
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
31	ccc	Project5	Task2
32	ddd	Project1	Task1
33	ddd	Project1	Task2

Worksheet: Database1

In support of this forum post
https://excelfox.com/forum/showthrea...ll=1#post16376




Before, as we had before


_____ Workbook: Work_file.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	S.No.	Year	Month	Name	Project	Task	Amount	Submitted By
2	1	2022	January	bbb	Project2	Task2	100	Liviu Popescu
3	2	2022	February	ccc	Project5	Task1	200	Liviu Popescu
4	3	2022	March	aaa	Project3	Task2	500	Liviu Popescu
5

Worksheet: Database



_____ 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
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
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
31	ccc	Project5	Task2
32	ddd	Project1	Task1
33	ddd	Project1	Task2

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 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

---

<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&amp;lc=UgyqZfLMydnVuNbtqTR4AaA BAg.9ZBy1PrRmM89ZEE6b4w03-">https://www.youtube.com/watch?v=5ckOWXGDL34&amp;lc=UgyqZfLMydnVuNbtqTR4AaA BAg.9ZBy1PrRmM89ZEE6b4w03-</a> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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&amp;lc=UgzW4-G9Rh2o5ljabrV4AaABAg</span> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </p>
</div>




https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9iHOYYpaAbC
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg. 9h4sd6Vs4qE9h7G-bVm8_-
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg. 9h6VhNCM-DZ9h7EqbG23kg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg. 9h4sd6Vs4qE9h7KvJXmK8o
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg. 9h6VhNCM-DZ9h7E1gwg4Aq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79hNGvJbu
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79YAfa24T
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79M1SYH1E
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78SxhXTnR
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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">&nbsp;</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" />&nbsp;<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" />&nbsp;<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" />&nbsp;<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">&nbsp;</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" />&nbsp;<span dir="ltr">8.94 Creating New Windows 10 Debloat Scripts fo...</span></a></div>
<div class="gmail_chip gmail_drive_chip">&nbsp;</div>

khskjhkshhhfhfhfslhafhalfh

In support of these issues
https://excelfox.com/forum/showthrea...ge51#post12784 https://www.youtube.com/watch?v=dKM8ZScbic8&t=75s
Winget issues https://github.com/ChrisTitusTech/wi...770ae56d3b6e85
https://github.com/ChrisTitusTech/wi...b236f3ecf290db

06.07.2021 The Best Windows Utility ( second nice shade of grey GUI )

Code:

---

Write-Host "Checking winget..."

Try{
        # Check if winget is already installed
        $er = (invoke-expression "winget -v") 2>&1
        if ($lastexitcode) {throw $er}
        Write-Host "winget is already installed."
}
Catch{
        # winget is not installed. Install it from the Github release
        Write-Host "winget is not found, installing it right now."
       
        $download = "https://github.com/microsoft/winget-cli/releases/download/v1.0.11692/Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle"
        $output = $PSScriptRoot + "\winget-latest.appxbundle"
        Write-Host "Dowloading latest release"
        Invoke-WebRequest -Uri $download -OutFile $output
       
        Write-Host "Installing the package"
        Add-AppxPackage -Path $output
}
Finally {
        # Start installing the packages with winget
        #Get-Content .\winget.txt | ForEach-Object {
        #        iex ("winget install -e " + $_)
        #}
}

---

The Ultimate Windows Utility Upgrade 29 09 2021

Code:

---

Write-Host "Checking winget..."

Try{
        # Check if winget is already installed
        $er = (invoke-expression "winget -v") 2>&1
        if ($lastexitcode) {throw $er}
        Write-Host "winget is already installed."
}
Catch{
        # winget is not installed. Install it from the Microsoft Store
        Write-Host "winget is not found, installing it right now."

        Start-Process "ms-appinstaller:?source=https://aka.ms/getwinget"
        $nid = (Get-Process AppInstaller).id
        Wait-Process -Id $nid

}
Finally {
        # Start installing the packages with winget
        #Get-Content .\winget.txt | ForEach-Object {
        #        iex ("winget install -e " + $_)
        #}
}

---

A commit a bit later by mrhaydendp to simplify a bit https://github.com/ChrisTitusTech/wi...0db?diff=split

Code:

---

Write-Host "Checking winget..."

# Check if winget is installed
if (Test-Path ~\AppData\Local\Microsoft\WindowsApps\winget.exe){
    'Winget Already Installed'
} 
else{
    # Installing winget from the Microsoft Store
        Write-Host "Winget not found, installing it now."
        Start-Process "ms-appinstaller:?source=https://aka.ms/getwinget"
        $nid = (Get-Process AppInstaller).Id
        Wait-Process -Id $nid
        Write-Host Winget Installed
}

---

Test



17-121-114-118.applebot.apple.com

header1	header2	A Header	Last Column Header
0	SubItem	SubItem	SubItem	SubItem	<-- This is a ListView Item. It has an Item number of 0 and an Item idenitfier/name of 345
1	SubItem	SubItem	SubItem	SubItem	<-- This is a ListView Item. It has an Item number of 1 and an Item idenitfier/name of 232
2	SubItem	SubItem	SubItem	SubItem	<-- This is a ListView Item. It has an Item number of 2 and an Item idenitfier/name of 36

[size]

header1	header2	A Header	Last Column Header
0	SubItem	SubItem	SubItem	<-- This brown thing is a ListView Item. It has an Item number of 0 and an Item idenitfier/name of 345
1	SubItem	SubItem	SubItem	<-- This blue thing is a ListView Item. It has an Item number of 1 and an Item idenitfier/name of 232
2	SubItem	SubItem	SubItem	<-- This purple thing is a ListView Item. It has an Item number of 2 and an Item idenitfier/name of 36

header1	header2	A Header	Last Header	<-- This bit with the created “column” is part of the main ListView object
0\ 345	SubItem	SubItem	SubItem	<-- This brown thing is a ListViewItem object. It has an Item number of 0, and an Item identifier/name of 345
1\ 232	SubItem	SubItem	SubItem	<-- This blue thing is a ListViewItem object. It has an Item number of 1, and an Item identifier/name of 232
2\ 36	SubItem	SubItem	SubItem	<-- This purple thing is a ListViewItem object. It has an Item number of 2 and an Item identifier/name of 36

__
In the above schematic we are showing 4 objects. The last three belong to the first one, ( after they have been ned to it ).
The values in the first column somehow belong to the main ListView object.
SubItems are Added to the ListViewItems

In support of this Thread https://eileenslounge.com/viewtopic.php?f=30&t=38110
https://eileenslounge.com/viewtopic....294721#p294721



Vertical to Horizontal,
This https://i.postimg.cc/14t4nPfD/This-Virtical.jpg to this https://i.postimg.cc/ygfdQprJ/That-Horizintal.jpg

---

Part 1 The main data Vertical to Horizontal
An idea I have is to build up the single string that we know can be put into the Windows Clipboard, and then pasted out into Excel. ( http://www.eileenslounge.com/viewtop...242941#p242941 )
I basically build that up with some Do While Loopy stuff

The Full Story
The usual worksheets defining and data getting information stuff.
( We capture one extra empty row, because, past experience with these sort of Do While Loopy stuff has shown that it can help simplify some conditional comparison things and/ or help prevent arrays doing out of bounds by one row.


Rem 1
The purpose of this is to get that maximum Amounts or Notes count, ( the biggest group ) ( which is 4 in the given example )
But its worth looking at how that works since the basic Do While Loop is then used in the next main ( Rem 2 ) section.
The #### Main Outer Loop keeps us going through all data rows
Within that the ' ---- Inner Loop that takes us through a group
This loop adds the things in the group, and after each loop is finished we check If the count was the biggest group so far.

Rem 2
This is the main meat of the solution.
First, exactly as before we have a #### Main Outer Loop keeps us going through all data rows

Within that Main Outer Loop we now have 2 inner loops.
'2a
The '2a The first inner loop one does something similar to before. It loops for a group. This time within it we build up two strings that we need for a line in the output.
As example, for the first group we are basically trying to build up these two strings, ( Just before we start that loop, we tack onto the string at the start the group name, which is A in the first group example.
This is what we would see, for example in the immediate window, for querying the string content after, in this example, the the loops for that inner loop

Code:

---

?  strClipL
A  vbTab 10 vbTab     20 vbTab    30 

?    strClipR
  vbTab   N1 vbTab N2 vbTab N3

---

( For the sake of clarity I use a vbTab to indicate the “invisible” vbTab characters, which is actually on those strings )
'2b
The purpose of '2b the second inner loop is to ,( if necessary ), give us effectively extra empty cells, ( achieved by adding a vbTab of the strings.
Using the same example, we would see that the loop is needed to be done once, and at the end of that single loop, our strings are modifies such:

Code:

---

?  strClipL
A  vbTab 10 vbTab    20 vbTab    30  vbTab

?    strClipR
  vbTab   N1 vbTab N2 vbTab N3 vbTab

---

'2c
At this point we combine the two strings and add a line separator so that this row data can be added onto by the next row data
So as to be sure what I have and demonstrate it more clearly, I added a line in testing which calls a function of mine , ( https://excelfox.com/forum/showthrea...ll=1#post15522 ) , which checks that line screen,
Here is the result

Code:

---

"A" & vbTab & "10" & vbTab & "20" & vbTab & "30" & vbTab & vbTab & "N1" & vbTab & "N2" & vbTab & "N3" & vbTab & vbTab & "GroupA" & vbCr & vbLf

---

That looks about correct.

Doing a few other tests, suggest to me that I have the final result that I need: https://i.postimg.cc/xcbJDZgM/StrClip.jpg

Code:

---

? strclip
A  10  20  30      N1  N2  N3      GroupA
B  40  50  60  70  N4  N5  N6  N7  GroupB
C  80              N8              GroupC
D  90  100        N9  N10        GroupD


"A" & vbTab & "10" & vbTab & "20" & vbTab & "30" & vbTab & vbTab & "N1" & vbTab & "N2" & vbTab & "N3" & vbTab & vbTab & "GroupA" & vbCr & vbLf & "B" & vbTab & "40" & vbTab & "50" & vbTab & "60" & vbTab & "70" & vbTab & "N4" & vbTab & "N5" & vbTab & "N6" & vbTab & "N7" & vbTab & "GroupB" & vbCr & vbLf & "C" & vbTab & "80" & vbTab & vbTab & vbTab & vbTab & "N8" & vbTab & vbTab & vbTab & vbTab & "GroupC" & vbCr & vbLf & "D" & vbTab & "90" & vbTab & "100" & vbTab & vbTab & vbTab & "N9" & vbTab & "N10" & vbTab & vbTab & vbTab & "GroupD" & vbCr & vbLf

---

---

' Ref
' http://www.eileenslounge.com/viewtop...=31395#p242941
' http://www.eileenslounge.com/viewtop...=31489#p243731
' http://www.eileenslounge.com/viewtop...=31938#p247681
' http://www.eileenslounge.com/viewtop...art=20#p246887
https://eileenslounge.com/viewtopic....294721#p294721
' http://web.archive.org/web/202001241...ms-dataobject/
' https://stackoverflow.com/questions/...60767#54960767
' https://stackoverflow.com/questions/...12342#59812342
‘ http://web.archive.org/web/202001241...ms-dataobject/


‘ https://www.myonlinetraininghub.com/excel-clipboard https://support.microsoft.com/en-us/...&fromar=1#bm2b
‘ https://www.thespreadsheetguru.com/b...nal-macro-file
‘ https://excelribbon.tips.net/T009810...Clipboard.html
‘ https://www.excelforum.com/excel-pro...t-working.html
‘ https://www.thespreadsheetguru.com/b...-the-clipboard
https://excelribbon.tips.net/T010691...Clipboard.html
https://excel.tips.net/T003111_Cant_...Workbooks.html

' VBA to clear the Office Clipboard http://www.eileenslounge.com/viewtop...634c64#p246838

Coding so far , for last post, https://excelfox.com/forum/showthrea...ll=1#post16529

Code:

---

'  https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16529&viewfull=1#post16529
'  http://www.eileenslounge.com/viewtopic.php?f=30&t=38110&p=294692#p294692
Sub Stantial()
Rem 0 data
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim RngPlus1 As Range
 Set RngPlus1 = Ws1.Cells.Item(1).CurrentRegion.Resize(Ws1.Cells.Item(1).CurrentRegion.Rows.Count + 1, Ws1.Cells.Item(1).CurrentRegion.Columns.Count)
Dim vArr() As Variant: Let vArr() = RngPlus1.Value2
Rem 1 determine the biggest group ( that maximum Amounts or Notes count )
Dim Cnt As Long, Cnt2 As Long, Mx As Long: Let Mx = 1: Let Cnt = 1
    Do  '            ############################# Main Outer Loop keeps us going through all data rows
        Do  '                          -----------------  Inner Loop that takes us through a group
        Let Cnt = Cnt + 1 ' Cnt is the main data row number
        Let Cnt2 = Cnt2 + 1
        Loop While vArr(Cnt + 1, 1) = vArr(Cnt, 1) ' ----  Inner Loop that takes us through a group
        If Cnt2 > Mx Then Let Mx = Cnt2
    Let Cnt2 = 0
    Loop While Cnt < UBound(vArr(), 1) - 1 '  #### Main Outer Loop keeps us going through all data rows

Rem 2 '            ############################# Main Outer Loop keeps us going through all data rows
 Let Cnt = 1
    Do
    Dim HrCnt As Long: Let HrCnt = 1
    Dim strClipR As String, strClipL As String: Let strClipL = strClipL & vArr(Cnt + 1, 1)
        Do                                        '2a The first inner loop
        Let Cnt = Cnt + 1
        Let HrCnt = HrCnt + 1
        Let strClipL = strClipL & vbTab & vArr(Cnt, 2)
        Let strClipR = strClipR & vbTab & vArr(Cnt, 3)
        Loop While vArr(Cnt + 1, 1) = vArr(Cnt, 1) '  The first inner loop
        Do While HrCnt < Mx + 1                    '2b the second inner loop
        Let strClipL = strClipL & vbTab
        Let strClipR = strClipR & vbTab
        Let HrCnt = HrCnt + 1
        Loop                                      '  the second inner loop
    '2c Finishing off the strings, and final string for an output line, after the inner loops
    Let strClipR = strClipR & vbTab & vArr(Cnt, 4) ' add the group name
    Dim strClip As String: Let strClip = strClip & strClipL & strClipR & vbCr & vbLf  ' join the strings and add a line seperator to the output row string
    'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(strClip)
    Let strClipL = "": strClipR = ""
    Loop While Cnt < UBound(vArr(), 1) - 1 '  #### Main Outer Loop keeps us going through all data rows
 'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(strClip)
'2d paste  strClip  out via the windows Clipboard
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")  '    http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
 objDataObject.SetText Text:=strClip
 objDataObject.PutInClipboard
 Ws1.Paste Destination:=Ws1.Range("G2")

End Sub


---

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9iHOYYpaAbC
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg. 9h4sd6Vs4qE9h7G-bVm8_-
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg. 9h6VhNCM-DZ9h7EqbG23kg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg. 9h4sd6Vs4qE9h7KvJXmK8o
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg. 9h6VhNCM-DZ9h7E1gwg4Aq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79hNGvJbu
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79YAfa24T
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h79M1SYH1E
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78SxhXTnR
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Spare post for later

Row\Col	A	B	C	D
1	Numbers	ID	Date
2	12345	22	01/03/2022
3	12345	22	01/03/2022
4	12345	22	01/03/2022
5	12345	22	01/03/2022
6	12345	22	01/05/2022
7	12345	22	01/05/2022
8	12345	22	01/06/2022
9	12345	22	01/06/2022
10	12345	22	04/02/2022
11	12345	22	04/02/2022
12	12345	22	04/02/2022
13	12345	22	04/03/2022
14	12345	22	04/03/2022
15	12345	22	04/04/2022
16	23456	22	01/03/2022
17	23456	22	01/03/2022
18	23456	22	01/03/2022
19	23456	22	01/04/2022
20	23456	22	01/04/2022
21

Worksheet: Data

And this is your results.

Row\Col	A	B	C	D	E	F	G
1	Number	ID	Start Date	End Date	Days	Working Days
2	12345	22	01/03/2022	04/04/2022	92	64	Incorrect
3	23456	22	01/03/2022	01/04/2022	2	1
4
5
6
7	12345	22	01/03/2022	01/06/2022	4	3	Correct
8	12345	22	04/02/2022	04/04/2022	3	0

Worksheet: Result

It is impossible to understand why you have more than one result for 12345

Following on from posts,
https://excelfox.com/forum/showthrea...ll=1#post16530 https://excelfox.com/forum/showthrea...ll=1#post16529
http://www.eileenslounge.com/viewtop...294692#p294692
,

The header row,
Group Amount1 Amount2 Amount3 Amount4 Notes1 Notes2 Notes3 Notes4 Name
, we could make partially dynamic, as is needed, since we don’t know the maximum number of amounts ( = maximum number of Notes ) , before seeing the data.

We do have the information needed, since Mx contains, in our current example, the required value of 4

Evaluate Range techniques are a convenient way to get these sort of things.

We start by considering spreadsheet formulas such as this,
={"Amount" & COLUMN(A1:D1)}
, which returns us an array, which applied across a range , would give us like
Amount1 Amount2 Amount3 Amount4 https://i.postimg.cc/vxWK4VnG/Amount...ay-Formula.jpg

Taking that general idea and a few other steps we can finally get at our heading like in this demo coding

Code:

---

' https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16532&viewfull=1#post16532
Sub MakeHeadings()
Dim Mx As Long: Let Mx = 4
Dim Amounts() As Variant
 Let Amounts() = Evaluate("=""Amount"" & COLUMN(A1:D1)")
 Let Amounts() = Evaluate("=""Amount"" & COLUMN(A:D)")
 Let Amounts() = Evaluate("=""Amount"" & COLUMN(A:" & "D" & ")")
' We need to get  D  from what we know,  Mx
Dim vTemp As Variant
 vTemp = Cells(1, 4).Address
 vTemp = Split(vTemp, "$", 3, vbBinaryCompare)
 vTemp = vTemp(1)
' Or
 vTemp = Split(Cells(1, 4).Address, "$", 3, vbBinaryCompare)(1)
' Or
 vTemp = Split(Cells(1, 4).Address, "$")(1)
 vTemp = Split(Cells(1, Mx).Address, "$")(1)

Let Amounts() = Evaluate("=""Amount"" & COLUMN(A:" & vTemp & ")")
Let Amounts() = Evaluate("=""Amount"" & COLUMN(A:" & Split(Cells(1, Mx).Address, "$")(1) & ")")
'
' We want this array as a string with  vbTabs seperating the array elements
Dim strAmounts As String
 Let strAmounts = Join(Amounts(), vbTab)
 Let strAmounts = Join(Evaluate("=""Amount"" & COLUMN(A:" & Split(Cells(1, Mx).Address, "$")(1) & ")"), vbTab)

' similarly for the  notes
Dim strNotes As String
 Let strNotes = Join(Evaluate("=""Note"" & COLUMN(A:" & Split(Cells(1, Mx).Address, "$")(1) & ")"), vbTab)

' To get our final heading string,
Dim strHd As String
 Let strHd = "Group" & vbTab & Join(Evaluate("=""Amount"" & COLUMN(A:" & Split(Cells(1, Mx).Address, "$")(1) & ")"), vbTab) & vbTab & Join(Evaluate("=""Note"" & COLUMN(A:" & Split(Cells(1, Mx).Address, "$")(1) & ")"), vbTab) & vbTab & "Notes"

Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")  '    http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
 objDataObject.SetText Text:=strHd
 objDataObject.PutInClipboard
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
 Ws1.Paste Destination:=Ws1.Range("G1")

End Sub

---

In the next post , https://excelfox.com/forum/showthrea...ll=1#post16533 , is that integrated into the main coding in Rem 3

Coding for these posts
https://excelfox.com/forum/showthrea...ll=1#post16532

Code:

---

Sub Stantially()
Rem 0 data
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim RngPlus1 As Range
 Set RngPlus1 = Ws1.Cells.Item(1).CurrentRegion.Resize(Ws1.Cells.Item(1).CurrentRegion.Rows.Count + 1, Ws1.Cells.Item(1).CurrentRegion.Columns.Count)
Dim vArr() As Variant: Let vArr() = RngPlus1.Value2
Rem 1 determine the biggest group ( that maximum Amounts or Notes count )
Dim Cnt As Long, Cnt2 As Long, Mx As Long: Let Mx = 1: Let Cnt = 1
    Do  '            ############################# Main Outer Loop keeps us going through all data rows
        Do  '                          -----------------  Inner Loop that takes us through a group
        Let Cnt = Cnt + 1 ' Cnt is the main data row number
        Let Cnt2 = Cnt2 + 1
        Loop While vArr(Cnt + 1, 1) = vArr(Cnt, 1) ' ----  Inner Loop that takes us through a group
        If Cnt2 > Mx Then Let Mx = Cnt2
    Let Cnt2 = 0
    Loop While Cnt < UBound(vArr(), 1) - 1 '  #### Main Outer Loop keeps us going through all data rows

Rem 2 '            ############################# Main Outer Loop keeps us going through all data rows
 Let Cnt = 1
    Do
    Dim HrCnt As Long: Let HrCnt = 1
    Dim strClipR As String, strClipL As String: Let strClipL = strClipL & vArr(Cnt + 1, 1)
        Do                                        '2a The first inner loop
        Let Cnt = Cnt + 1
        Let HrCnt = HrCnt + 1
        Let strClipL = strClipL & vbTab & vArr(Cnt, 2)
        Let strClipR = strClipR & vbTab & vArr(Cnt, 3)
        Loop While vArr(Cnt + 1, 1) = vArr(Cnt, 1) '  The first inner loop
        Do While HrCnt < Mx + 1                    '2b the second inner loop
        Let strClipL = strClipL & vbTab
        Let strClipR = strClipR & vbTab
        Let HrCnt = HrCnt + 1
        Loop                                      '  the second inner loop
    '2c Finishing off the strings, and final string for an output line, after the inner loops
    Let strClipR = strClipR & vbTab & vArr(Cnt, 4) ' add the group name
    Dim strClip As String: Let strClip = strClip & strClipL & strClipR & vbCr & vbLf  ' join the strings and add a line seperator to the output row string
    'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(strClip)
    Let strClipL = "": strClipR = ""
    Loop While Cnt < UBound(vArr(), 1) - 1 '  #### Main Outer Loop keeps us going through all data rows
 'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(strClip)
'2d paste  strClip  out via the windows Clipboard
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")  '    http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
 objDataObject.SetText Text:=strClip
 objDataObject.PutInClipboard
 Ws1.Paste Destination:=Ws1.Range("G2")

Rem 3 headers
Dim strHd As String
 Let strHd = "Group" & vbTab & Join(Evaluate("=""Amount"" & COLUMN(A:" & Split(Cells(1, Mx).Address, "$")(1) & ")"), vbTab) & vbTab & Join(Evaluate("=""Note"" & COLUMN(A:" & Split(Cells(1, Mx).Address, "$")(1) & ")"), vbTab) & vbTab & "Name"
 objDataObject.SetText Text:=strHd
 objDataObject.PutInClipboard
 Ws1.Paste Destination:=Ws1.Range("G1")

End Sub


---

Some extra notes for this main forum post:
http://www.eileenslounge.com/viewtopic.php?f=27&t=38331

This is a sample input,

_____ Workbook: Split- Copy.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B
2	1,2,3,4	t,y,u,m

Worksheet: Sheet2Original

This is what I want out

_____ Workbook: Split- Copy.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B
2	1	t
3	2	y
4	3	u
5	4	m

Worksheet: Sheet2


I want to do this sort of thing,

__ arrOut()= App.Index(arrIn(), Rws(), Clms())

The arrIn() in this case will be all the input data. Conveniently, we can join the two cell values with a comma then split all that by comma to get a single array, {1 2 3 4 t y u m }
Then we need the Rws() like this
1 1
1 1
1 1
1 1
and the Clms() like this
1 5
2 6
3 7
4 8

Code:

---

'  https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16639&viewfull=1#post16639
Sub SplitData4()
Dim Ws2 As Worksheet: Set Ws2 = ThisWorkbook.Worksheets.Item("Sheet2")
Dim strDta As String: Let strDta = Ws2.Range("A2").Value & "," & Ws2.Range("B2").Value
Dim arrIn() As String
 Let arrIn() = Split(strDta, ",", -1, vbBinaryCompare)
' Or
 arrIn() = Split(Range("A2").Value & "," & Range("B2").Value, ",")
Dim Rws() As Variant
 Let Rws() = Evaluate("=Row(1:4)/Row(1:4)*Column(A:B)/Column(A:B)")
 Let Rws() = Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")/Row(1:" & (UBound(arrIn()) + 1) / 2 & ")*Column(A:B)/Column(A:B)")
Dim Clms() As Variant
 Let Clms() = Evaluate("=Row(1:4)+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")")
 Let Clms() = Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")")

Dim arrOut() As Variant
 Let arrOut() = Application.Index(arrIn(), Rws(), Clms())

 Let Ws2.Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = arrOut()

' Or
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = arrOut()
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = Application.Index(arrIn(), Rws(), Clms())
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = Application.Index(arrIn(), Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")/Row(1:" & (UBound(arrIn()) + 1) / 2 & ")*Column(A:B)/Column(A:B)"), Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")"))

 Range("A2").Resize((UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2, 2).Value = Application.Index(Split(Range("A2").Value & "," & Range("B2").Value, ","), Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")/Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")*Column(A:B)/Column(A:B)"), Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")"))
 
End Sub

---

Code:

---

Sub StantiallyBeautiful() '  http://www.eileenslounge.com/viewtopic.php?f=27&t=38331
 Range("A2").Resize((UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2, 2).Value = Application.Index(Split(Range("A2").Value & "," & Range("B2").Value, ","), Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")/Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")*Column(A:B)/Column(A:B)"), Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")"))
End Sub

---

---

In actual fact, we can simplify things a bit , since Intersexual interception theory tells us that if Excel is looking for the indicies of this form
A b
C d
E f
G h
, but we only give it
1
, then it will see this instead
1 1
1 1
1 1
1 1

So that means we can replace Rws() with just 1
So that all simplifies it a bit…

Code:

---

Sub SplitData4b()
Dim Ws2 As Worksheet: Set Ws2 = ThisWorkbook.Worksheets.Item("Sheet2")
Dim strDta As String: Let strDta = Ws2.Range("A2").Value & "," & Ws2.Range("B2").Value
Dim arrIn() As String
 Let arrIn() = Split(strDta, ",", -1, vbBinaryCompare)
' Or
 arrIn() = Split(Range("A2").Value & "," & Range("B2").Value, ",")
'Dim Rws() As Variant
' Let Rws() = Evaluate("=Row(1:4)/Row(1:4)*Column(A:B)/Column(A:B)")
' Let Rws() = Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")/Row(1:" & (UBound(arrIn()) + 1) / 2 & ")*Column(A:B)/Column(A:B)")
Dim Clms() As Variant
 Let Clms() = Evaluate("=Row(1:4)+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")")
 Let Clms() = Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")")

Dim arrOut() As Variant
' Let arrOut() = Application.Index(arrIn(), Rws(), Clms())
 Let arrOut() = Application.Index(arrIn(), 1, Clms())
 Let Ws2.Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = arrOut()

' Or
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = arrOut()
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = Application.Index(arrIn(), 1, Clms())
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = Application.Index(arrIn(), 1, Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")"))

 Range("A2").Resize((UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2, 2).Value = Application.Index(Split(Range("A2").Value & "," & Range("B2").Value, ","), 1, Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")"))
End Sub

Sub StantiallyBeautifulb() '  http://www.eileenslounge.com/viewtopic.php?f=27&t=38331
 Range("A2").Resize((UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2, 2).Value = Application.Index(Split(Range("A2").Value & "," & Range("B2").Value, ","), 1, Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")"))
End Sub

---