Dialog box for selecting directory/path, which way is best?

[srj19]

I've seen many posts advocating the use of BrowseFolder.zip located at
http://www.oaltd.co.uk/MVP/Default.htm


And some others saying to use the FileDialog object (if you use Excel 2002, or older(?))


I have to admit both are above my prior experience level, but I think I can do it if I understand what their function, requirements, and output are.


All I need is a dialog box that preferably lets me select the default directory, and then allows the user to select the target directory.

Any suggestions?


Thanks,
Scott

 

[BrianB]

The API function " SHBrowseForFolder Lib "shell32.dll" .. etc. is Ok but is so long that it needs a whole macro module to itself. I prefer to use GetOpenFileName. Not only is there less code, but we get to see the files in the folder too - helping to make sure we have the right one.

'===================================================================================
'- BROWSE FOR A FOLDER USING GetOpenFileName
'- Although normally used to select a file, here it is used to get just the path
'===================================================================================
'-
Sub GET_FOLDER_PATH()
    Dim MyPath As String
    MsgBox ("Please browse to the required folder and click 'Open'." _
            & vbCr & "No need to select a file")
    MyPath = Application.GetOpenFileName("All Files (*.*),*.*", , "FOLDER REQUIRED")
    If MyPath = "False" Then Exit Sub       ' Cancel button
    '--------------------------------------------------------------------------------
    '- USER  MAY HAVE SELECTED A FILE. check for ".". Remove any file name.
    If InStr(1, MyPath, ".", vbTextCompare) > 0 Then
        For c = Len(MyPath) To 1 Step -1
            If Mid(MyPath, c, 1) = "\" Then
                MyPath = Left(MyPath, c)
                Exit For
            End If
        Next
    End If
    '-------------------------------------------------------------------------------
    MsgBox ("Path = " & MyPath)
End Sub

 

[Ivan F Moala]

Re: Dialog box for selecting directory/path, which way is be

I've seen many posts advocating the use of BrowseFolder.zip located at
http://www.oaltd.co.uk/MVP/Default.htm


And some others saying to use the FileDialog object (if you use Excel 2002, or older(?))


I have to admit both are above my prior experience level, but I think I can do it if I understand what their function, requirements, and output are.


All I need is a dialog box that preferably lets me select the default directory, and then allows the user to select the target directory.

Any suggestions?


Thanks,
Scott

Scott, what you need to undertand is that the Browse for Folder ONLY functionality is NOT availiable in some versions of Excel, hence Stevens Browse.zip function and BrianB's version (Which works for all versions as it uses the brosw for FILE functionality)
Xl97 + 2000 DID NOT support the browse for Folder ... all other versions onwards do.
If you know what Version you are programing for then use the appropriate Function. There are other ways to do this besides API.

 

[srj19]

All users will be using Excel 2003 and onward. So it sounds like I'll be ok and not have to get too fancy, as for using a function to do this....I generally am all self taught and find my solutions by the hunt and peck method on this forum and the net in general. This is a hap-hazzard way to do it, and I've not run into a solution other than those mentioned by meself and others, can anyone stear me toward the simplest function for this?

Thanks,

Scott

 

[srj19]

Thanks for the help, I ran the code provided and it's nice, with the exception that I may not always have a file in the chosen folder.

Ivan you said in your post:
"If you know what Version you are programing for then use the appropriate Function. There are other ways to do this besides API.
"

Are you saying here that there is an appropriate function available to me in the newer versions of Excel that I can use that doesn't require the prior mentioned approaches?

Thanks,
Scott

 

[Ivan F Moala]

example

Sub fnfolder()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
    If .Show Then
        MsgBox .SelectedItems(1)
    End If
End With

End Sub

Lookup FileDialog in help

 

[srj19]

Data Overflow Error?

Does anyone have exerience with this error? I created and ran code that seemed to work fine on my machine but generated this error on a wo-workers machine.

I'm not aware of doing anything that would have consumed massive amounts of memory space.

I can post code but it's quite long, and the problem seems to be of a general nature.

Anyone seen this before? Thanks,

Scott

 

[Ivan F Moala]

Post your code

 

[srj19]

Sorry for the confustion, I thought I had posted a new thread on an overflow error I was getting but as you can see it ended up here. For the record, the intermitent problem was caused by intermitent division by 0



Thanks,

Scott

 

[BrianB]

may not always have a file in the chosen folder

As stated, you do not need one.