Saturday, October 27, 2012

How to Find Number of Days, Weeks, Months, Quarters between Two Dates

Excel VBA Macro to Know Number of Days, Weeks, Months, Quarters between Two Dates.
You can also know the Names of Days, Weeks, Months  Between Two Dates.
Sub DatesDiff()
Dim X As Date  'Or You Can Declare as String
Dim Y As Date 'Or You Can Declare as String
Dim DD As String

X = "01-May-2012" 'Or You Can Declare as #01-May-2012#
Y = "01-AUG-2012" 'Or You Can Declare as #01-AUG-2012#

'To Know No.of Days Between Two Dates
DD = DateDiff("D", X, Y)
MsgBox "The No.of Days Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

DD = DateDiff("Y", X, Y)
MsgBox "The No.of Days Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

'To Know No.of Months Between Two Dates
DD = DateDiff("M", X, Y)
MsgBox "The No.of Months Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

'To Know No.of Weeks Between Two Dates
X = "01-May-2010"
Y = "01-MAY-2012"
DD = DateDiff("WW", X, Y)
MsgBox "The No.of Weeks Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

'To Know No.of Weekdays(Suppose 'Saturday'as on 01-May2010) Between Two Dates
X = "01-May-2010"
Y = "01-MAY-2012"
DD = DateDiff("W", X, Y)
MsgBox "The No.of ThisWeekDays Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

'To Know No.of Years Between Two Dates
X = "01-May-2010"
Y = "01-MAY-2012"
DD = DateDiff("YYYY", X, Y)
MsgBox "The No.of Years Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

'To Know No.of Quarters Between Two Dates
X = "01-May-2010"
Y = "01-MAY-2012"
DD = DateDiff("Q", X, Y)
MsgBox "The No.of Quarters Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

End Sub

Thanks,TAMATAM

How to Find Number of Days, Weeks, Months, Quarter In the Given Date Year using Excel VBA Macro

Excel VBA Macro To Know Number of Days, Weeks, Months, Quarter In the Given Date Year.
You can also know the Names of the Days, Weeks, Months In the Given Date

Sub MyYear_Month_DayNames()
Dim X As String
Dim Y As String

X = #10/24/2012#

'To Know Week Number (Eg: 4th Week) In the Current Year as of Given Date
Y = Weekday(X)
MsgBox "Week No.of The Month In Given Date " & " " & X & " " & " Is: " & Y

'To Know  Weekday Name of the Given Date
Y = WeekdayName(Weekday(X))
MsgBox "Week Name of The Month In Given Date " & " " & X & " " & " Is: " & Y

'To Know Month Number of the Given Date
Y = Month(X)
MsgBox "Month Number of The Month In Given Date " & " " & X & " " & " Is: " & Y


'To Know Month Name of the Given Date
Y = MonthName(Month(X))
MsgBox "Month Name of The Month In Given Date " & " " & X & " " & " Is: " & Y

'To Know Year of the Given Date
Y = Year(X)
MsgBox "Year of The Given Date " & " " & X & " " & " Is: " & Y

'To Know No. of Days of the Given Date
Y = Day(X)
MsgBox "The No. of Days in Given Date" & "  " & X & " Are: " & Y

'To Know Current Quarter of the Year as of Given Date
Y = DatePart ("Q", X)
MsgBox "The Current Quarter of the Year In Given Date" & "  " & X & " Is: " & Y

End Sub

Thanks,Tamatam

Monday, October 22, 2012

How to Rename and Move a File to another Location using Excel VBA Macro

Excel VBA Macro To Rename and Move A File to Another Location
Sub Rename_Move_One_File()
Dim MyFilePath As String
Dim MyTargetFolder As String

MyFilePath = "C:\Documents and Settings\Administrator\My Documents\SourceFolder\"
MyTargetFolder = "F:\TargetFolder\"

'You can change the Path and File Name

Name MyFilePath & "Sample.xlsx" As MyTargetFolder & " NewSample.xlsx"
End Sub

Thanks,Tamatam

How To Copy a File From One Path to Another Path

Excel VBA Macro To Copy  a File From One Path to Another Path
Sub Copy_All_Folder_Files()
Dim SourceFolderPath As String
Dim TargetFolderPath As String
Dim FSO As Object

SourceFolderPath = "C:\Documents and Settings\Administrator\My Documents\SourceFolder"

'File Path Should Specify Exactly
'TargetFolderPath = "C:\Documents and Settings\Administrator\My Documents"
TargetFolderPath = "F:\TargetFolder"

If Right(SourceFolderPath, 1) = "\" Then
SourceFolderPath = Left(SourceFolderPath, Len(SourceFolderPath) - 1)
End If

