VBA - FileSystemObject Properties and Methods

Properties and Methods of the FileSystemObject Object

Description

Property/Method

BuildPath()

Appends information to a file path

CopyFile()

Copies a file from one location to another

CopyFolder()

Copies a folder from one location to another

CreateFolder()

Creates a new folder object

CreateTextfile()

Creates a new text file object

DeleteFile()

Removes a file

DeleteFolder()

Removes a folder object

DriveExists()

Determines whether a drive exists

Drives

Returns a Drives collection, containing all the available drive objects

FileExists()

Determines whether a file exists

FolderExists()

Determines whether a folder exists

GetAbsolutePathName()

Returns the absolute pathname for a file

GetBaseName()

Gets the base name of the last component

GetDrive()

Gets the drive letter for a file

GetDriveName()

Gets the drive name on which a file resides

ExtensionName()

Returns the extension for a file

GetFile()

Gets the file object

GetFileName()

Gets the name of a file

GetFolder()

Gets the folder name that contains a file

GetParentFolderName()

Gets the parent folder's name

GetSpecialFolder()

Gets the folder names for special folders

GetTempName()

Creates a randomly generated temporary file

MoveFile()

Moves a file from one location to another

MoveFolder()

Moves a folder and its contents from one location to another

OpentextFile()

Opens a text file stream to a file

VBA Shortcuts

The shortcut to open the component dialog box in Visual Basic Editor

CTRL, T

Excel VBA RoundDown and RoundUp functions

Function TestMin(DMin As Double)

        If DMin < 100 Then

            DMin = WorksheetFunction.RoundDown(DMin, 0)

        ElseIf DMin < 1000 Then

            DMin = WorksheetFunction.RoundDown(DMin, -1)

        ElseIf DMin < 10000 Then

            DMin = WorksheetFunction.RoundDown(DMin, -2)

        ElseIf DMin < 100000 Then

            DMin = WorksheetFunction.RoundDown(DMin, -3)

        ElseIf DMin < 1000000 Then

            DMin = WorksheetFunction.RoundDown(DMin, -4)

        End If

        TestMin = DMin

End Function

Function TestMax(DMax As Double)

        If DMax < 100 Then

            DMax = WorksheetFunction.RoundUp(DMax, 0)

        ElseIf DMax < 1000 Then

            DMax = WorksheetFunction.RoundUp(DMax, -1)

        ElseIf DMax < 10000 Then

            DMax = WorksheetFunction.RoundUp(DMax, -2)

        ElseIf DMax < 100000 Then

            DMax = WorksheetFunction.RoundUp(DMax, -3)

        ElseIf DMax < 1000000 Then

            DMax = WorksheetFunction.RoundUp(DMax, -4)

        End If

        TestMax = DMax

End Function

Sub test()

    MsgBox TestMin(87789.89)

    MsgBox TestMax(97979.89)

End Sub

VBA Excel How to check the date is same after reversal of day and month

Function isBeforeAfterDateSame(strDateBefore As String) As Boolean

    Dim x As String

    Dim y As String

    Dim z As String

    Dim strInDate As String

    Dim strDateAfter As String

    Dim flag As Boolean

    Dim result As Date

    

    strInDate = strDateBefore

    

    If Len(strInDate) >= 8 And Len(strInDate) <= 10 Then

        '' date format is with /  digit separator

        If VBA.InStr(strInDate, "/") > 0 Then

            If VBA.IsDate(strInDate) Then

            

                x = VBA.Split(strInDate, "/")(0)

                y = VBA.Split(strInDate, "/")(1)

                z = VBA.Split(strInDate, "/")(2)

                

                If Len(x) = 1 Then x = "0" & x

                If Len(y) = 1 Then y = "0" & y

                

                strInDate = x & "/" & y & "/" & z

                result = DateValue(strInDate)

                strOutDate = CStr(result)

            

                x = VBA.Split(strOutDate, "/")(0)

                y = VBA.Split(strOutDate, "/")(1)

                z = VBA.Split(strOutDate, "/")(2)

                

                If Len(x) = 1 Then x = "0" & x

                If Len(y) = 1 Then y = "0" & y

                

                strOutDate = x & "/" & y & "/" & z

                If strOutDate = strInDate Then

                    flag = True

                End If

            End If

        End If

    End If

    

    isBeforeAfterDateSame = flag

    

End Function

VBA Excel How to Get the nearest Monday of a date

Function GetMonday(strDate As String) As Date

    ''get the nearest Monday before the given date

    Dim wkNum As Integer

    Dim dNearestMonday As Date

    wkNum = Weekday(strDate, vbMonday)

    

    Select Case wkNum

        Case 1:

            dNearestMonday = DateValue(strDate)

        Case 2:

            dNearestMonday = DateValue(strDate) - 1

        Case 3:

            dNearestMonday = DateValue(strDate) - 2

        Case 4:

            dNearestMonday = DateValue(strDate) - 3

        Case 5:

            dNearestMonday = DateValue(strDate) - 4

        Case 6:

            dNearestMonday = DateValue(strDate) - 5

        Case 7:

            dNearestMonday = DateValue(strDate) - 6

        Case Else

            dNearestMonday = #1/1/1900#

    End Select

    GetMonday = dNearestMonday

End Function

Excel VBA How to change value of a cell using Scroll Bar

Private Sub ScrollBar1_Change()

    Application.EnableEvents = False

    ScrollBar1.Min = -100

    ScrollBar1.Max = 100

    ScrollBar1.SmallChange = 1

    ScrollBar1.LargeChange = 5

    ShtGraph1.Range("A5").Value = ScrollBar1.Value * 0.01

    Application.EnableEvents = True

