Thursday, February 22, 2018

Alias command in Ubuntu 16,linux Teacher Sourav,Kolkata 09748184075

aliases can be any name other than reserved word ,it can even be same name as a command with different options,aliases can be applied on global basis or per user basis


for global basis we have to modify the file
called /etc/bashrc

for user basis the file is 
/home/username/.bashrc

let's create a custom command


the alias command will show you the already set aliases

set a alias

alias ls='ls -l'

now using the alias command you will see 
the new alias set

when we use ls using the alias it will run 
ls -l

we can perform the same thing 

by changing df to df -h

alias df='df -h'

h is for human readable format,the output of df will be shown in human readable format by default from now instead of block sizes



now let's create a command which does not exist


alias delete='rm -i'

by defining the alias delete will be available to bash will also help in tab complition when typing delete

these aliases are also going to available to shell scripts

to remove the alias

unalias delete

to make these aliases permanenet we have to aliases to the /etc/bashrc(for parmanent) or /home/username/.bashrc for just the user

firefox or chrome can not run swf files solved on ubuntu 16,Linux Teacher Sourav,Kolkata 09748184075

Edit /usr/share/mime/packages/freedesktop.org.xml as root and replace the following:


With:


And then run:

sudo update-mime-database /usr/share/mime

Friday, February 2, 2018

Create a dynamic chart automatically and change it's va;ue dynamically when the original data is filtered or sorted using VBA,VBA Teacher Sourav,Kolkata 09748184075

Sub Macro1()

Dim ch As Shape

    Range("B13").Select
    Selection.CurrentRegion.Select
    ActiveSheet.Shapes.AddChart.Select
  
    Set ch = ActiveSheet.Shapes(1)
    ch.Name = "Chart 3"
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range("'(2)'!$B$12:$C$36")
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Selection.Delete
    ch.Placement = xlFreeFloating
    Range("B13").Select
    Selection.AutoFilter
    ActiveSheet.Range("$B$12:$C$36").AutoFilter Field:=2, Criteria1:="5", _
        Operator:=xlTop10Items
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
    Range("C19").Select
    ActiveWorkbook.Worksheets("(2)").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("(2)").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "C19"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("(2)").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ApplyDataLabels
    ActiveChart.SeriesCollection(1).DataLabels.Select
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.ChartGroups(1).VaryByCategories = True
End Sub

Wednesday, December 27, 2017

Automating searching mails in outlook with a particular word in the subject,copy the mail message and the sender's mail address in an existing excel file,VBA Teacher Sourav,Kolkata 09748184075


Sub CloseOutlook()
Dim OL As Object
On Error Resume Next
Set OL = GetObject(, "Outlook.Application")
On Error GoTo 0
If OL Is Nothing Then
'MsgBox "Outlook is not running!"
Else
OL.Quit
End If
End Sub


 Sub Search_Inbox()
 'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  Dim wb As Workbook
 'Trying to close outlook first

 CloseOutlook
 'Trying to close the target excel file

    'now let's see if the workbook where the data to be pasted is open or not
       
         Dim status As Boolean

         status = IsWorkBookOpen("C:\Users\sourav\Desktop\data.xlsm")
         If status = True Then
         Workbooks("data.xlsm").Close SaveChanges:=True
        
         End If
       

Dim myOlApp As New Outlook.Application
Dim myNameSpace As Outlook.Namespace
Dim myInbox As Outlook.MAPIFolder
Dim myitems As Outlook.Items
Dim myItem As Object
Dim Found As Boolean
Dim atmt As Outlook.Attachment
Dim MyAr() As String
Dim address As String
Dim message As String


Dim ws As Worksheet
Dim aCell As Range, Rng As Range
Dim col As Long, lRow As Long
Dim colName As String
On Error Resume Next

Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myitems = myInbox.Items
Found = False

