VBA code example Get File Name or Directory Name

VBA code example Get File Name or Directory Name

FileDialogs

Office exposes capability to invoke inbuilt functions which can be used to interact to read/write inputs. An example of Open file dialog which belongs to Application class as below:

Open File Dialog

Public Function GetFile() As String
	On Error GoTo errh
	GetFile = ""
	Dim fd As FileDialog
	Dim fName As String  ' Includes full path
	Dim fChosen As Integer
	Dim fNameFile As String  'Only the name of the file
	
	'Get dialog instance
	Set fd = Application.FileDialog(msoFileDialogFilePicker)
	
	'Set dialog title
	fd.Title = "Please select a case file"
	'Clear Filters
	fd.Filters.Clear
	'Prevent multiple document selection
	fd.AllowMultiSelect = False
	'add filter
	fd.Filters.Add "Word Document", "*.docx"
	'Show dialog
	fChosen = fd.Show

	If fChosen <> -1 Then
		MsgBox "No file selected"
	Else
		GetFile = fd.SelectedItems(1)
	End If

errh:
	If Err.Number <> 0 Then
		MsgBox "Error while processing FileDialog Operation " & Err.Description
	End If
End Function

Above method returns a valid file name including file path. If user cancels action or make no file selection it returns empty string which can be validated outside call using if else conditional block.

Select Directory

Public Function GetDirectory() As String
    On Error GoTo errh
    GetDirectory = ""
    Dim fd As FileDialog
    Dim fName As String  ' Includes full path
    Dim fChosen As Integer
    Dim fNameFile As String  'Only the name of the file
    
    'Get dialog instance
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    'Set dialog title
    fd.TITLE = "Please select a valid directory"
    
    'Prevent multiple document selection
    fd.AllowMultiSelect = False
    
    'Show dialog
    fChosen = fd.Show

    If fChosen <> -1 Then
        MsgBox "No file selected"
    Else
        GetDirectory = fd.SelectedItems(1)
    End If

errh:
    If Err.Number <> 0 Then
        MsgBox "Error while processing directory " & Err.Description
    End If
End Function

Above method will allow user to browse a directory in windows and returns complete path of selected directory as result.

Next Add Paragraph in Word Document

Leave a Reply

Your email address will not be published.