Folders and File Handling in Excel VBA

Folder and File Handling in Excel VBA Examples help you to create, delete,copy,move files and folder. And customize File or Folder dialog Box using VBA.

• Check if Folder Exists using Excel VBA
• Opening Folders using VBA Excel
• Creating Folders in Excel VBA
• Copying Folders From One Location to Another in Excel VBA
• Move Folder From One Location to Another in Excel VBA
• Deleting Folders in VBA Excel
• Make File Read Only in VBA Excel
• Copy all Excel Files One Folder to Another in VBA Excel
• Opening Files Using File Dialog Box in Excel VBA
• Customize File or Folder Dialog Box in VBA Excel
• Excel VBA File Dialog Box – Displaying Vanilla Dialog Box to Pick Files

Check if Folder Exists using Excel VBA

'In this Example I am checking if "C:Temp" exits
Sub sbCheckingIfAFolderExists()

Dim FSO
Dim sFolder As String

sFolder = "C:Temp" ' You can Specify Any Folder To Check It
Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FolderExists(sFolder) Then
    MsgBox "Specified Folder Is Available", vbInformation, "Exists!"
Else
    MsgBox folder &"Specified Folder Not Found", vbInformation, "Not Found!"
End If

End Sub

Opening Folders using VBA Excel

'In this Example I am Opening a Folder ("C:Temp")
Sub sbOpeningAFolder()

Dim FSO
Dim sFolder As String
sFolder = "C:Temp"  'You can specify your Folder which you wants to Open

Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FolderExists(sFolder) Then
    MsgBox "Specified Folder Not Found", vbInformation, "Folder Not Found!"
ElseIf FSO.FolderExists(sFolder) Then
    Call Shell("explorer.exe " &sFolder, vbNormalFocus)
End If

End Sub

Creating Folders in Excel VBA

'In this Example I am creating a Folder "C:SampleFolder"
Sub sbCreatingAFolder()

Dim FSO
Dim sFolder As String

sFolder= "C:SampleFolder" ' You can Specify Any Path and Name To Create a Folder
Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FolderExists(sFolder) Then
    FSO.CreateFolder (sFolder) 'Checking if the same Folder already exists
    MsgBox "New FolderCreated Successfully", vbExclamation, "Done!"
Else
    MsgBox "Specified Folder Already Exists", vbExclamation, "Folder Already Exists!"
End If

End Sub

Copying Folders From One Location to Another in Excel VBA

'In this Example I am Coping "C:Temp" to "D:Job"
Sub sbCopyingAFolder()

Dim FSO
Dim sFolder As String, dFolder As String

sFolder = "C:Temp"  'Specify Your Source Folder
dFolder = "D:Job" ' Specify Your Destination Folder
Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FolderExists(dFolder) Then
    FSO.CopyFolder sFolder, dFolder
     MsgBox "Folder Copied Successfully to The Destination", vbExclamation, "Done!"
Else
    MsgBox "Folder Already Exists in the Destination", vbExclamation, "Folder Already Exists!"
End If

End Sub

Move Folder From One Location to Another in Excel VBA

'In this Example I am Moving "C:Temp" to "D:Job"
Sub sbMovingAFolder()

Dim FSO
Dim sFolder As String, dFolder As String

sFolder = "C:Temp"  'Specify Your Source Folder
dFolder = "D:Job" ' Specify Your Destination Folder
Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FolderExists(dFolder) Then
    FSO.MoveFolder sFolder, dFolder
     MsgBox "Folder Moved Successfully to The Destination", vbExclamation, "Done!"
Else
    MsgBox "Folder Already Exists in the Destination", vbExclamation, "Folder Already Exists!"
End If

End Sub

Deleting Folders in VBA Excel

'In this Example I am Deleting "C:SampleFolder"
Sub sbDeletingAFolder()

Dim FSO
Dim sFolder As String

sFolder = "C:SampleFolder" 'Specify Your Folder Which You Wants to Delete
Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FolderExists(sFolder) Then
    FSO.DeleteFolder sFolder
    MsgBox "Specified Folder Deleted Successfully", vbExclamation, "Done!"
Else
    MsgBox "Specified Folder Not Found", vbExclamation, "Not Found!"
