Application.FileDialog(msoFileDialogFilePicker) with dynamic InitialFileName

[Bering]

Hello guys,

I would like to modify this function so that the InitialFileName will change according to the user selected path except for the penultimate portion of the path.

For example, within a loop, if the first time the selected path is:

"U:\Mypath\MySubFolder\ABC\CDE\Filex.pdf"

I would like the function to "memorize"

.InitialFileName as "U:\Mypath\MySubFolder\ABC\"

until the end of the loop.

No luck so far..

Function GetFolderName(Optional OpenAt As String) As String
    Dim wbFullName As Variant

    With Excel.Application.FileDialog(msoFileDialogFolderPicker)
    wbFullName = Excel.Application.FileDialog(msoFileDialogFilePicker)

        .InitialFileName = "U:\Mypath\MySubFolder\"
        
        .Show
        
        GetFolderName = .SelectedItems(1)

    End With
    
    
End Function

Many thanks in advance for any suggestions!

 

[Micron]

Perhaps a module level variable where your function is located, or a public (global) variable somewhere else. No idea where based on what you've shown. Perhaps research variable scope to get an idea of what would be best. BTW, if user cancels, your code will error. You should wrap .Show in an If block, as in
If .Show Then

 

[Bering]

Perhaps a module level variable where your function is located, or a public (global) variable somewhere else. No idea where based on what you've shown. Perhaps research variable scope to get an idea of what would be best. BTW, if user cancels, your code will error. You should wrap .Show in an If block, as in
If .Show Then

Thanks I will amend that!
I tried declaring GetFolderName as a public variable between option explicit and the Sub that calls this function, however I get an error message… maybe I should use a different name?

 

[Micron]

You cannot use the same name for 2 objects like that. One object is the procedure, the other is the variable.

 

[Bering]

You cannot use the same name for 2 objects like that. One object is the procedure, the other is the variable.

Okie noted, thanks

 

[John_w]

Your question and code is a bit ambiguous. Your post shows you want to select a file and remember the parent's parent folder of the file, but your code has a function named GetFolderName which calls both FileDialog(msoFileDialogFolderPicker) and FileDialog(msoFileDialogFilePicker).

Assuming you want to use FileDialog(msoFileDialogFilePicker) to select a file and remember the folder two levels up, we can append the DOS/Windows "..\" parent folder syntax to the folder path, therefore append "..\..\" to go up 2 folder levels.

Public Sub Test_Loop()

    Dim i As Long
    Dim initialFolder As String
    Dim selectedFile As String
    
    initialFolder = ""
    For i = 1 To 4
        MsgBox "initialFolder = " & initialFolder, Title:="Loop " & i
        selectedFile = GetFileName(initialFolder)
        If selectedFile <> "" Then
            initialFolder = Left(selectedFile, InStrRev(selectedFile, "\")) & "..\..\"
            MsgBox "selectedFile = " & selectedFile & vbCrLf & vbCrLf & _
                   "next initialFolder = " & initialFolder, Title:="Loop " & i
        End If
    Next
    
End Sub


Function GetFileName(Optional OpenAt As String) As String

    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = OpenAt
        If .Show Then
            GetFileName = .SelectedItems(1)
        Else
            GetFileName = ""
        End If
    End With
    
End Function

 

[Bering]

Your question and code is a bit ambiguous. Your post shows you want to select a file and remember the parent's parent folder of the file, but your code has a function named GetFolderName which calls both FileDialog(msoFileDialogFolderPicker) and FileDialog(msoFileDialogFilePicker).

Assuming you want to use FileDialog(msoFileDialogFilePicker) to select a file and remember the folder two levels up, we can append the DOS/Windows "..\" parent folder syntax to the folder path, therefore append "..\..\" to go up 2 folder levels.

Public Sub Test_Loop()

    Dim i As Long
    Dim initialFolder As String
    Dim selectedFile As String
  
    initialFolder = ""
    For i = 1 To 4
        MsgBox "initialFolder = " & initialFolder, Title:="Loop " & i
        selectedFile = GetFileName(initialFolder)
        If selectedFile <> "" Then
            initialFolder = Left(selectedFile, InStrRev(selectedFile, "\")) & "..\..\"
            MsgBox "selectedFile = " & selectedFile & vbCrLf & vbCrLf & _
                   "next initialFolder = " & initialFolder, Title:="Loop " & i
        End If
    Next
  
End Sub


Function GetFileName(Optional OpenAt As String) As String

    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = OpenAt
        If .Show Then
            GetFileName = .SelectedItems(1)
        Else
            GetFileName = ""
        End If
    End With
  
End Function
[/CODE
[/QUOTE]

Thank you so much!!