Sunday, April 26, 2020

Understanding variables and editing the powershell profile for custom message when logging in in powershell 7 in Debian 10

creating variable

$a=1
$b=2
$c=$a+$b

to see the type of the variable

$a | Get-Member

gm is the alias of Get-Member

so we can do

$a | gm

as gci is the alias of get-child-items

we can do

$items=gci

$items|gm

to get the processes

$processes=get-process

$process | gm

by default the variables are loosely typed,for some reason if we want strict type variables
we need to cast it like this

[int]$dozen=12

if we get the type

$dozen|gm

it will be shown as a integer

Now I can not assign a string inside it for obvious reasons

 $dozen="kshinfo"

so the above line will not execute

to see all the variables

get-variable

in the output we will see the profile.sh file

now from terminal to edit the profile file

code $PROFILE

will open the profile in vscode if it is installed

Now to get a custom message like a greeting
showing the time and the day

we need to write

#Create a greeting that says good morning or good afternoon based on time of the
day
$date=Get-Date

$hour=$date.Hour

$dayofweek=$date.DayOfWeek

if ($hour -lt 12)

{
$greeting="Good Morning,"

}

else
{

    $greeting="Good Evening,"
    }

Write-Host "$greeting I hope you are having a great $dayofweek"





save the file and close it and restart the powershell session

you will see the custom prompt


Saturday, April 25, 2020

Install VSCode in Debian10 as powershell editor

curl https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor > packages.microsoft.gpg
sudo install -o root -g root -m 644 packages.microsoft.gpg /usr/share/keyrings/
sudo sh -c 'echo "deb [arch=amd64 signed-by=/usr/share/keyrings/packages.microsoft.gpg] https://packages.microsoft.com/repos/vscode stable main" > /etc/apt/sources.list.d/vscode.list'

Then update the package cache and install the package using:

sudo apt-get install apt-transport-https
sudo apt-get update
sudo apt-get install code # or code-insiders


Source:https://code.visualstudio.com/docs/setup/linux

Help in Powershell 7

to see how many commands are available

 get-command

to count the numbers of commands available

 get-command | measure

due to consistent syntax each powershell command is build up by a verb a dash and a noun

to see all the verbs

get-verb


to see the nouns there is no standard procedure

for example

to see the all get command with process

suppose we want to get help on starting process


get-help start-process

to get the help with full documentation

get-help start-process -full


for example to start firefox

start-process firefox

get-command *process

Thursday, April 23, 2020

Exporting command output to csv,import csv and see the content as a formatted table in powershell 7 in Debian 10

exporting command output to a csv file

get-process |  sort-object -property ws -descending | select-object -property processname,ws -first 5|Export-csv /home/sourav/Desktop/top5proc.csv


to see the content of the csv

 Get-Content ./Desktop/top5proc.csv


to impoer the csv file

import-csv ./Desktop/top5proc.csv  

if there are lots of data in the csv file we can format the output which will produce data in a more readable format

import-csv ./Desktop/top5proc.csv|Format-Table

Using Pipes in Powershell 7 on Debian 10

using get-process we get a detailed info of processes running

from that output if we only want to filter the output by just processname,id,ws we need to use

 get-process | select-object -Property processname,id,ws

to sort the output with a particular column like working set(ws) which lists processes based on memory usages in a ascending order


to see the same result in a descending manner

 get-process | select-object -Property processname,id,ws | sort-object -property ws -descending

to see the top 5 processes as they are consuming memory in a descending manner

get-process |  sort-object -property ws -descending | select-object -property processname,ws -first 5


To download videos from youtube in best resolution or in particular resolution like 1280*720p resolution using youtube-dl

youtube-dl --no-check-certificate -cio "E:/powerpoint_vba/po
weshell_linux/%(title)s.%(ext)s" --playlist-start 1 --playlist-end 7 -f best htt
ps://www.youtube.com/playlist?list=PL4URqfIIQcfBOtaSXl5tu4tYvpApw91Ew








