Add data labels to dynamic scatter plot

Define 2 named ranges (Formula Tab > Name Manager > New):

-----------------------------------------------------------------------------

JobSat_Score

=OFFSET(Presentation!$B$7,0,0,COUNTA(Presentation!$B:$B)-5,1)

WorkLife_Score

=OFFSET(Presentation!$C$7,0,0,COUNTA(Presentation!$C:$C)-1,1)

Change the parameters according to your pivot design.

==================================================== 

Insert a scatter plot chart.

Add a series:

Series X values: ='Refresh Data Labels.xlsm'!JobSat_Score

Series Y values: ='Refresh Data Labels.xlsm'!WorkLife_Score

Adjust chart axis as per your requirement.

====================================================

For adding and refreshing data labels dynamically write this procedure and call it on a button click.

Sub refreshLabels()

    On Error Resume Next

    Dim counter As Integer, xValueFormula As String

    Application.ScreenUpdating = False

   

    ActiveSheet.ChartObjects(1).Activate

    xValueFormula = ActiveChart.SeriesCollection(1).Formula

   

    'getting the series name for X values

    xValueFormula = Mid(xValueFormula, InStr(1, xValueFormula, ",'", vbTextCompare) + 1)

    xValueFormula = Left(xValueFormula, InStr(1, xValueFormula, ",'", vbTextCompare) - 1)

   

    'Attach a label to each data point in the chart

    For counter = 1 To Range(xValueFormula).Cells.Count

        ActiveChart.SeriesCollection(1).Points(counter).HasDataLabel = False

        ActiveChart.SeriesCollection(1).Points(counter).HasDataLabel = True

        ActiveChart.SeriesCollection(1).Points(counter).DataLabel.Text = Range(xValueFormula).Cells(counter, 1).Offset(0, -1).Value

    Next counter

End Sub

DATA USED FOR EXAMPLE

SAMPLE DATA: HR Employee Attrition and Performance

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.

LINK FOR THE EXAMPLE FILE

Excel function for Rail Fence Cipher and Caesar Cipher

      Rail Fence Cipher:

·     

           Key and String as argument parameter from user,

o   =encode(input string, key)

o   =decode(encoded string, key)

·         If the user enters 2 then

o   There will be 2 rows:

o   fn encode_P1 ("MESSAGE IS THIS",2) will modify the input as:

MSAEI HSESG STI

M

S

A

E

I

H

S

E

S

G

S

T

I

o   fn decode_P1 ("MSAEI HSESG STI",2) will print the output as:

MESSAGE IS THIS

·         If the user enters 3 then

o   There will be 3 rows:

o   fn encode_P1 ("MESSAGE IS THIS",3) will modify the input as:

MAIHESG STISE S

M

A

I

H

E

S

G

S

T

I

S

E

S

o   fn decode_P1 ("MSAEI HSESG STI",3) will print the output as:

MESSAGE IS THIS

Caesar Cipher:

Eg. MESSAGE IS NOT THIS.

·       fn encode_P2("MESSAGE IS NOT THIS",3) will offset it by 3 ASCII characters.

Output:  PHVVDJH#LV#QRW#WKLV

fn decode_P2("PHVVDJH#LV#QRW#WKLV",3) will return the original statement.

Output: MESSAGE IS NOT THIS

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.

LINK FOR THE EXAMPLE FILE

Sending Automated emails from Excel via YahooMail

Hello Everyone,

I have written other blogs on sending mail from Excel using VBA via Outlook and Gmail.

This blog contains the code for sending mails from Excel using VBA via YahooMail.
This is tested from a personal free yahoo account.
Here is the code for the same:


Sub automated_email_yahooMail()
    
    Dim mail As CDO.Message
    Set mail = New CDO.Message

    With mail.Configuration.Fields
   
       '1. Setting SSL Authentication
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
       '2. Setting SMTP Authentication
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
       '3. Setting SMTP Server and Port
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mail.yahoo.com"
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
       
       
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        
       'USERID AND PASSWORD
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "emailID@yahoo.com"
       .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
        
       'Don't forget to Update the configuration fields
       .Update
    End With
    
    With mail
       .To = "emailID1@domain.com"
       .From = "emailID@yahoo.com"
       .Sender = "Warlock Solutions"
       .CC = "emailID2@domain.com"
       .BCC = "emailID3@domain.com"
       .Subject = "Test mail from VBA via YahooMail"
       .textbody = "Please respond if you've received."
       .AddAttachment "Path\attachmentName.attachmentExtension"
       .Send
    End With
    
    Set mail = Nothing
End Sub

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.

Sending Automated emails from Excel via GMAIL

Hello Everyone,

This blog contains details regarding sending automated emails from excel.

The other blog that I wrote on similar line is used to send mail via yahooMail.

This blog contains the code for sending MAILS VIA GMAIL.

This is tested from an corporate google account.

Here is the code for the same :

