Excel VBA setting the startfolder using the UDF GetFolder()

Keebellah · Sep 4th 2018

Hi guys,
The code I copied a long time ago from the OzGrid site but I still haven't been able to figure out how to set the folder to start in using the UDF

Code

Function GetFolder(strPath As String, Optional sTitle As String) As String
Dim fldr As FileDialog
Dim sItem As String
isOk = FolderExists(strPath)
If Len(Trim(strPath)) = 0 Or isOk = False Then
    strPath = ""
End If


Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .InitialFileName = strPath
    .Title = "Select " & IIf(Len(Trim(sTitle)) > 0, sTitle, "a Folder:")
    .AllowMultiSelect = False
    .ButtonName = "Select folder"
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

Display More

I pass the parameter (which is valid) as strPath (with and without the final backslash) and it always starts in a previously selected folder.
Even if the value for InitialFileName is filled or not

the value in strPath can either be a UNC share or a local folder.

Can somebody point me in the right direction?

I cannot use ChDir because it will not work, especially if it points to a network share (UNC path)

Hope somebody has an idea.
Thanks

**Kenneth Hobson** · Sep 4th 2018

That is a function but I would not call it a UDF.

Compile code before a Run shows that FolderExists() is not a native method.

It works as expected for me.

Code

Sub Test_GetFolder()
  'MsgBox GetFolder("c:\myfiles\excel")
  MsgBox GetFolder("c:\myfiles\excel\")
End Sub


Function GetFolder(strPath As String, Optional sTitle As String) As String
  Dim fldr As FileDialog, sItem As String, isOK As Boolean
  
  'isOk = FolderExists(strPath)
  isOK = Dir(strPath, vbDirectory) <> ""
  If Len(Trim(strPath)) = 0 Or isOK = False Then
    strPath = ""
  End If
  
  Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
  With fldr
    .InitialFileName = strPath
    .Title = "Select " & IIf(Len(Trim(sTitle)) > 0, sTitle, "a Folder:")
    .AllowMultiSelect = False
    .ButtonName = "Select folder"
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
  End With
NextCode:
  GetFolder = sItem
  Set fldr = Nothing
End Function

Display More

Keebellah · Sep 5th 2018

Thanks Kenneth,
Well strictly it'snot a UDF but, what's in a name
I found the reason, why my code did not work as expected, I was under the assumption I was passing the correct parameter (a file path) but it was a filepath and name and this caused the IfFolderExists() to return false and thus not the path.
The thing is I've used this GetFolder() for ages but thought it had something to do with the UNC paths.
Well, it was more of a user's mistake and it takes a pair of extra eyes to make me look at it better.
Thanks for looking into it, and yes, it's solved.

Can't seem to find the MARK SOLVED option or button

Excel VBA setting the startfolder using the UDF GetFolder()

Participate now!

Share