Page 91 of 94 FirstFirst ... 41818990919293 ... LastLast
Results 901 to 910 of 935

Thread: Windows 10 and Office Excel

  1. 02-09-2022, 11:45 PM #901
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    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)
})
    Reply With Quote Reply With Quote
  2. 02-09-2022, 11:45 PM #902
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    khskjhkshhhfhfhfslhafhalfh
    Reply With Quote Reply With Quote
  3. 02-09-2022, 11:45 PM #903
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    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
    Reply With Quote Reply With Quote
  4. 02-09-2022, 11:45 PM #904
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    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
    Reply With Quote Reply With Quote
  5. 02-09-2022, 11:45 PM #905
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    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
    Reply With Quote Reply With Quote
  6. 02-09-2022, 11:45 PM #906
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
     

     5.50 8.18 Clean Up Windows 10 _ 3 Steps For A ...

     7.56 8 (Ransome) How to Make Windows 10 Secur...

     8.45 Speed Up Windows 10 in 2020-8E6OT_QcHaU_20...

     

     8.94 Creating New Windows 10 Debloat Scripts fo...

     
    Reply With Quote Reply With Quote
  7. 02-14-2022, 02:10 PM #907
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    Test Burgandy

    Generic color name: Burgundy
    Colors that make up #8C001A
    RGB: 140, 0, 26 - HSL: 0.97, 1.00, 0.27

    Purple[COLOR="#800080"]Purple[/COLOR]
    Purple[COLOR=Purple]Purple[/COLOR]
    Maroon[COLOR="#800000"]Maroon[/COLOR]
    Maroon[COLOR=Maroon]Maroon[/COLOR]
    Burgundy[COLOR="#8C001A"]Burgundy[/COLOR]
    Windows10[color="#3E0000"]Windows10[/COLOR]
    color:#A82D00 $NFS




    HTML Code:
    <div class=WordSection1>

<p class=MsoNormal><span lang=EN-GB style='font-size:9.0pt;line-height:115%;
font-family:"Lucida Console";mso-bidi-font-family:"Lucida Console";color:#A82D00;
mso-ansi-language:EN-GB;mso-fareast-language:DE'>$NFS</span><span lang=EN-GB
style='font-size:9.0pt;line-height:115%;font-family:"Lucida Console";
mso-bidi-font-family:"Lucida Console";mso-ansi-language:EN-GB;mso-fareast-language:
DE'><span style='mso-spacerun:yes'>                             </span><span
style='color:dimgray'>=</span> <span style='color:blue'>New-Object</span> <span
class=SpellE><span style='color:blueviolet'>system.Windows.Forms.Button</span></span></span><span
lang=EN-US style='mso-ansi-language:EN-US'><o:p></o:p></span></p>

</div>
    Code:
    'Sub Makro9BBBurgundy()   '   '     https://excelfox.com/forum/showthread.php/2417-Test-BB-Code-Highlighting-and-Colors?p=16370#post16370
'    With Selection
'     .Font.Color = 1704076
'     .Text = "[color=""#8C001A""] " & .Text & " [/color]"
'     .Collapse Direction:=wdCollapseEnd
'    End With
'End Sub
Sub Makro9BBBurgundy() '    Ctrl+Shift+B,G   '  https://eileenslounge.com/viewtopic.php?f=26&t=37808&p=292620#p292620
    Dim Text1 As String
    Dim Text2 As String
    Text1 = "[color=""#8C001A""] "
    Text2 = " [/color]"
    With Selection
        .InsertBefore Text:=Text1
        .InsertAfter Text:=Text2
        ActiveDocument.Range(Selection.Start, Selection.Start + Len(Text1)).Font.Size = 8
        ActiveDocument.Range(Selection.End - Len(Text2), Selection.End).Font.Size = 8
        .Font.Color = 1704076
        .Collapse Direction:=wdCollapseEnd
    End With
