Preeti's Blog

Category: VBA Codes

  • Vba Code For Joining and Spliting

    vba code for join and split

  • VBA Code To Separate Filename From The Path

    Dim fullPath() As String
    Dim wbIndex As Long
    Dim wbName as String
    Dim wbAddr as String

    ‘ Wbaddr holds the full path for example: ” D:\Documents and Settings\Desktop\Preeti\macro\Input.xls”

    fullPath() = Split(wbAddr, “\”) ‘Put  Parts of the path into the array fullPath()
    wbIndex = UBound(fullPath) ‘wbIndex holds the largest subscript
    wbName = fullPath(wbIndex) ‘wbName holds the filename i.e. Input.xls

    Here, through Split( ) we have stored the srings separated by “\”. The UBound( ) gives the maximum subscript value through which we get the filename.

  • Macro To Delete Sheets From Workbook


    To delete a sheet from the workbook without the user being prompted for confirmations, here is the code:

    Sub DeleteSheet ( ) 
          Dim SheetName As String
          ‘ deletes a sheet named SheetName in the active workbook
          Application.DisplayAlerts = False
          Sheets(SheetName).Delete
    End Sub


    Macro to delete the default sheets(remember there has to be atleast 1 sheet in the workbook, you cannot delete all.) :

    Worksheets(“Sheet1”).Delete  or Worksheets(1).Delete
    Worksheets(“Sheet2”).Delete or Worksheets(2).Delete
    Worksheets(“Sheet3”).Delete or Worksheets(3).Delete

  • VBA Code To Create A New Excel File By Deleting The Existing File

    Sub CreateFile ( )

    If fso.FileExists(wbAddr + “Output.xls”) Then
            Set wkOut = Workbooks.Open(wbAddr + “Output.xls”)
            Set wkOut = Workbooks(“Output.xls”)
            wkOut.Close
            fso.DeleteFile wbAddr + “Output.xls”
    End If
    Set wkOut = Workbooks.Add
    wkOut.SaveAs filename:=wbAddr + “Output.xls”
    Set wkOut = Workbooks.Open(wbAddr + “Output.xls”)
    Set wkOut = Workbooks(“Output.xls”)

     End Sub

    What The Code Does?

    Each time you execute the program the file,  ” Output.xls” is created if it do not exist otherwise it’s deleted and then created.  In the If part to delete the file I have closed it because any open workbook cannot be deleted. Also before closing I have opened it because a wokbook is closed only when it is open. If the workbook is already open no error will occur if you again open it.   Set wkOut = Workbooks.Open(wbAddr + “Output.xls”)  is done only to make sure the workbook is open before closing it.
     

  • VBA Code To Create An excel File

    Sub CreateNewFile ()

    Dim wkOut as Workbook
    Dim wbAddr as string 
    Dim fso
     ‘wbAddr holds the path where the file needs to be created
     ‘like wbAddr=”D:\VbaCodes” 
    Set fso = CreateObject(“Scripting.FileSystemObject”)
    Set wkOut = Workbooks.Add
    ‘Output.xls is the file to be created. A file or workbook is same.
    wkOut.SaveAs filename:=wbAddr + “Output.xls”
    Set wkOut = Workbooks.Open(wbAddr + “Output.xls”)
    Set wkOut = Workbooks(“Output.xls”)

    End Sub

  • VBA Code To Find Days In A Month

    Sub findDays()
        Dim m As Integer, y As Integer
        Dim DaysInMonth As Integer
        m = Month(January)
        y = Year(2009)
        DaysInMonth = DateSerial(y, m + 1, 1) – DateSerial(y, m, 1)
        MsgBox “Days In Month :” + Str(DaysInMonth)
    End Sub

  • Writing macros in Microsoft Excel using VBA

    Hi All,

    A month back I was assigned a work in my office. The work required writing macros in Microsoft Excel using VBA. While doing that work i got to know many VBA codes, some of which i’ll be sharing with you.