Selecting a Folder or Subfolder

**Rabi** · 11-08-2006, 12:59 AM

Hi everyone from a new comer

My application extracts filenames from folders or subfolders. Sometimes, the folders are deep down making it cumbersome as well as error-prone, for my colleagues (users of this application), to type the long path in a textbox.

I need to include a code which helps the users select a folder or a subfolder from a dialogbox; like the FileOpen dialogbox. The FileOpen has a restriction of choosing particular file(s). Although, I can truncate the returned string from this dialogobx to extract the path - but I don't want this method to use for some reasons.

I expect the members of the forum suggest me more professional way to solve this problem.

Regards,

Rabi

**Simon Lloyd** · 11-08-2006, 03:34 AM

Rabi, Firstly welcome to Excel Forum, if you post your code here you are more likely to get a response as it is difficult to see what you are trying to achieve without having a sample to work with.

Regards,
Simon

**Rabi** · 11-08-2006, 04:42 AM

Thanks Simon for the response. Well, I want to read a folder name on the disk (in order to extract all the files under this folder and its subfolders). Presently, my application requires to type the path, in a textbox, as below, for example:

"d:\documents\projects\reports\cities\"

I want a way to choose a folder, like the above , for example, by clicking from the list of folders instead of typing the path (sometimes much longer than the above example). Consequently, my applications extracts all the files under this folder and its subfolders.

Please note that I need to read the FolderName only, not the FileName(s).

Regards,

Rabi

**Simon Lloyd** · 11-08-2006, 05:04 AM

Hi Rabi i have some code that was supplied at another forum by Jim Cone, it searches the folder name you specify and returns all the file names with the extension you specify, it then places all the names including the path on the active worksheet, hope it helps you!

Please Login or Register  to view this content.

Regards,
Simon.

P.S Dont forget to set the runtime reference!

**Rabi** · 11-08-2006, 05:26 AM

Hi Simon

It is a great script and I am already using one similar to this one.
But, the foldername, in my case, is always different and different for every user.
What I want is to pick the foldername arbitrarily, using some dialogbox, making it easier for the users to choose one of their need.

Hope to find a solution.

Regards,

Rabi

**Simon Lloyd** · 11-08-2006, 05:37 AM

Rabi, how do you envisage

pick the foldername arbitrarily, using some dialogbox

happening if you dont first load all the names of folders to a worksheet or list?

You could always add these lines

Please Login or Register  to view this content.

then change this line

Please Login or Register  to view this content.

to

strPath = T1 &":\"

this way an inputbox will appear allowing you to choose which drive to search, then once you have a list you could populate a listbox with the findings.

Play around with adding another inputbox to specify a different folder each time you run the code.

Regards,
Simon

**Rabi** · 11-08-2006, 05:54 AM

Hi Simon!

My application is already working quite fine.

I just want to make it easier for my users to pick the foldername from some sort of interface like the "FileOpen dialogox" (instead of typing a loooong path string).

Sorry for too much interruptions and hope I have made my point more clear, this time.

Regards,

Rabi

**Simon Lloyd** · 11-08-2006, 06:02 AM

Well this line opens a dialog box

Please Login or Register  to view this content.

but ideally you should use filters to filter for the file extension you want.

Regards,
Simon

**Rabi** · 11-08-2006, 06:39 AM

Great, ultimately I am successful in making my point clear. But still, One more inch to go.
I don't want to select the file but the folder only, which is not served by;

Application.GetOpenFileName

It is because, this dialogbox makes selection of a file compulsory in order to return the "file path". It can, of course, be truncated to eliminate the filename from the string.

But, it is not a professional way of doing something for me. As far as I have some idea, to get around this problem, is that some API calls can be used. But using APIs is inappropriately heavy for this minor task.

I am hopeful for finding a smarter way of solving the problem.

Simon! I hope you don't get annoyed with such demanding query because we are here to learn something more and better. Isn't it!

Regards,

Rabi

**Simon Lloyd** · 11-08-2006, 07:26 AM

I found this code on the internet its extensive but does what you want

Please Login or Register  to view this content.

**Rabi** · 11-08-2006, 07:41 AM

This is the API solution.

I heartly thank Simon for your exhaustive help and assistance.

Best regards and wishes,

Rabi

**T-J** · 11-08-2006, 05:41 PM

Originally Posted by Rabi

I need to include a code which helps the users select a folder or a subfolder from a dialogbox; like the FileOpen dialogbox. The FileOpen has a restriction of choosing particular file(s). Although, I can truncate the returned string from this dialogobx to extract the path - but I don't want this method to use for some reasons.

I expect the members of the forum suggest me more professional way to solve this problem.

Regards,

Rabi

If using Excel 2003 or 2002, you could use the FileDialog Object, see Working with Files and Folders in Office 2003 Editions

The example given on that page shows selecting a file:
Set FD = Application.FileDialog(msoFileDialogOpen)

To select a folder change it to:
Set FD = Application.FileDialog(msoFileDialogFolderPicker)

Then for testing purposes, change Image1.Picture = LoadPicture(.SelectedItems(1)) to
Cells(1, 1) = .SelectedItems(1) to place the selected folder in cell A1

**bhofsetz** · 11-08-2006, 06:39 PM

Rabi,
I found a solution to this same problem. A great place to start is the CCRP web site. The code is a bit more complicated than simply using the GetOpenFilename command but once you get it working it is easy to implement in new projects and worth the effort.

Check out the site:

HTML Code:

HTH