Option Compare Database
Option Explicit
Public Function fChooseFile()
' Requires reference to Microsoft Office 11.0 Object Library.
Dim fDialog As Office.FileDialog
Dim varFile As Variant
' Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow user to make multiple selections in dialog box
.AllowMultiSelect = False
' Set the title of the dialog box.
.Title = "Please select one file"
'starting location
.InitialFileName = CurrentProject.path
' Clear out the current filters, and add our own.
.Filters.Clear
'''' .Filters.Add "Excel ", "*.XLSX"
.Filters.Add "Access Databases", "*.ACCDB, *.MDB"
'''' .Filters.Add "Access Projects", "*.ADP"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
'Loop through each file selected and add it to our list box.
For Each varFile In .SelectedItems
fChooseFile = varFile
'''' Me.FileList.AddItem varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Function
''https://msdn.microsoft.com/en-us/library/hww8txat%28v=vs.84%29.aspx?f=255&MSPPError=-2147217396
''https://www.exceltrick.com/formulas_macros/filesystemobject-in-vba/
''https://bettersolutions.com/vba/files-directories/file-system-object.htm
''http://www.xl-central.com/list-files-fso.html
''http://www.thevbprogrammer.com/ch06/06-09-fso.htm
Public Function fChooseDirectory()
' requires a reference to the Office xx Object library
'Declare a variable as a FileDialog object.
'Dim fd As FileDialog
'''' Const msoFileDialogFolderPicker = 4 'use for late binding
Dim fd As Object
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant
'Use a With...End With block to reference the FileDialog object.
With fd
'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems
'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
'Only one item will be returned since the file dialog is a folder picker
'MsgBox "The path is: " & vrtSelectedItem
fChooseDirectory = vrtSelectedItem
Exit Function
Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With
'Set the object variable to Nothing.
Set fd = Nothing
fChooseDirectory = "Error - nothing chosen"
End Function
