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

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