'If Right(TargetFolderPath, 1) = "\" Then
'TargetFolderPath = Left(TargetFolderPath, Len(TargetFolderPath) - 1)
'End If

Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FolderExists(SourceFolderPath) = False Then
MsgBox ("Source Folder Does Not Exist or Path Not Found")
End If

If FSO.FolderExists(TargetFolderPath) = False Then
MsgBox ("Target Folder Does Not Exist or Path Not Found")
End If

On Error Resume Next
FSO.CopyFolder SourceFolderPath, TargetFolderPath
MsgBox "SuccessFull Copied"

End Sub

Thanks,Tamatam

How to Copy or Move a File from one Folder to another Folder using Excel VBA Macro

Excel VBA Macro To Copy or Move a File from one location to another location
Sub Copy_One_File()
Dim SourceFilePath As String
Dim TargetFolderPath As String
Dim FSO As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

SourceFile = "C:\Documents and Settings\Administrator\My Documents\SourceFolder\Sample.xlsx"

'File Path Should Specify Exactly With File Extension
TargetFolderPath = "C:\Documents and Settings\Administrator\My Documents\TargetFolder\"

If Right(TargetFolderPath, 1) <> "\" Then
TargetFolderPath = TargetFolderPath & "\"
End If

'Checking the File Existence using FSO Method 
If FSO.FileExists(SourceFile) = False Then
MsgBox ("Source File Does Not Exist in Path")
End If

'Checking the File Existence using DIR Method
If Dir(SourceFile) = vbNullString Then
MsgBox ("Source File Does Not Exist in Path")
End If

On Error Resume Next

FSO.CopyFile SourceFile, TargetFolderPath
MsgBox "SuccessFull Copied"

'FSO.MoveFile SourceFilePath, TargetPath  'To Move
'MsgBox "SuccessFull Moved"

End Sub

Thanks,Tamatam

How to Open a Special Folder using VBA Macro

Excel VBA Macro to Open a Special Folder
Sub GetSpecialFolder()
'Special Folders are : 
'AllUsersDesktop, AllUsersStartMenu
'AllUsersPrograms, AllUsersStartup, Desktop, Favorites
'Fonts, MyDocuments, NetHood, PrintHood, Programs, Recent
'SendTo, StartMenu, Startup, Templates

'Get Favorites Folder and Open it
Dim WShell As Object
Dim SpecialPath As String
  
Set WShell = CreateObject("WScript.Shell")
SpecialPath = WShell.SpecialFolders("Favorites")
MsgBox SpecialPath
'Open Folder in Explorer
Shell "Explorer.Exe " & SpecialPath, vbNormalFocus
       
'The Window Style Named Argument has These Values:
'Constant Value Description
'vbHide : 0 Window is hidden and focus is passed to the hidden window. The vbHide constant is not applicable on Macintosh platforms.
'vbNormalFocus : 1 Window has focus and is restored to its original size and position.
'vbMinimizedFocus : 2 Window is displayed as an icon with focus.
'vbMaximizedFocus : 3 Window is maximized with focus.
'vbNormalNoFocus : 4 Window is restored to its most recent size and position. The currently active window remains active.
'vbMinimizedNoFocus : 6 Window is displayed as an icon. The currently active window remains active.
End Sub

Thanks,Tamatam

How to Delete Whole Folder With All Files using VBA Macro

Excel VBA Macro to Delete Whole Folder With All Files
Sub Delete_Whole_Folder()
    Dim FSO As Object
    Dim MyPath As String

    Set FSO = CreateObject("Scripting.FileSystemObject")

    MyPath = "C:\Documents and Settings\Administrator\My Documents\Sample"
'Sample is the Folder to Delete
    If Right(MyPath, 1) = "\" Then
        MyPath = Left(MyPath, Len(MyPath) - 1)
    End If

    If FSO.FolderExists(MyPath) = False Then
        MsgBox MyPath & " Folder Doesn't Exist In Given Path", , "Filder Does Not Exist"
        Exit Sub
    End If

    FSO.DeleteFolder MyPath

End Sub

Thanks,Tamatam

How to Delete all Files and Sub Folders In a Folder using VBA

VBA Macro To Delete all Files and Sub Folders In a Folder
'Be Sure That No File is Open in The Folder
Sub Clear_All_Files_SubFolders_In_Folder()
    Dim FSO As Object
    Dim MyPath As String

    Set FSO = CreateObject("Scripting.FileSystemObject")
