File Folder Operation in VBA

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

Learn many more things like these, Join our Online VBA Training program.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

Send us mail