VBA FileDialog

[mlahajnar]

Hello, me again

In my task database, coworker wants to be able to link a file (report,...) to a task, when its completed. I already got it all set up for adding, also got it working to add a link to one file. Here is the catch, right now I am using this:

Public Function FileSelection() As String
    Dim objFD As Object
    Dim strOut As String
    strOut = vbNullString
    Set objFD = Application.FileDialog(3)
    If objFD.Show = -1 Then
        strOut = objFD.SelectedItems(1)
    End If
    Set objFD = Nothing
    FolderSelection = strOut
End Function

This one is only able to select files in dialog box. I know you can select DIR if you use Application.FileDialog(4), but I wonder, if it's possible to be able to select either file or directory, or will I have to make 2 separate dialogs?

 

[Ranman256]

add this after user picks a file, then you can get the Dir

i = InStrRev(sFilePath, "\")
If i > 0 Then vDir = Left(sFilePath, i)

 

[mlahajnar]

Yeah I know about that, but I want the user to be able to pick a dir in the dialog. Sometimes he will add a file, sometimes a whole dir, if you get what I'm trying to say. I don't know when I have to use the dir and when the whole path.

 

[arnelgp]

Public Function FileDirSelection(Optional bolPickFiles As Boolean = True) As String
    Dim objFD As Object
    Dim strOut As String
    strOut = vbNullString

    If bolPickFiles Then
        Set objFD = Application.FileDialog(msoFileDialogFilePicker)
        With objFD
            .Title = "Select a file"
            .Filters.Add "All files", "*.*", 1
            .Filters.Add "Excel Workbooks", "*.xls;*.xlsx;*.xlsm", 2
            .Filters.Add "Access Database", "*.mdb;*.mde;*.accdb;*.accde", 3
        End With
    Else
        Set objFD = Application.FileDialog(msoFileDialogFolderPicker)
        objFD.Title = "Select a folder"
    End If
    
    objFD.AllowMultiSelect = False
    
    If objFD.Show = -1 Then
        strOut = objFD.SelectedItems(1)
    End If
    Set objFD = Nothing
    FolderSelection = strOut
End Function

call it without parameter (default is file picker), call it with parameter FileDirSelection(False) and it function like a directory picker.

 

[mlahajnar]

Thanks for the reply.

If I understand this correctly, I would need to ask the user via form, if he wants to pick files or folders. I kinda don't like that idea, but if there is no other way, I guess it will have to do.

Thanks anyway

 

[arnelgp]

If I understand this correctly, I would need to ask the user via form, if he wants to pick files or folders. I kinda don't like that idea

of course you want your users to enter data through form, otherwise you'll database is just naked and anyone can mess with your database object directly.

In my task database, coworker wants to be able to link a file (report,...) to a task, when its completed.

from where are they linking the file, should be through Form with command button that execute FilePicker/Folder picker.

 

[mlahajnar]

Yeah, I think you understood me wrong.
I have a button that adds or opens, depends if link is already added, link. I have the command button, but from how I understand that code, I would have to ask the user if he wants to add a file or directory. That would mean that when he would click that button, another form would popup, asking if file/dir, and only then the actual filedialog would popup.

Am I wrong?

 

[arnelgp]

as i've said the default action of the function (without parameter) is file picker, you can call the function from your present command button on your form, like:

strFile = FileDirSelection()

if you wish you can add another command button on the form for Directory Picker function, and On Click event of that button:

strDir = FileDirSelection(False)

 

[mlahajnar]

So there is no way you can choose either file or dir in the same dialog?

 

[arnelgp]

Public Function FileDirSelection() As String
    Dim objFD As Object
    Dim strOut As String
    strOut = vbNullString
    
    Dim vbAnswer As Long
    
    vbAnswer = MsgBox("Do you wish to pick a File or a Folder?" & vbNewLine & vbNewLine & _
    "Press (Yes) to pick a File." & vbNewLine & _
    "Press (No) to pick a Folder." & vbNewLine & _
    "Cancel to close this window", vbQuestion + vbYesNoCancel)
    
    Select Case vbAnswer
    Case vbYes
        Set objFD = Application.FileDialog(msoFileDialogFilePicker)
        With objFD
            .Title = "Select a file"
            .Filters.Add "All files", "*.*", 1
            .Filters.Add "Excel Workbooks", "*.xls;*.xlsx;*.xlsm", 2
            .Filters.Add "Access Database", "*.mdb;*.mde;*.accdb;*.accde", 3
        End With
    Case vbNo
        Set objFD = Application.FileDialog(msoFileDialogFolderPicker)
        objFD.Title = "Select a folder"
    Case Else
        Exit Function
    End Select
    
    objFD.AllowMultiSelect = False
    
    If objFD.Show = -1 Then
        strOut = objFD.SelectedItems(1)
    End If
    Set objFD = Nothing
    FolderSelection = strOut
End Function

 

[mlahajnar]

Yeah I got that already, i guess you can't do what I wanted. Anyway thanks will use this.