MyPath = "C:\Documents and Settings\Administrator\My Documents\Sample"

    If Right(MyPath, 1) = "\" Then
        MyPath = Left(MyPath, Len(MyPath) - 1)
    End If

    If FSO.FolderExists(MyPath) = False Then
        MsgBox MyPath & " Folder Doesn't Exist"
        Exit Sub
    End If

    On Error Resume Next
    'Delete Files
    FSO.DeleteFile MyPath & "\*.*", True
    'Delete SubFolders
    FSO.DeleteFolder MyPath & "\*.*", True

End Sub

Thanks,Tamatam

Saturday, October 13, 2012

What are Date and Time Functions In VBA

The Date and Time Functions in VBA:
Function
Description
Now

This Function Returns Current date and time.
Example:
1/7/12 5:16:38 pm returned by Now



Date


This Function Returns Current date only.
Example:
1/7/12 returned by Date


Time



This Function Returns Current time only.
Example:
5:16:38 pm returned by Time

Timer

This Function Returns Number Of Seconds Since Midnight.

Example:
5:16:38 Pm Returned By Timer

Timevalue()

This Function Returns Time part of argument.

Example:
5:16:38 pm returned by Timevalue(Now)


Datevalue()

This Function Returns Date part of argument (Used for ordering by date)
Example:
select * from TblPeople order by datevalue(review)


Dateserial()

This Function Returns Date Part Of Three Arguments: Year, Month, Day

Example:
Having Invoicedate <= Dateserial(Year(Now), Month(Now)-1, Day(Now))

Dateserial Handles January Correctly In The Above Example


Datepart()

This Function Returns Returns A Portion Of The Date.
Year example:
2012 Returned By Datepart('YYYY', Date)

Month Example:
10 returned By Datepart('M', #10/11/2012#)

Week Of Year Example:
41 returned by Datepart('ww', #10/11/2012#)

Day Of Week Example:
Monday Returned By Datepart('D', #6/3/2012#)

Quarter example:
4 returned by Datepart('Q', #10/11/2012#)


Year()

This Function Returns the Year Portion of the date argument.


Month()

This Function Returns the Month Portion of the date argument.


Day()

This Function Returns the Day Portion of the date argument.


Monthname()

This Function Used to Format Month Names.
July returned by Monthname(month(date))


Weekdayname()

This Function Used To Format Day Names.
Wednesday returned by WeekdayName(Weekday(Date))



Today()


This Function  Returns Current date only; used in Excel, not available in Access.
Datediff()

This Function  Returns The Difference In Dates.

Days example:
-656 returned by Datediff("D", #10/11/2001#, #12/25/1999#)

Months Example:
1 returned by Datediff("M", #8/10/2000#, #9/14/2012#)

Days Example:
0 returned by Datediff("M", date1, date2)
0 is returned above only if the two dates have same month and year


Dateadd()

This Function Add and Subtract Dates.

10/11/2002 returned by Dateadd("yyyy", 1, #10/11/2012#))

Today's Date + 30 Days Returned By Dateadd("d", 30, date)

The date 45 days ago returned by Dateadd("D", -45, Date)

To Find Monday Of A Week:
Dateadd("D",-Weekday(Date)+2,Date)


Format()

This Function Very useful for Formatting Dates.
Examples:

Wed, 5-July-2012 Returned By Format(Date,"DD-MMMM-YYYY")
5-Jul-12  Returned By
Format(Date,"D-MM-YY")



Thanks,Tamatam

Sunday, October 7, 2012

What Is the difference between Dim and ReDim Statements in VBA

How to use Dim and ReDim Statements in VBA
Dim Statement:
Variables declared with Dim at the script level are available to all procedures within the script. At the procedure level, variables are available only within the procedure
Syntax:
Dim VarName[([subscripts])]
[, VarName[([subscripts])]] .. 

You can also use the Dim statement with empty parentheses to declare a dynamic array. After declaring a dynamic array, use the ReDim statement within a procedure to define the number of dimensions and elements in the array. If you try to redeclare a dimension for an array variable whose size was explicitly specified in a Dim statement, an error occurs.
Eg:
Dim x as Integer 
 'Declaring a Variable  or Array
Dim y(10) as String

ReDim Statement :
Used to declare the dynamic-array variables, allocates or reallocates storage space at procedure level.
Syntax:
ReDim [Preserve] VarName(subscripts) 
[, VarName(subscripts)] . . .

The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array.
Eg:
Dim x As String
Dim y ()  As String

ReDim y(20)          
‘ReSizing or Reinitialization of Array Size /Variable Value
ReDim x =" TPR"

If you use the Preserve keyword, you can resize only the last array dimension, and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array.

The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

ReDim X(20, 20, 20)
. . .
ReDim Preserve X(20, 20, 25)

Caution : 
If you make an array smaller than it was originally, data in the eliminated elements is lost.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog