Open and Close Workbook - VBA

Introduction

    In this post you can learn how to Open and Close a workbook using VBA in Microsoft Excel.

Objective:

    A Macro which can open and close Excel workbook.

Solution:

    Let's see a VBA code, which does it.

Simple Method:

    Macro purpose: To open & close a excel workbook

Sub OpenRCloseWorkbook()     
    On Error Resume Next
    Set wk = Workbooks.Open("C:\MyExcel.xlsx")
    
    'Error Handling If file not found
    If Err.Number = 1004 Then
        MsgBox "File Not Found", vbCritical, "Warnings"
        Err.Clear
        Exit Sub
    End If
    
    ActiveWorkbook.Close
End Sub

Tips :

If you want to close a workbook without the user being prompted about saving the workbook, use the below code

    ActiveWorkbook.Close True 'SaveChanges:=TRUE
    ActiveWorkbook.Close False 'SaveChanges:=FALSE

Dynamic Method:

    This GetOpenFilename method displays the standard open Dialog box where user can select the file.

Sub OpenRCloseWorkbook()
    Dim MyFile As String

    MyFile = Application.GetOpenFilename()
    Workbooks.Open MyFile

    Filename = ActiveWorkbook.Name
    Workbooks(Filename).Close True
End Sub

                      !********************* Let me know how it works J ***************************!

Delete Files and Folders - VBA

In this blog you will learn how to:

• Delete a file
• Delete all the files in a folder
• Delete/Create a directory using VBA 

Please note that Kill command permanently deletes the file. we cant "undo" the delete.

'Macro Purpose: To Create/Remove a folder

Sub Create_RemoveDirectory()

    On Error Resume Next

    Kill "C:\Test\*"          ' Deletes all the files in the folder Test

    RmDir "C:\Test\"       ' Deletes empty folder

    MkDir "C:\Test"         ' Creates a Folder name Test

    On Error GoTo 0

End Sub

'Macro Purpose : Deletes given file & deletes all *.TXT files in current directory.

Sub DeleteFile()

    On Error Resume Next

    Kill ("C:\Test1.xlsx")

    Kill ("C:\Test\*.txt")

    On Error GoTo 0

End Sub

Execute the code step by step for better understanding

Sub SampleProcedure()

    Dim fpath As String, ffile As String

    fpath = "C:\Test\"

    ffile = Dir(fpath + "*.txt")

    

    Do While ffile <> ""

        Kill (fpath & ffile)

        ffile = Dir

    Loop        

    MsgBox "Files deleted in the given folder"

End Sub

                                                Hope this would help beginners J

Function to Add Business days - Vb.Net

Introduction

      In many cases developers have a demand of calculation and manipulations with business dates.

Scenario:

      We want to add “N” business days/working days to a current date (Or from a specified date). Where “N” is the number of business days.

Solution:

      In this blog I have described how to add “N” business days/working days to a date.

The below C# function does it all for you. Simply pass in a date, along with the number of working days you want to add.

Public Function AddBusinessDays(ByVal DateIn As DateTime, ByVal Days2Add As Integer) As DateTime

        Dim resultDate As DateTime

        ' Adds the [Days2Add] number of working days to DateIn

        resultDate = DateIn.AddDays(Days2Add)

        ' Loops and adds only non-weekend day(working days)

        While Weekday(resultDate) = 1 Or Weekday(resultDate) = 7

            resultDate = resultDate.AddDays(IIf(Days2Add < 0, -1, 1))

        End While

        Return resultDate

 End Function

And this's how you call it in your application:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim newDate As DateTime = AddBusinessDays(Today, 5)
        MsgBox("The 5th Business day from today is " & newDate)
    End Sub

The above code is tested.

Function to Add Business days - C#

Introduction

      In many cases developers have a demand of calculation and manipulations with business dates.

Scenario:

      We want to add “N” business days/working days to a current date (Or from a specified date). Where “N” is the number of business days.

Solution:

      In this blog I have described how to add “N” business days/working days to a date

The below C# function does it all for you. Simply pass in a date, along with the number of working days you want to add.

public DateTime AddBusinessDays(DateTime DateIn, int Days2Add)
{
    DateTime resultDate;
    // Adds the N (Days2Add) working days to DateIn (Today)
    resultDate = DateIn.AddDays(Days2Add);
         
    // Loops and adds only non-weekend day(working days)
    while (resultDate.DayOfWeek == DayOfWeek.Saturday || resultDate.DayOfWeek == DayOfWeek.Sunday)
    {
        resultDate = resultDate.AddDays((Days2Add < 0 ? -1 : 1));
    }
    return resultDate;
}

And here's how you call it in your application:

private void button1_Click(object sender, EventArgs e)
{
    DateTime newDate = AddBusinessDays(DateTime.Today, 6);
    MessageBox.Show("The 6th Business day from today is {0}", newDate.ToString());
}

The above code is Tested