End If

End Sub

Make File Read Only in VBA Excel

Sub sbMakeFileReadOnly()

    Dim strSaveFilename As String
    Dim oFSO As Object
    Dim oFile As Object

    sFile = "C:ExampleFile.xls" 'Your File name and Path to make it read only
    'Create Objects
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = oFSO.GetFile(FilePath:=sFile)

    'Set file to be read-only
    oFile.Attributes = 1

    'Releasing Objects
    If Not oFSO Is Nothing Then Set oFSO = Nothing
    If Not oFile Is Nothing Then Set oFile = Nothing

End Sub

Copy all Excel Files One Folder to Another in VBA Excel

'In this Example I am Coping all excel files from one Folder ("C:Temp") to another Folder ("D:Job")
Sub sbCopyingAllExcelFiles()

Dim FSO
Dim sFolder As String
Dim dFolder As String

sFolder = "C:Temp" ' change to match the source folder path
dFolder = "D:Job" ' change to match the destination folder path
Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FolderExists(sFolder) Then
    MsgBox "Source Folder Not Found", vbInformation, "Source Not Found!"
ElseIf Not FSO.FolderExists(dFolder) Then
    MsgBox "Destination Folder Not Found", vbInformation, "Destination Not Found!"
Else
    FSO.CopyFile (sFolder & "*.xl*"), dFolder
    MsgBox "Successfully Copied All Excel Files to Destination", vbInformation, "Done!"
End If

End Sub

Opening Files Using File Dialog Box in Excel VBA

Solution: You can get the file name using file dialog and open it by using Workbooks.Open method

Sub OpenWorkbookUsingFileDialog()

Dim fdl As FileDialog
Dim FileName As String
Dim FileChosen As Integer

Set fdl = Application.FileDialog(msoFileDialogFilePicker)

'Set the caption of the dialog box,
fdl.Title = "Please Select a Excel Macro File"
'Set the InitialFile Path
fdl.InitialFileName = "c:"
'Set the Folder View
fdl.InitialView = msoFileDialogViewSmallIcons
'Set the filter
fdl.Filters.Clear
fdl.Filters.Add "Excel Macros Files", "*.xlsm"

FileChosen = fdl.Show

If FileChosen <> -1 Then
    'Not choosen anything / Clicked on CANCEL
    MsgBox "You have choosen nothing"
Else
    'display name and complete path of file chosen
     MsgBox fdl .SelectedItems(1)
End If

FileName = fdl.SelectedItems(1)
'Open the File
Workbooks.Open (FileName)

End Sub

Customize File or Folder Dialog Box in VBA Excel

Solution: You can set differ properties of the file-dialog box to look more prettier

Sub CustomizingFileDialog()

Dim fdl As FileDialog
Dim FileChosen As Integer

Set fdl = Application.FileDialog(msoFileDialogFilePicker)

'Set the caption of the dialog box,
fdl.Title = "Please Select a Excel Macro File"
'Set the InitialFile Path
' determine the initial folder selected
fdl.InitialFileName = "c:"
'Set the Folder View
fdl.InitialView = msoFileDialogViewSmallIcons
'Set the filter
fdl.Filters.Clear
fdl.Filters.Add "Excel Macros Files", "*.xlsm"

FileChosen = fdl.Show

If FileChosen <> -1 Then
    'Not choosen anything / Clicked on CANCEL
    MsgBox "You have choosen nothing"
Else
   'display name and complete path of file chosen
    MsgBox fdl.SelectedItems(1)
End If

End Sub

Excel VBA File Dialog Box – Displaying Vanilla Dialog Box to Pick Files

Solution: You can use Application.FileDialog(msoFileDialogFilePicker) method

Sub ChooseFileUsingFileDialog()

Dim fld As FileDialog
Dim FileChosen As Integer

Set fld = Application.FileDialog(msoFileDialogFilePicker)
FileChosen = fld.Show

If FileChosen <> -1 Then
    'Not choosen anything / Clicked on CANCEL
    MsgBox "You have choosen nothing"
Else
   'display name and complete path of file chosen
   MsgBox fld.SelectedItems(1)
EndIf

End Sub