Run your VBA Program Daily & Automatically!

My Client has a Query!

He wants to receive an email as a notification just before a 15 days before his employee's vacation period starts.

here is his explanation in his words : "I am thinking about a script that opens the spreadsheet once a day iterate through all the employees (about 20) then check if today's date is equal to the day that each employee will complete one year minus 15 days, and, if the result is true, send the notification. But I don't know how to implement that with code."

Here is the solution how to make this automation happened.

1. Create a workbook

2. Add Worksheet

3. Name it as Employee Manager

4. Create a Table and enter the Employees Details in line by line

5. Create a VB Program which finds the Joining Date of Employee and if it matches the criteria then It will Email you just before the vacation period starts for him/her.

6. Supposed, Employee joined on 1st Jan 2018 then on the 15th December 2018, you will receive an email about the employee and his vacations details.

7. Now, it's very normal to create process mentioned in Point 6. (If it still thought for you then don't worry... let us know, we will provide you the solution for that module too...)

Now guess, up to the point 7 everything goes according to the plan. Now, it's time to automate it!

Now suppose in the D drive, in some folder, your this workbook is placed and it isn't open even. But the Module with VB code is also there inside. You will be required to use TWO other services of Microsoft Windows for this process to automate the VB code in the module. How? Here is the process.

8. The First thing you required is to create a VB Script. yes a file with an extension of ".VBS". You have to open a Notepad and write some code and you have to save it as any name with .VBS extension. which will further create a VB Script file.

9. Now, if you click the VB script file, if everything is okay with it, then it will find the workbook, then it will find the module and then it will run the sub-procedure which will roam through each line of employee details and if your current date is 15th December 2018, then It will trigger the email procedure and will send the email to you automatically without opening the excel file. isn't its a great thing?

Following is the YouTube link to create a VBS file to trigger the sub-procedure.

Run Macro Automatically without Opening Excel File

Now the question is how can we create code to click on that VBS file automatically on each day so you don't have to do manually on each day?

10. Here is the answer, Use Windows Task Scheduler Tool. Just Target The .VBS file you have to create and configure it in such a way that it will trigger the VBS file on each day when your computer is open.

Following is the YouTube link to trigger the VBS file by Task Scheduler on daily basis.

Launch VB Script Using Task Scheduler

If you guys liked my idea then please let me know in the comment and please do like and share this article.

Thank you.

Kamal Bharakhda

Future of VBA

It's necessary to think of exactly 10 years ahead of now because it makes your actions more based on future.

well, when you are doing good something anything, the best thing you received out of it is a community of Good People. Yes, the best asset.

I met one guy on LinkedIn named Vincent ISOZ and he suggested me to provide the same VBA solution on power query. because according to him it is the future. in the next 10 years, every corporation will moving towards cloud office. Then he wrote one comment and I really felt, I should post that comment here in front of you guys, because if VBA is the tree then we are the leaves of it.

well here is his comment...

"I also worked for Microsoft as an external consultant during 4 years. VBA will be stopped in 2024 (according to the roadmap I saw but you can probably add the 3 usual years of delay) as only Office Online will still be maintained (cloud strategy of Microsoft). We already know that the replacement of VBA is Power Query (with the M language) or JavaScript with JQuery (that's one of the reasons why Office 2019 allows you to create functions in JavaScript instead of in VBA).

And almost nobody will write VBA anymore as only JavaScript Excel functions will be supported on Mac, Windows, and Online.

Microsoft is moving everything to the web using mainly Typescript that is compiled in JavaScript and powered by Electron.

But the future will tell us what will really happen (the roadmap change many times). We will speak again about this in 2024+3 = 2027. See ya!" - Vincent ISOZ

Lol, his comment stunned me. I do have lots of argument with him thereafter but the may the best commenter win the argument. 

But I know he didn't opposed the VBA Idea but he advocated him self for the upcoming demands from the industry. So he literally asked us,

are we ready to supply over demands?

Let's discuss this....

Import XML Data in Excel Using VBA

Here is the complete VBA code to import XML Data into the Excel Workbook in a completely dynamic approach. Look at the following video.

Following is the VBA Code