For Each myItem In myitems
    If myItem.Class = olMail Then
        If InStr(1, myItem.Subject, "macro") > 0 Then
            'MsgBox ("Found")
            For Each atmt In myItem.Attachments

                If atmt.FileName = "macro.txt" Then
                    atmt.SaveAsFile "D:\" & atmt.FileName
                    'MsgBox (myItem.SenderEmailAddress)
                    'saving the mail body in an array
               
                    MyAr = Split(myItem.Body, vbCrLf)

                    For i = LBound(MyAr) To UBound(MyAr)
                        '~~> This will give you the contents of your email
                        '~~> on separate lines
                        'MsgBox (MyAr(i))
                        If MyAr(i) <> "" Then
                        message = message + MyAr(i)
                        End If
   
   
                    Next i

                    'Now trying to write the message and the sender address in an existing excel file

                    'First trying to close the file if it is already open
                    On Error Resume Next

                    Workbooks("data.xlsm").Close SaveChanges:=True

                    'now trying to open the file

                    Set wb = Workbooks.Open("C:\Users\sourav\Desktop\data.xlsm")
                    'now finding the spicific column with some word or words
                    Set ws = wb.Sheets("Sheet2")

                    With ws
                        Set aCell = .Range("A1:P1").Find(What:="Mail*", LookIn:=xlValues, LookAt:=xlWhole, _
                        MatchCase:=False, SearchFormat:=False)
                        If Not aCell Is Nothing Then
                            col = aCell.Column
                            colName = Split(.Cells(, col).address, "$")(1)
                            'MsgBox (colName)

                            lRow = .Range(colName & .Rows.count).End(xlUp).Row
                            'MsgBox (lRow)
           
                            '~~> This is your range
                            'Set Rng = .Range(colName & "8:" & colName & lRow)

                            'MsgBox (Rng.address)
                            '~~> If not found
       
                            'Now find the blank cell in the column found earlier
                            Set Rng = .Range(colName & lRow)
                            Rng.Select
       
                             Range(Selection.End(xlDown)).Select
                             ActiveCell.Offset(1, 0).Select
                            
                           
                            ActiveCell.Value = myItem.SenderEmailAddress
                            If ActiveCell.Offset(0, 1) = "" Then
                                ActiveCell.Offset(0, 1).Value = message
                            End If
                            ws.Columns.AutoFit
                        Else
                            ' MsgBox "Mail Not Found"
                        End If
                        End With
                        On Error Resume Next
                        wb.Close SaveChanges:=True
                        Set wb = Nothing
                        Set ws = Nothing
                       
                        'MsgBox ("Done")

                End If


                message = ""
            Next
                Found = True
        End If
    End If
Next myItem

'If the subject isn't found:
If Not Found Then
   ' NoResults.Show
   MsgBox ("Task Failed")
  
End If

myOlApp.Quit
Set myOlApp = Nothing


'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Monday, December 25, 2017

Search mail in outlook with a particular word in the subject and read the message in the mail line by lines using VBA,VBA Teacher Sourav,Kolkata 09748184075


Sub CloseOutlook()
Dim OL As Object
On Error Resume Next
Set OL = GetObject(, "Outlook.Application")
On Error GoTo 0
If OL Is Nothing Then
MsgBox "Outlook is not running!"
Else
OL.Quit
End If
End Sub


 Sub Search_Inbox()
 'Trying to close outlook first

 CloseOutlook


Dim myOlApp As New Outlook.Application
Dim myNameSpace As Outlook.Namespace
Dim myInbox As Outlook.MAPIFolder
Dim myitems As Outlook.Items
Dim myItem As Object
Dim Found As Boolean
Dim atmt As Outlook.Attachment
Dim MyAr() As String
On Error Resume Next

Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myInbox = myNameSpace.GetDefaultFolder(olFolderSentMail)
Set myitems = myInbox.Items
Found = False

For Each myItem In myitems
    If myItem.Class = olMail Then
        If InStr(1, myItem.Subject, "macro") > 0 Then
            'MsgBox ("Found")
            For Each atmt In myItem.Attachments

If atmt.FileName = "macro.txt" Then
atmt.SaveAsFile "D:\" & atmt.FileName

MyAr = Split(myItem.Body, vbCrLf)

For i = LBound(MyAr) To UBound(MyAr)
    '~~> This will give you the contents of your email
    '~~> on separate lines
    MsgBox (MyAr(i))
Next i
MsgBox ("Done")

End If



Next
            Found = True
        End If
    End If
Next myItem

'If the subject isn't found:
If Not Found Then
   ' NoResults.Show
End If

myOlApp.Quit
Set myOlApp = Nothing

End Sub