Browse Folder

[avadhutd2]

I have a requirement where I select a cell and click on browse button.

For some specific cells, I need to find a way to code the Browse Button so that they can select only the folder path, but do NOT include a file name.

I need to specify what folder user want for result. I need to put a result file in the folder they specify.

Can anyone let me know if there is an API to open a folder?

Thanks!

[domfootwear]

I have a requirement where I select a cell and click on browse button.

For some specific cells, I need to find a way to code the Browse Button so that they can select only the folder path, but do NOT include a file name.

I need to specify what folder user want for result. I need to put a result file in the folder they specify.

Can anyone let me know if there is an API to open a folder?

Thanks!

Pls try this code:



Option Explicit
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
Dim ShellApp As Object
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, OpenAt)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
Set ShellApp = Nothing
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
BrowseForFolder = False
End Function
Sub Folder()
On Error Resume Next
Dim result As String
result = BrowseForFolder
Select Case result
Case Is = False
result = "Invalid !"
Case Else
End Select
[a1].Value = result
MsgBox "You have just selected folder " & result, _
vbOKOnly + vbInformation
End Sub

[Bob Phillips]

With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
If .Show = -1 Then

MsgBox .SelectedItems(1)
End If
End With

[avadhutd2]

Hi,

Thanks for the replies. I implemented the following code in my code and I am able to get the Browse Folder box.

I just need to add one situation here .... is it possible for me to get the path previously browsed when I use this Browse Folder code?

For example if I had browsed E:\Testfolder\SubFolder1\SubFolder2\SubFolder3\SubFolder4\

Here, if I need to browse the same folder for more locations ...e.g at 100 places, in that case I need to browse this starting from MYCOMPUTERS. Can we have something that will directly have the previously browsed path expanded & user have choice to select that directly?

If possible can anyone add the code in the attached sheet Select Folder.xls so that it would be helpful for me to check.

Thanks in advance!!

[Bob Phillips]

In mine, the shorter, more efficient, more modern code, you can set an InitialFilename property which will open at that directory.

[aysam]

Hi All
Dear xld
Could your code open excel files????????
I need it
Regards

[Bob Phillips]

No, it is a folder picker, it just returns the path of the selected folder.

[aysam]

Is there any way else to open an EXCEL file through another?????????

[lucas]

For aysam:
With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = False
If .Show = -1 Then

MsgBox .SelectedItems(1)
End If
End With

[lucas]

Change:
MsgBox .SelectedItems(1)
to:
Workbooks.Open (.SelectedItems(1))
To actually open a workbook.

[aysam]

THANKS SO MUCH LUCAS
It worked great
But
I need to add a piece of code to copy a range from the opened file and paste to the main file starting at a certain row.
thanks in advance
greatly appreciated

[lucas]

aysam, I answered you filedialog questsion here because it followed the thread somewhat.

You should really start a new thread to address your question at post #11.

When you post your question be sure to mention and post the code you have obtained from here as a starting point. That you have a way to open the file and need further assistance.

I hope you understand that it's a completely seperate question.

[aysam]

SO SORRY FOR THAT
I REALLY APPRECIATE YOUR SYSTEM AND DESCIPLINE
I'll do what you suggested
aysam

[avadhutd2]

Follow-up query for Browse Folder.

I had posted this query previously to get a folder browsed via API.

I got following answer -

Option Explicit
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
Dim ShellApp As Object
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, OpenAt)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
Set ShellApp = Nothing
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
BrowseForFolder = False
End Function
Sub Folder()
On Error Resume Next
Dim result As String
result = BrowseForFolder
Select Case result
Case Is = False
result = "Invalid !"
Case Else
End Select
[a1].Value = result
MsgBox "You have just selected folder " & result, _
vbOKOnly + vbInformation
End Sub

Here...further to add I also got one resolution when I asked for the folder to be displayed directly when clicked the Browse Folder button :-

"In mine, the shorter, more efficient, more modern code, you can set an InitialFilename property which will open at that directory."

I am not sure how to apply that in the code above....can anyone let me know where & how to put the "InitialFileName" property to get the requirements fulfilled?

Thanks in advance!

[domfootwear]

Same as my post
http://www.vbaexpress.com/forum/showpost.php?p=206557&postcount=2

[avadhutd2]

Hi domfootwear ,

Thanks for your reply...I just want to add here, that there is no query for this code. My query is -

Suppose I browsed a folder E:\TestFolder1\TestFolder2\SubFolder1\SubFolder2\

Next time when I click another cell and want to browse same folder I expect the path browsed previously should stored & displayed instead of asking user to browsed again from My Computers.

I hope my query is clear now....just need direction in the code above to modifiy to get the recently accessed folder when the API is opened.

Thanks!

[domfootwear]

Hi domfootwear ,

Thanks for your reply...I just want to add here, that there is no query for this code. My query is -

Suppose I browsed a folder E:\TestFolder1\TestFolder2\SubFolder1\SubFolder2\

Next time when I click another cell and want to browse same folder I expect the path browsed previously should stored & displayed instead of asking user to browsed again from My Computers.

I hope my query is clear now....just need direction in the code above to modifiy to get the recently accessed folder when the API is opened.

Thanks!

Try this code



Option Explicit
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
Dim ShellApp As Object
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, OpenAt)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
Set ShellApp = Nothing
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
BrowseForFolder = False
End Function

Sub Folder()
On Error Resume Next
Dim result As String
If [a1].Value = "Invalid !" Or [a1].Value = "" Then

result = BrowseForFolder
Select Case result
Case Is = False
result = "Invalid !"
Case Else
End Select

[a1].Value = result
MsgBox "You have just selected folder " & result, _
vbOKOnly + vbInformation
Exit Sub
End If
MsgBox [a1].Value
End Sub