Sub automated_email_gmail()
   
    Dim mail As CDO.Message
    Set mail = New CDO.Message

    With mail.Configuration.Fields
  
       '1. Setting SSL Authentication
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
       '2. Setting SMTP Authentication
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
       '3. Setting SMTP Server and Port
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
      
      
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
       
       'Credentials of your Gmail Account
       'https://myaccount.google.com/
       'https://myaccount.google.com/security
       'Go to App Passwords
       'Select "Mail" in Select App
       'Select "Windows Computer" in Select device
       'Click Generate
       'Copy the password generated and paste it in the password field
      
       'USERID AND PASSWORD
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "emailID@domain.com"
       .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Case Sensitive Password"
       
       'Don't forget to Update the configuration fields
       .Update
    End With

    With mail
       .To = "emailID1@domain.com"
       .From = "emailID@domain.com"
       .Sender = "Warlock Solutions"
       .CC = "emailID2@domain.com"
       .BCC = "emailID3@domain.com"
       .Subject = "Test mail via gmail"
       .textbody = "Please respond if you've received."
       .AddAttachment "Path\attachmentName.attachmentExtension"
       .Send
    End With

    Set mail = Nothing

End Sub

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.

Expressions for Excel VBA Environ

Here's a list of few expressions for the Environ in Excel VBA

Environ("os")

Environ("tmp")

Environ("path")

Environ("path")

Environ("windir")

Environ("appdata")

Environ("appdata")

Environ("homepath")

Environ("username")

Environ("homedrive")

Environ("userdomain")

Environ("logonserver")

Environ("sessionname")

Environ("systemdrive")

Environ("userprofile")

Environ("computername")

Environ("programfiles")

Environ("allusersprofile")

Environ("processor_level")

Environ("commonprogramfiles")

Environ("processor_revision")

Environ("number_of_processors")

Environ("processor_identifier")

Environ("processor_architecture")

Will keep updating the list as and when i come across new once.

Thanks

Subhajit

Exploring The File System Object_Part 2

In my previous post I explored the few  methods of the fileSystemObject to find all the Sub Folders in a parent folder. Count of all files in them, their size and filetypes.

In this post I have used few more methods to build a macro which will do the following:

1. Does whatever was possible in the last post ;) 
2. Make different folders for different file types
3. Copy all the files from the specified folder
4. Organize the similar file types into their respective folders

This is how the excel file looks,

This is the main() subroutine which calls the organize routine,

This is the routine that organizes different file types into different folders

This is the initial and final output folders:

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.
SUBHAJIT.
.
To Download the example file:
Please click this Google Drive Link

Exploring The File System Object_Part 1

We can use the Microsoft scripting runtime object library for working easily with files and folders.
Start with opening the references and checking the Microsoft scripting runtime library.
This gives a lot of objects collections, methods and properties in the scripting library.
You can browse through them by opening the object library.

Given below is a sample code for exploring few possibilities of the file system object. 

Put any folder link in the range(A2) and see the following information about the folder.

Sub Directories, Total Count of Files, File Types, Count of File Types , Size of Folder

Please find the file link below to see and explore the file.

========================================================================

========================================================================

Will write another blog on exploring properties of file methods.

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.
SUBHAJIT.
.
To Download the example file:
Please click this Google Drive Link

Sending Automated emails - WITH GENERIC AND CUSTOMIZED CONTENT Via Outlook

Hello Everyone,


Long time I have written a blog on Excel VBA.

This blog contains details regarding sending automated emails from an excel template, which is a common thing and you'll find many other blogs for the same.


But What's different in this is:

1. You can use customized names for few user and the generic name for the rest.
2. Send a copy of the mail to an individual person or just CC to the generic address.
3. You can use customized subject for few user and the generic subject for the rest.
4. You can have customized message body for few user and the generic content for the rest.
5. You can use customized signatures for few user and the generic signature for the rest.
6. You can also have attachments for few users.

Here is the code for the same :

Sub automated_email()
    
    Dim counter As Long
    
    Dim outlookApp As Outlook.Application
    Dim outlookMail As MailItem
    Set outlookApp = New Outlook.Application
    
    Dim bodyString As String
    
    For counter = 5 To formatEmail.Cells(formatEmail.Rows.Count, "A").End(xlUp).Row

        Set outlookMail = outlookApp.CreateItem(outlookMailItem)
        outlookMail.To = formatEmail.Range("B" & counter).Value
        
        If formatEmail.Range("C" & counter).Value <> "" Then
            outlookMail.CC = formatEmail.Range("C" & counter).Value
        Else
            outlookMail.CC = formatEmail.Range("C2").Value
        End If
        
        outlookMail.BCC = formatEmail.Range("D" & counter).Value
        
        If formatEmail.Range("E" & counter).Value <> "" Then
            outlookMail.Subject = formatEmail.Range("E" & counter).Value
        Else
            outlookMail.Subject = formatEmail.Range("E2").Value
        End If
        
        bodyString = "Hi "
        
        If formatEmail.Range("A" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("A" & counter).Value & "," & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("A2").Value & "," & vbNewLine & vbNewLine
        End If
        If formatEmail.Range("G" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("G" & counter).Value & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("G2").Value & vbNewLine & vbNewLine
        End If
        If formatEmail.Range("H" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("H" & counter).Value & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("H2").Value & vbNewLine & vbNewLine
        End If
        
        outlookMail.Body = bodyString
        
        If formatEmail.Range("F" & counter).Value <> "" Then
            If Dir(formatEmail.Range("F" & counter).Value) <> "" Then
                outlookMail.Attachments.Add formatEmail.Range("F" & counter).Value
            End If
        End If
        
'        Use outlookMail.Display to review before sending
        outlookMail.Display
        
'        Use outlookMail.Send to directly send without reviewing
'        outlookMail.Send
        
    
        Set outlookMail = Nothing
    Next
       
    Set outlookApp = Nothing
    
End Sub

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.
SUBHAJIT.
.
To Download the example file:
Please click this Google Drive Link