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

srj19

Board Regular
Joined
Aug 23, 2006
Messages
152
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
Code:
'===================================================================================
'- 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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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

Board Regular
Joined
Aug 23, 2006
Messages
152
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

Board Regular
Joined
Aug 23, 2006
Messages
152
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
example

Code:
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

Board Regular
Joined
Aug 23, 2006
Messages
152
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
 

srj19

Board Regular
Joined
Aug 23, 2006
Messages
152
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
 

Forum statistics

Threads
1,181,420
Messages
5,929,801
Members
436,696
Latest member
Mr Rice

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top