Sub Impor_XML_Data()
    
    'Code By Kamal Bharkahda
    Application.ScreenUpdating = False
    Dim TargetSheet As Worksheet
    Dim ChooseFIle As Variant
    Dim TargetSheetName As String
    Dim TargetCellAddress As String
    
    TargetSheetName = Application.InputBox("Write a Sheet Name where you want to place the XML Data ***Case Sensitive***", "Target Sheet Name")

    TargetCellAddress = Application.InputBox("Write a Cell Address from where XML Data Start Placing", "Target Cell Address")
    
    
    Set TargetSheet = ThisWorkbook.Sheets(TargetSheetName)
    TargetSheet.UsedRange.Clear
    
    ChooseFIle = Application.GetOpenFilename("XML File (*.xml), *.xml", , "Kamal Bharakhda", , False)
    
    If ChooseFIle = vbNullString Then Exit Sub
    
    ThisWorkbook.XmlImport Url:=ChooseFIle, ImportMap:=Nothing, Overwrite:=True, Destination:=TargetSheet.Range(TargetCellAddress)
    
    MsgBox "Import Done"
    
    Set TargetSheet = Nothing
    Application.ScreenUpdating = True
    
End Sub

Import CSV Data into the Worksheet!

Hello Friends! Merry Christmas to all of you and to your friends and family.

Let me share a Christmas present for you all.

Do you want to import CSV file into the sheet and calculate the sum of the column data into the last row?

Well here is the file! and video of the file is in the comment box. Let me know if you guys have any queries.

Thank you. and enjoy your beautiful festival season.

Regards,
Kamal.

Sub ImportCSVFile()

    'By Kamal Bharakhda @ 919328093207 [E] kamal.9328093207@gmail.com
    Application.ScreenUpdating = False
    Dim xFileName As Variant
    Dim TargetSheet As Worksheet
    Dim Rg As Range
    Dim xAddress As String
    Dim TargetRange As Range
    
    Set TargetSheet = Sheets("DATABASE")
    TargetSheet.UsedRange.Clear
    xFileName = Application.GetOpenFilename("CSV File (*.csv), *.csv", , "Kamal Bharakhda", , False)
    
    If xFileName = False Then Exit Sub
     On Error Resume Next
    Set Rg = TargetSheet.Range("A1")
    On Error GoTo 0
    If Rg Is Nothing Then Exit Sub
    xAddress = Rg.Address
    With ActiveSheet.QueryTables.Add("TEXT;" & xFileName, Range(xAddress))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 936
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    Set TargetRange = TargetSheet.Range("A:A")
    
    'Splitting the Delimited Strings
    TargetRange.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
    True
    
    With TargetSheet
        Dim TotalRow As Long
        TotalRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Cells(TotalRow + 1, 3).Value = "Sub Total"
        .Cells(TotalRow + 1, 4).Value = Application.WorksheetFunction.Sum(.Range("D2:D" & TotalRow))
        With .Rows(TotalRow + 1).Font
            .Bold = True
            .Size = 13
        End With
        .UsedRange.Columns.AutoFit
    End With
    
    MsgBox "Process Done", vbInformation, "Message from Kamal"
    
    Application.ScreenUpdating = True
End Sub

IST (Indian Standard Time) to EST (Eastern Standard Time) Zone in Excel VBA.

Use the following function to get IST Date Time Values of the cell value into EST Date-Time Value. Paste the following VBA code into Module and that's it!

'/-----------------
Function IST_to_EST(ByRef SelectRange As Range) As Date
    
    ESTValue = SelectRange.Value - (0.0416667 * 10.5)
    IST_to_EST = ESTValue

End Function
'/-----------------

See this video to get more idea on this. Please comment if you have any query.

Delete Row from the Table when you have multiple tables on the same sheet

My client has query was to delete a row from the table. It sounds easy. But when the same sheet has more than two tables starting from the same row (defiantly from the different columns) it's a bit tricky to delete only a row from the Specific table.

let's say in the same worksheet You have Table1 and Table2 and you want to delete a row from table 1 dynamically by just selecting a cell inside the table range that's how my client wants to remove a row.

I prepared the workbook to show the demo to delete a row of specific table range by selecting the cell inside of it.

Here is the Code :

