Excel VBA‎ > ‎

File Manipulation in Excel VBA


Return a workbook object, the one selected by the standard dialog of the Getopenfilename. Also only XLS, XLSX and XLSM file are shown in the dialog box. Also provide with the possibility to put a title in the dialog box

Function ReturnWorkbookFromPath(Optional defaultpath As String, Optional mytitle As String) As Workbook

    Dim fileToOpen As String
    Dim myfolder As String
    Dim myworkbook As Workbook

    Application.ScreenUpdating = False
    If defaultpath <> "" Then
        ChDrive defaultpath
        ChDir defaultpath

    End If

    fileToOpen = Application.GetOpenFilename("Excel *.xls, *.xlsx; *.xlsm; *.xls;*.csv", , mytitle)

    If fileToOpen <> "False" And fileToOpen <> "Faux" Then

        Application.DisplayAlerts = False
        Application.EnableEvents = False

        On Error Resume Next
        Set myworkbook = ReturnWorkbookFromPath(fileToOpen)
        On Error GoTo 0
        If myworkbook Is Nothing Then

            Set myworkbook = Workbooks.Open(fileToOpen)
        End If
        Application.EnableEvents = True
        Application.DisplayAlerts = True

    End If
    Set ReturnWorkbookFromPath = myworkbook
End Function

Return True if the file exists, even if it is hidden.
'Arguments: strFile: File name to look for. Current directory searched if no path included.
'           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
'Note:      Does not look inside subdirectories for the file.

Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean

    Dim lngAttributes As Long

    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory)    'Include folders as well.
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
    End If

    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function