End Sub
    Reply With Quote Reply With Quote
  8. 02-16-2022, 03:19 AM #908
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    Hello Atlantis764
    Welcome to ExcelFox

    I don’t have any experience with UserForms
    and
    I don’t really understand the full picture of what you are trying to do.

    Here is a screenshot of the file you uploaded: https://excelfox.com/forum/showthrea...ll=1#post16365

    I can see that there is some correlation in the coloured cells, but all I can understand from your explanation is that you want to match Name and Project and Task as you showed in the workbook

    The best I can do is that I can get you started on doing that matching:

    We can make an array which has as many elements as there are data rows in Database1
    Each element will contain a string made up from each row of the
    Name & Project & Task

    We can do something similar for worksheet Database

    Then you can match the strings in the two arrays.

    Run this demo macro and I think you will see what I mean


    Code:
    '    https://excelfox.com/forum/showthread.php/2783-User-Form-entry-in-a-second-sheet-need-help-with-VBA-code?p=16371&viewfull=1#post16371
Sub MatchNameProjectTask()
Rem 0 Worksheets info
Dim WsD As Worksheet, WsD1 As Worksheet
 Set WsD = ThisWorkbook.Worksheets("Database"): Set WsD1 = ThisWorkbook.Worksheets("Database1")
Dim LrD As Long, LrD1 As Long
 Let LrD = WsD.Range("A" & WsD.Rows.Count & "").End(xlUp).Row: Let LrD1 = WsD1.Range("A" & WsD1.Rows.Count & "").End(xlUp).Row
Rem 2 make arrays of concatenated words
'Dim v: v = WsD.Evaluate("=D2:D4&E2:E4&F2:F4"): v = WsD.Evaluate("=D2:D" & LrD & "&E2:E" & LrD & "&F2:F" & LrD & "")
Dim arrD() As Variant: Let arrD() = WsD.Evaluate("=D2:D" & LrD & "&E2:E" & LrD & "&F2:F" & LrD & "")
Dim arrD1() As Variant: Let arrD1() = WsD1.Evaluate("=A2:A" & LrD1 & "&B2:B" & LrD1 & "&C2:C" & LrD1 & "")
Rem 3 compare arrays
Dim rwD As Long, rwD1 As Long
    For rwD = 2 To LrD
        For rwD1 = 2 To LrD1
            If arrD(rwD - 1, 1) = arrD1(rwD1 - 1, 1) Then MsgBox prompt:="match for " & arrD(rwD - 1, 1) & " at Database row " & rwD & " Database1 row " & rwD1
        
        Next rwD1
    Next rwD
End Sub
    Here's the same basic macro done slightly differently
    Code:
    Sub MatchNameProjectTask2()
Rem 0 Worksheets info
Dim WsD As Worksheet, WsD1 As Worksheet
 Set WsD = ThisWorkbook.Worksheets("Database"): Set WsD1 = ThisWorkbook.Worksheets("Database1")
Dim LrD As Long, LrD1 As Long
 Let LrD = WsD.Range("A" & WsD.Rows.Count & "").End(xlUp).Row: Let LrD1 = WsD1.Range("A" & WsD1.Rows.Count & "").End(xlUp).Row
Rem 2 make arrays of concatenated words
Dim arrD() As String, arrD1() As String
 ReDim arrD(2 To LrD): ReDim arrD1(2 To LrD1)
Dim rwD As Long, rwD1 As Long
    For rwD = 2 To LrD
     Let arrD(rwD) = WsD.Range("D" & rwD & "") & WsD.Range("E" & rwD & "") & WsD.Range("F" & rwD & "")
    Next rwD
    For rwD1 = 2 To LrD1
     Let arrD1(rwD1) = WsD1.Range("A" & rwD1 & "") & WsD1.Range("B" & rwD1 & "") & WsD1.Range("C" & rwD1 & "")
    Next rwD1
