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