Private Sub CommandButton1_Click()
    
    If ActiveCell.Value = vbNullString Then Exit Sub
    
    Const RestrictedRow As Byte = 1
    Const FirstColofTable1 As Long = 1
    Const LastColofTable1 As Long = 5
    Const FirstColofTable2 As Long = 7
    Const LastColofTable2 As Long = 11
    Const Table1LeftIndex As String = "A"
    Const Table1RightIndex As String = "E"
    Const Table2LeftIndex As String = "G"
    Const Table2RightIndex As String = "K"
    Dim ActiveCellRow As Long
    Dim ActiveColumn As Long
    
    ActiveCellRow = VBA.Val(ActiveCell.Row)
    ActiveColumn = VBA.Val(ActiveCell.Column)

    If ActiveCellRow = RestrictedRow Then Exit Sub 
        
    With Sheets("Sheet2")
        If FirstColofTable1 <= ActiveColumn And ActiveColumn <= LastColofTable1 Then
            Sheets("Sheet2").Range(Table1LeftIndex & ActiveCellRow & ":" & Table1RightIndex & ActiveCellRow).Delete shift:=xlUp
        ElseIf FirstColofTable2 <= ActiveColumn And ActiveColumn <= LastColofTable2 Then
            Sheets("Sheet2").Range(Table2LeftIndex & ActiveCellRow & ":" & Table2RightIndex & ActiveCellRow).Delete shift:=xlUp
        End If
    End With
    
    MsgBox "Deleted"
    
End Sub

If my input is 1,2 & 3 then result should be 1,2,3,12,13,23,123

Q. Need VBA code to get different combinations. Eg. if my input is 1 & 2 then the result should be 1,2,12.
If my input is 1, 2 & 3 then result should be 1,2,3,12,13,23,123

Input can go up to 20 digits also
Also, result 12 and 21 should treat as same
Similarly, 123 & 321 or 231 or 312 is the same.

here is the perfect dynamic solution. You can add more than 20 digits and it will still provide you with the perfect result.

Put the following code in the module and that's it. I was working on this since yesterday. :D

Using Spin Button, Hide & Show the column one by one in Excel VBA

Show and Hide Columns one by one by Spin button up to mentioned columns limits.

this is going to be the best small tool for excel users.

here is the video. and here is the code of Spin Button.

Private Const DefaltWidth As Integer = 9
Dim TargetSheetName As String
Dim TargetSheet As Worksheet
Dim FirstColNo As Integer
Dim LastColNo As Integer
Dim I As Integer

Private Sub SpinButton1_SpinDown()
    Application.ScreenUpdating = False
    'Assigning Values to the Variables
    TargetSheetName = "Sheet1"
    Set TargetSheet = Application.ThisWorkbook.Sheets(TargetSheetName)
    FirstColNo = TargetSheet.Range("A7").Value
    LastColNo = TargetSheet.Range("A9").Value
    If TargetSheet.Range("A7").Value = Empty Or TargetSheet.Range("A9").Value = Empty Then
        MsgBox "Please Set The  Column Range Limits Before You Proceed", vbCritical, "Kamal Bharakhda"
        Exit Sub
    End If
    'Code For Unhiding Columns One By One from Limit One to Last Column
    For I = LastColNo To FirstColNo Step -1
        If TargetSheet.Columns(I).ColumnWidth = 0 Then
            TargetSheet.Columns(I).ColumnWidth = DefaltWidth
            Exit For 'This is most important point if you want to show columns one by one.
        End If
    Next I
    Set TargetSheet = Nothing
    Application.ScreenUpdating = True
End Sub

Private Sub SpinButton1_SpinUp()
    Application.ScreenUpdating = False
    'Assigning Values to the Variables
    TargetSheetName = "Sheet1"
    Set TargetSheet = Application.ThisWorkbook.Sheets(TargetSheetName)
    FirstColNo = TargetSheet.Range("A7").Value
    LastColNo = TargetSheet.Range("A9").Value
    If TargetSheet.Range("A7").Value = Empty Or TargetSheet.Range("A9").Value = Empty Then
        MsgBox "Please Set The  Column Range Limits Before You Proceed", vbCritical, "Kamal Bharakhda"
        Exit Sub
    End If
    'Code For Hiding Columns One By One from Limit One to Last Column
    For I = FirstColNo To LastColNo Step 1
        If TargetSheet.Columns(I).ColumnWidth > 0 Then
            TargetSheet.Columns(I).ColumnWidth = 0
            Exit For 'This is most important point if you want to hide columns one by one.
        End If
    Next I
    Set TargetSheet = Nothing
    Application.ScreenUpdating = True
End Sub