Rem 3 compare arrays
    For rwD = 2 To LrD
        For rwD1 = 2 To LrD1
            If arrD(rwD) = arrD1(rwD1) Then MsgBox prompt:="match for " & arrD(rwD) & " at Database row " & rwD & " Database1 row " & rwD1
        Next rwD1
    Next rwD
End Sub
    Alan
    Attached Files Attached Files
    Reply With Quote Reply With Quote
  9. 02-16-2022, 03:53 AM #909
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    Hi Kris
    Just one very minor observation: You will need Excel 2010 or higher because I think .ForeColor.Brightness isn't supported in earlier Excel
    Attachment 3832
    (I checked in a few versions of 2010 and higher, and all seems OK)

    Alan
    Attached Images Attached Images
    Reply With Quote Reply With Quote
  10. 02-16-2022, 04:06 PM #910
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,521
    Rep Power
    10
    Hi Liviu
    It’s possibly difficult for me to understand the full picture as I know nothing about UserForms.
    I doubt therefore that I will be able to give you exactly the final coding you need, since I don’t understand the workings of UserForms.
    Also, if I click that Form button , then I see this, which is impossible for me to see anything in.
    https://i.postimg.cc/8cdDypfn/Click-Form-Button.jpg
    Attachment 3833
    ( If you have an issue / problem specific to something associated with the UserForm then I can’t help further. If for example, there is a simple typo in Task, or you have task rather than Task, then I can’t see that in that UserForm )


    The best I can do therefore is give again some ideas that takes the matching further and goes on to match those date values, and then puts the Amount in the appropriate place in database1
    (Basically, what I did for you before is going some way to demo to you coding that does that sort of row matching based on the various criteria of matching headings. So that was just matching the rows, which matched effectively "Name" and "Project" and the "Task).


    This next macro is basically the same as my last. But instead of the demo message box, it now adds the "Amount" value to be added to Database1 sheet in the corresponding cell

    The way I have done this extra section, is to convert the Year and Month from column B and C in Database, (along with an assumed day of the first of the month) to an Excel date serial number *****
    I then compare that with the date serial numbers for the top row of Database1.
    A match will then tell me which column to put the Amount in

    Code:
    Sub MatchNameProjectTask3()
Rem 0 Worksheets info
Dim WsD As Worksheet, WsD1 As Worksheet
 Set WsD = ThisWorkbook.Worksheets("Database"): Set WsD1 = ThisWorkbook.Worksheets("Database1")
Dim LrD As Long, LrD1 As Long
 Let LrD = WsD.Range("A" & WsD.Rows.Count & "").End(xlUp).Row: Let LrD1 = WsD1.Range("A" & WsD1.Rows.Count & "").End(xlUp).Row
Rem 2 make arrays of concatenated words
Dim arrD() As String, arrD1() As String
 ReDim arrD(2 To LrD): ReDim arrD1(2 To LrD1)
Dim rwD As Long, rwD1 As Long
    For rwD = 2 To LrD
     Let arrD(rwD) = WsD.Range("D" & rwD & "") & WsD.Range("E" & rwD & "") & WsD.Range("F" & rwD & "")
    Next rwD
    For rwD1 = 2 To LrD1
     Let arrD1(rwD1) = WsD1.Range("A" & rwD1 & "") & WsD1.Range("B" & rwD1 & "") & WsD1.Range("C" & rwD1 & "")
    Next rwD1
'2b) Array of date serials from Database1
Dim arrDtSerials() As Variant, LcD1 As Long
 Let LcD1 = WsD1.Cells(1, WsD1.Columns.Count).End(xlToLeft).Column
 Let arrDtSerials() = WsD1.Range("A1").Resize(1, LcD1).Value2
Rem 3 compare arrays for headings
    For rwD = 2 To LrD
        For rwD1 = 2 To LrD1
            If arrD(rwD) = arrD1(rwD1) Then    '     MsgBox prompt:="match for " & arrD(rwD) & " at Database row " & rwD & " Database1 row " & rwD1
            '3b We have a heading match , so now match the date
            Dim DteSerial As Variant
            ' Let DteSerial = WsD.Evaluate("=DATEVALUE(""1 " & WsD.Range("C2").Value & " " & WsD.Range("B2").Value & """)")
             Let DteSerial = WsD.Evaluate("=DATEVALUE(""1 " & WsD.Range("C" & rwD & "").Value & " " & WsD.Range("B" & rwD & "").Value & """)")
            Dim MtchRes As Variant
             Let MtchRes = Application.match(DteSerial, arrDtSerials(), 0)
                If IsError(MtchRes) Then MsgBox prompt:="No date match": Exit Sub
             Let WsD1.Cells(rwD1, MtchRes) = WsD.Range("G" & rwD & "").Value
            Else
            
            End If
        Next rwD1
    Next rwD
End Sub



    So this would be the Before, as we had before: https://excelfox.com/forum/showthrea...ll=1#post16377

    Then, after running that macro, this would be the After https://excelfox.com/forum/showthrea...ll=1#post16378


    Alan



    *****Unfortunately dates in Excel and VBA are a real pain in the arse, since different excel versions and land versions and user setting all give different results, so you may need to tweak some of the coding that gets the date match. There is no known way around these problems. It makes sharing files to different people with dates in them sometimes impossible.
    The macro is working in my Excel
    Its based on this bit working to give me the correct date serial number from worksheet Database


    Code:
    Sub Dts()
Rem 0 Worksheets info
Dim WsD As Worksheet
 Set WsD = ThisWorkbook.Worksheets("Database")
Dim DteSerial As Variant
 Let DteSerial = Evaluate("=DATEVALUE(""1-January-2022"")") ' 44562 : Variant/Double
 Let DteSerial = WsD.Evaluate("=DATEVALUE(""1-"" & ""January"" & ""-2022"")") ' 44562 : Variant/Double
 Let DteSerial = WsD.Evaluate("=DATEVALUE(""1-" & WsD.Range("C2").Value & "-2022"")") ' 44562 : Variant/Double
 Let DteSerial = WsD.Evaluate("=DATEVALUE(""1-" & WsD.Range("C2").Value & "-2022"")") ' 44562 : Variant/Double
 Let DteSerial = WsD.Evaluate("=DATEVALUE(""1-" & WsD.Range("C2").Value & "-" & WsD.Range("B2").Value & """)")  ' 44562 : Variant/Double
 Let DteSerial = WsD.Evaluate("=DATEVALUE(""1 " & WsD.Range("C2").Value & " " & WsD.Range("B2").Value & """)") ' 44562 : Variant/Double
 
 
 
End Sub

    Ref http://www.eileenslounge.com/viewtop...290229#p290229
    https://excelfox.com/forum/showthrea...otes-and-Tests
    Attached Images Attached Images
    Attached Files Attached Files
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. Tests and Notes on Range Referrencing
    By DocAElstein in forum Test Area
    Replies: 70
    Last Post: 02-20-2024, 01:54 AM
  2. Tests and Notes for EMail Threads
    By DocAElstein in forum Test Area
    Replies: 29
    Last Post: 11-15-2022, 04:39 PM
  3. Notes tests. Excel VBA Folder File Search
    By DocAElstein in forum Test Area
    Replies: 39
    Last Post: 03-20-2018, 04:09 PM
  4. Notes tests. ByVal ByRef Application.Run.OnTime Multiple Variable Arguments ByRef ByVal
    By DocAElstein in forum Test Area
    Replies: 37
    Last Post: 02-28-2018, 12:22 AM
  5. Excel Workbook ReOpening After Closing With Application.OnTime
    By leopaulc in forum Excel Help
    Replies: 2
    Last Post: 12-04-2012, 02:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules