FileDialog pass starting Folder as a variable

[bbrazeau]

I have the following in my DB and it works as long a s the line .InitialFileName is a hard coded string. I would however like to be able to pass a variable to the function that hones in on the proper directory a little closer. I'm tryin g to pass a variable and use it in .InitialFileName= Any one know if/How to do this???

Public Function fGetPath(Optional vStartFolder As String) As String
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
Dim strStartFolder As String
strStartFolder = vStartFolder
strStartFolder = "C:\Users\bbrazeau\Desktop\MyDB\" & strStartFolder
Set f = Application.FileDialog(3)
MsgBox (strStartFolder)
With f
.InitialFileName = "C:\Users\bbrazeau\Desktop\MyDB\"
f.AllowMultiSelect = True
If f.Show Then
For Each varItem In f.SelectedItems


strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder: " & strFolder & vbCrLf & _
"File: " & strFile
fGetPath = varItem
Next
End If
End With
Set f = Nothing
End Function

[pbaldy]

You add a second variable to the function declaration, just like vStartFolder (though if you use Optional I'd test it for a value before using).

[bbrazeau]

Hi PBaldy, Thanks for the reply. I do have a second variable "strStartFolder", and I did test it's output, the msgbox displays the correct string, but if I replace .InitialFileName = "C:\Users\bbrazeau\Desktop\MyDB\" with .InitialFileName = strStartFolder
I get method not supported

[bbrazeau]

Hi again, I got it! It was simple. Trick was to just use the variable as passed. No need to assign it to a local variable I used it as "vStartFolder" and it worked just fine.
What this does is put the user in the proper sub directory of the current customer directory, which keeps the user from having to remember and navigate to the correct directory. Less error prone.

Public Function fGetPath(vStartFolder As String) As String
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
Set f = Application.FileDialog(3)
With f
.InitialFileName = vStartFolder
f.AllowMultiSelect = True
If f.Show Then
For Each varItem In f.SelectedItems
strFile = Dir(varItem)
strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder: " & strFolder & vbCrLf & _
"File: " & strFile
fGetPath = varItem
Next
End If
End With
Set f = Nothing
End Function