How to select a file in VBA
Sub BrowseFile()
With Application.FileDialog(msoFileDialogFilePicker)
If .Show = -1 Then '-- if file dialog is opened without error
If .SelectedItems.Count = 1 Then
Sheet1.Range("rngFile").Value = .SelectedItems(1)
End If
End If
End With
End Sub
How to select a Folder in VBA
Sub BrowseFolder()
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = -1 Then '-- if file dialog is opened without error
If .SelectedItems.Count = 1 Then
Sheet1.Range("rngFolder").Value = .SelectedItems(1)
End If
End If
End With
End Sub
How to validate paths
Sub CheckifPathisvalid()
Dim strFilePath As String
Dim strFolderPath As String
strFilePath = Sheet1.Range("rngFile").Value
strFolderPath = Sheet1.Range("rngFolder").Value
If strFilePath = "" Then
MsgBox "Please browse file", vbCritical
Exit Sub
End If
If strFolderPath = "" Then
MsgBox "Please browse folder", vbCritical
Exit Sub
End If
'--Dir will return file name, if file is available at given path.
'--So, if dir is returning blank, it means path is invalid
If Dir(strFilePath) = "" Then
MsgBox "File path is invalid", vbCritical
Exit Sub
End If
'-- to check folder path, we need to use vbDirectory
If Dir(strFolderPath, vbDirectory) = "" Then
MsgBox "Folder path is invalid", vbCritical
Exit Sub
End If
End Sub
How to delete a file
Sub DeleteFile()
Dim strFilePath As String
strFilePath = Sheet1.Range("rngFile").Value
'--First validate the path
Kill strFilePath
End Sub
How to delete a folder
Sub DeleteAFolder()
Dim strFolderPath As String
strFolderPath = Sheet1.Range("rngFolder").Value
'--Validate folder path first
'--before deleting a folder, we need to delete all files inside that.
'--*.* means every file, *.xls* means all excel files. you can specify anything
'-- \*Jan*.xls*
Kill strFolderPath & "\*.*"
'--RmDir will delete a blank folder. if folder have files, it wont delete
'-- it will give File/Path access error.
RmDir strFolderPath
End Sub
How to create a folder
Sub CreateFolder()
Dim strLocation As String
Dim strFolderName As String
Dim strPath As String
strLocation = Sheet1.Range("rngFolder").Value
strFolderName = "Rajan"
'-- before creating a folder, we will check if same name folder is already there or not
'-- Application.PathSeparator will return "\" for windows and ":" for mac.
strPath = strLocation & Application.PathSeparator & strFolderName
If Dir(strPath, vbDirectory) = "" Then
'--MkDir is make directory to create a folder
MkDir strPath
Else
MsgBox "Folder already exists", vbCritical
End If
End Sub
Join my VBA whatsApp Group to know more
How to copy file
Sub CopyFile()
Dim strSource As String
Dim strDestination As String
'--Assign your path
strSource = "YourPath"
strDestination = "YourNewPath"
'--Validate your Source and Desitnation path
'--Copy file
FileCopy strSource, strDestination
End Sub
Leave a Reply
Want to join the discussion?Feel free to contribute!