that -f best is for best format available

say for example if we want to download the videos only in  1280x720 mp4 format we need to use
-f 22



youtube-dl --no-check-certificate -cio "E:/powerpoint_vba/po
weshell_linux/%(title)s.%(ext)s" --playlist-start 1 --playlist-end 7 -f 22 htt
ps://www.youtube.com/playlist?list=PL4URqfIIQcfBOtaSXl5tu4tYvpApw91Ew 






Source:https://askubuntu.com/questions/486297/how-to-select-video-quality-from-youtube-dl

 




















Tuesday, April 21, 2020

Powershell basics on linux part 1,creating files and folders,deleting files and folders,creating multiple files with variable names using batch processing using powershell 7 on Debian 10

powershell basics

get-childitem
to see the subdirectories in the current directory


get-process
to see the current processes


Create a new folder

New-Item -Type Directory -name FileTest

we can use ls to see if it is created

To see the current working directory or pwd

get-location

Now to get inside a folder or cd

set-location './FileTest/'

to see the contents inside that folder

get-childitem

create a text file

new-item -name test1.text


to delete the text file

remove-item ./test1.text

Using Linux binary or tools installed nside powershell

nano test2.text

write something and save it by ctrl+x

to see the content of the file

Get-Content ./test2.text

to get to the parent folder or cd .. we have to use

set

Set-Location ..

to delete the folder

 Remove-Item ./FileTest/

in this case you will be prompted for confirmation

to create 10 text files with names like 1 to 10

1..10|%{new-item -name "test$_.text"}

ls to see the files created like

test1.txt,test2,txt.... test10.txt


Install powershell 7 on Debian 10

See the version of Debian

lsb_release -a

First I need to access all the commands stored in /usr/local/sbin and /usr/sbin

I need to edit the .bashrc file in my user's home profile and the last two lines at the end

export PATH=$PATH:/usr/local/sbin
export PATH=$PATH:/usr/sbin



save the file 


and 

Source .bashrc

to reload the file to see the change


to use the old ifconfig

sudo apt install net-tools



to see the dynamically generated ip 

ifconfig

I enabled ssh while installing so now using tool like putty I can access debian from outside

Now let me install cshell zshell and korshell

 sudo apt install csh zsh ksh

Now I am using Debian 10 which I can verify by using  lsb_release -a command

So to install powershell I have to use these commands



# Download the Microsoft repository GPG keys
wget https://packages.microsoft.com/config/debian/10/packages-microsoft-prod.deb

# Register the Microsoft repository GPG keys
sudo dpkg -i packages-microsoft-prod.deb

# Update the list of products
sudo apt-get update

# Install PowerShell
sudo apt-get install -y powershell

# Start PowerShell
pwsh





Source:https://docs.microsoft.com/en-us/powershell/scripting/install/installing-powershell-core-on-linux?view=powershell-7



Sunday, April 19, 2020

Install ZShell ,KornShell and CShell on Lubuntu 19.10

 sudo apt install zsh ksh csh

Install and enable SSH on Lubuntu 19.10

sudo apt install net-tools

use ifconfig to see the automatically given ip

sudo apt install openssh-server

after installing to start stop and restart this service

sudo systemctl stop ssh

sudo systemctl start ssh

sudo systemctl restart ssh

To disable the SSH service to start during system boot run:

sudo systemctl disable ssh

To enable it again type:

sudo systemctl enable ssh

Ubuntu comes with a firewall configuration tool called UFW. If the firewall is enabled on your system, make sure to open the SSH port:

sudo ufw allow ssh





Source:https://linuxize.com/post/how-to-enable-ssh-on-ubuntu-18-04/

Install powershell on Fedora 31

# Register the Microsoft signature key
sudo rpm --import https://packages.microsoft.com/keys/microsoft.asc

