Folders and File Handling in Excel 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