End Sub

Private Sub ScrollBar1_Scroll()

    Application.EnableEvents = False

    ScrollBar1.Min = -100

    ScrollBar1.Max = 100

    ScrollBar1.SmallChange = 1

    ScrollBar1.LargeChange = 5

    ShtGraph1.Range("A5").Value = ScrollBar1.Value * 0.01

    Application.EnableEvents = True

End Sub

NOTE: The A5 cell should be in percentage format to see change of percentage value.

VBA Excel How to Clear All data in Excel sheet Range Except Formulas

Sub ClearConstantData(rng As Range)

    Dim cl As Range

    For Each cl In rng.Cells

        If Not cl.HasFormula Then

            If cl.Value <> "" Or Not (IsEmpty(cl)) Then

                cl.ClearContents

            End If

        End If

    Next

End Sub

VBA Thick Border Around Range

Option Explicit

Sub ThickBorderAround(rng As Range)

    rng.Borders(xlDiagonalDown).LineStyle = xlNone

    rng.Borders(xlDiagonalUp).LineStyle = xlNone

    With rng.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

    With rng.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

    With rng.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

    With rng.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

End Sub

VBA Replace Text In Range in Excel

Sub ReplaceTextInRange(sht As Worksheet, rngSearch As Range, strFindText As String,  strReplaceText As String)

    Dim cellFound As Range
    Dim firstAddress As String

    ActiveWorkbook.Sheets(sht.Name).Activate
 
    With rngSearch
        Set cellFound = .Find(strFindText, LookIn:=xlValues)
        If Not cellFound Is Nothing Then
            firstAddress = cellFound.Address
            Do
                cellFound.Value = VBA.Replace(cellFound.Value, strFindText, strReplaceText)
                Set cellFound = .FindNext(cellFound)
''            Loop While cellFound.Address <> firstAddress
            Loop Until cellFound Is Nothing
        End If
    End With

End Sub

Sheet Password Breaker

Sub PasswordBreaker()

'Breaks worksheet password protection.

Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

VBA Statements Cheat sheet

 Brief Information of VBA Statements

VBA Statement	What It Does
AppActivate	Activates an application window
Beep	Sounds a tone via the computer's speaker
Call	Transfers control to another procedure
ChDir	Changes the current directory
ChDrive	Changes the current drive
Close	Closes a text file
Const	Declares a constant value
Date	Sets the current system date
Declare	Declares a reference to an external procedure in a Dynamic Link Library (DLL)
DeleteSetting	Deletes a section or key setting from an application's entry in the Windows Registry
Dim	Declares variables and (optionally) their data types
Do-Loop	Loops through a set of instructions
End	Used by itself, exits the program; also used to end a block of statements that begin with If, With, Sub, Function, Property, Type, or Select
Erase	Re-initializes an array
Error	Simulates a specific error condition
Exit Do	Exits a block of Do-Loop code
Exit For	Exits a block of For-Next code
Exit Function	Exits a Function procedure
Exit Property	Exits a property procedure
Exit Sub	Exits a subroutine procedure
FileCopy	Copies a file
For Each-Next	Loops through a set of instructions for each member of a series
For-Next	Loops through a set of instructions a specific number of times
Function	Declares the name and arguments for a Function procedure
Get	Reads data from a text file
GoSub...Return	Branches to and returns from a procedure
GoTo	Branches to a specified statement within a procedure
If-Then-Else	Processes statements conditionally
Input #	Reads data from a sequential text file
Kill	Deletes a file from a disk
Let	Assigns the value of an expression to a variable or property
Line Input #	Reads a line of data from a sequential text file
Load	Loads an object but doesn't show it
Lock...Unlock	Controls access to a text file
Mid	Replaces characters in a string with other characters
MkDir	Creates a new directory
Name	Renames a file or directory
On Error	Gives specific instructions for what to do in the case of an error
On...GoSub	Branches, based on a condition
On...GoTo	Branches, based on a condition
Open	Opens a text file
Option Base	Changes the default lower limit for arrays
Option Compare	Declares the default comparison mode when comparing strings
Option Explicit	Forces declaration of all variables in a module
Option Private	Indicates that an entire module is Private
Print #	Writes data to a sequential file
Private	Declares a local array or variable
Property Get	Declares the name and arguments of a Property Get procedure
Property Let	Declares the name and arguments of a Property Let procedure
Property Set	Declares the name and arguments of a Property Set procedure
Public	Declares a public array or variable
Put	Writes a variable to a text file
RaiseEvent	Fires a user-defined event
Randomize	Initializes the random number generator
ReDim	Changes the dimensions of an array
Rem	Specifies a line of comments (same as an apostrophe ['])
Reset	Closes all open text files
Resume	Resumes execution when an error-handling routine finishes
RmDir	Removes an empty directory
SaveSetting	Saves or creates an application entry in the Windows Registry
Seek	Sets the position for the next access in a text file
Select Case	Processes statements conditionally
SendKeys	Sends keystrokes to the active window
Set	Assigns an object reference to a variable or property
SetAttr	Changes attribute information for a file
Static	Declares variables at the procedure level so that the variables retain their values as long as the code is running
Stop	Pauses the program
Sub	Declares the name and arguments of a Sub procedure
Time	Sets the system time
Type	Defines a custom data type
Unload	Removes an object from memory
While...Wend	Loops through a set of instructions as long as a certain condition remains true
Width #	Sets the output line width of a text file
With	Sets a series of properties for an object
Write #	Writes data to a sequential text file