# Register the Microsoft RedHat repository
curl https://packages.microsoft.com/config/rhel/7/prod.repo | sudo tee /etc/yum.repos.d/microsoft.repo

# Update the list of products
sudo dnf update

# Install a system component
sudo dnf install compat-openssl10

# Install PowerShell
sudo dnf install -y powershell

# Start PowerShell
pwsh


Source:https://docs.microsoft.com/en-us/powershell/scripting/install/installing-powershell-core-on-linux?view=powershell-7

Install kornshell ,CShell and ZShell on Fedora 31

sudo dnf install ksh csh zsh

Install and enable openssh in fedora server 31

$ sudo dnf install openssh-server

Enable service sshd to make sure that SSH daemon will start after the reboot:

$ sudo systemctl enable sshd

To start SSH server:

$ sudo systemctl start sshd

check the SSH server status using the following command:

$ sudo systemctl status sshd

Furthermore, you should now see the port 22 open for a new incoming connections:

$ sudo ss -lt



Source:https://linuxconfig.org/how-to-install-start-and-connect-to-ssh-server-on-fedora-linux

Friday, April 17, 2020

Getting column letter from column number,selecting the column and change the column format using vba

'MsgBox (Split(Cells(1, ActiveCell.Column).address, "$")(1))
Dim rangecol As Range
Set rangecol = Range(Split(Cells(1, ActiveCell.Column).address, "$")(1) & ":" & Split(Cells(1, ActiveCell.Column).address, "$")(1))
'MsgBox (rangecol.address)

rangecol.NumberFormat = "General"

Monday, April 13, 2020

Subtracting one range from another in VBA

Option Explicit
Private mrBuild As Range
Sub selection_experiment()
Sheets("RCformula").Select

Dim workingrange As Range


Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Set workingrange = Selection
MsgBox (workingrange.Address)
Dim removefromrange As Range
Range("A1").Select
Selection.End(xlDown).Select
Set removefromrange = Selection
MsgBox (removefromrange.Address)
MsgBox (SubtractRanges(workingrange, removefromrange).Address)


End Sub



Public Function SubtractRanges(rFirst As Range, rSecond As Range) As Range

    Dim rInter As Range
    Dim rReturn As Range
    Dim rArea As Range

    Set rInter = Intersect(rFirst, rSecond)
    Set mrBuild = Nothing

    If rInter Is Nothing Then 'No overlap
        Set rReturn = rFirst
    ElseIf rInter.Address = rFirst.Address Then 'total overlap
        Set rReturn = Nothing
    Else 'partial overlap
        For Each rArea In rFirst.Areas
            BuildRange rArea, rInter
        Next rArea
        Set rReturn = mrBuild
    End If

    Set SubtractRanges = rReturn

End Function

Sub BuildRange(rArea As Range, rInter As Range)

    Dim rLeft As Range, rRight As Range
    Dim rTop As Range, rBottom As Range

    If Intersect(rArea, rInter) Is Nothing Then 'no overlap
        If mrBuild Is Nothing Then
            Set mrBuild = rArea
        Else
            Set mrBuild = Union(mrBuild, rArea)
        End If
    Else 'some overlap
        If rArea.Columns.count = 1 Then 'we've exhausted columns, so split on rows
            If rArea.Rows.count > 1 Then 'if one cell left, don't do anything
                Set rTop = rArea.Resize(rArea.Rows.count \ 2) 'split the range top to bottom
                Set rBottom = rArea.Resize(rArea.Rows.count - rTop.Rows.count).Offset(rTop.Rows.count)
                BuildRange rTop, rInter 'rerun it
                BuildRange rBottom, rInter
            End If
        Else
            Set rLeft = rArea.Resize(, rArea.Columns.count \ 2) 'split the range left to right
            Set rRight = rArea.Resize(, rArea.Columns.count - rLeft.Columns.count).Offset(, rLeft.Columns.count)
            BuildRange rLeft, rInter 'rerun it
            BuildRange rRight, rInter
        End If
    End If

End Sub


Source:https://stackoverflow.com/questions/21580795/subtracting-ranges-in-vba-excel

Sunday, April 12, 2020

Download youtube videos from a playlist between start and end index using youtube-dl

youtube-dl --no-check-certificate -cio "E:/powerpoint_vba/access_vba/%(title)s.%(ext)s" --playlist-start 1 --playlist-end 6 https://www.youtube.com/playlist?list=PLYMOUCVo86jEeMMdaaq03jQ_t9nFV737s

Using Excel formulas with and without RC notation in VBA,VBA Teacher Sourav,Kolkata 08910141720

 Without RC notation

Sub usingexcelfunctioninvba()

Sheets("prg").Select
Dim workingrange As Range


Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Set workingrange = Selection
Dim sumresult As Integer
sumresult = Application.sum(workingrange)
Dim average As Double
average = Application.average(workingrange)
Dim count As Integer
count = Application.count(workingrange)
Dim max, min As Double
max = Application.max(workingrange)
min = Application.min(workingrange)
Dim stdevsample As Double
stdevsample = Application.WorksheetFunction.StDev_S(workingrange)
Dim stdevpopulation As Double
stdevpopulation = Application.WorksheetFunction.StDev_P(workingrange)
Dim median, mean, mode As Double
median = Application.WorksheetFunction.median(workingrange)
'mean = Application.WorksheetFunction.mean(workingrange)
mode = Application.mode(workingrange)
Range("A5").Value = sumresult
Range("B5").Value = average
Range("C5").Value = count
Range("D5").Value = max
Range("E5").Value = min
Range("F5").Value = stdevsample
Range("G5").Value = stdevpopulation
Range("H5").Value = average
Range("I5").Value = median
Range("J5").Value = mode

End Sub



With RC notation

Option Explicit

Sub usingrcformulainvba()
Sheets("prg").Select
Range("A1").Select
Dim firstrangerow As Integer
firstrangerow = (ActiveCell.Row)
Dim firstrangecolumn As Integer
firstrangecolumn = (ActiveCell.Column)
Selection.End(xlToRight).Select
Dim lastrangerow As Integer
lastrangerow = (ActiveCell.Row)
Dim lastrangecolumn As Integer
lastrangecolumn = (ActiveCell.Column)

Range("A7").Select

Dim currentrangerow As Integer
currentrangerow = (ActiveCell.Row)
Dim currentrangecolumn As Integer
currentrangecolumn = (ActiveCell.Column)

'MsgBox (firstrangerow - currentrangerow)


ActiveCell.FormulaR1C1 = "=SUM(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"
ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=Average(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=count(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=max(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=min(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=stdev.s(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=stdev.p(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=average(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"

ActiveCell.Offset(0, 1).Select

currentrangerow = (ActiveCell.Row)

currentrangecolumn = (ActiveCell.Column)
ActiveCell.FormulaR1C1 = "=median(R[" & (firstrangerow - currentrangerow) & "]C[" & (firstrangecolumn - currentrangecolumn) & "]:R[" & (lastrangerow - currentrangerow) & "]C[" & (lastrangecolumn - currentrangecolumn) & "])"
End Sub

Friday, April 10, 2020

youtube-dl error when downloading videos from youtube 'urlopen error' solved

Instead of 

C:\Windows\system32> youtube-dl  -cio "E:/powerpoint_vba/%(title)s.%(ext)s" http
s://www.youtube.com/playlist?list=PLyKV-uDcp5XgJ8FqczLBf2XxKjmQUvcz6


we should use

C:\Windows\system32> youtube-dl --no-check-certificate  -cio "E:/powerpoint_vba/
%(title)s.%(ext)s" https://www.youtube.com/playlist?list=PLyKV-uDcp5XgJ8FqczLBf2
XxKjmQUvcz6


Source:https://github.com/ytdl-org/youtube-dl/issues/883