How do you brose a directory? 3

[jsnunez]

Hi all.

I need some VBA code that will allow anuser to browse diectorires and send back the path to the selected directory.

thanks
jose

 

[fumei]

Send it back...where? Think this one through.

User browses to folderA, which has 10 subfolders. You want the path to folderA to be sent back....what, as a message to the users? As a string that you are going to do something with...store it perhaps?

The user only briefly stops at folderA because they are going to folderA/subfolder7. Do you want TWO path statements sent back...again to where? After all, they browsed TWO places.

Say subfolder7 has two more subfolders. User browses to subsubfolder2. Do you now want THREE path statements "sent back"?

Please clarify.

Gerry

 

[cLFlaVA]

Dim thePath as String
thePath = Application.GetSaveAsFilename

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[jsnunez]

Hi all

I need in VBA to browse directories or folder.

Let's say that an user clicks in a button, and browse like when he/she clicks the "My Computer" icon.

The user is going to select the folder when he wants to exports some files that the VBA applicaiton generates.

Thanks
jose

 

[fumei]

Sorry, but you are going to have to build it. You can call up the Save dialog by code, but if you do not actually save anything AT THE TIME, I don't know how you would record the selected folder, and pass that on to another procedure.

To build it you will need to use the FileSystemObject and two comboboxes (dropdowns). One loads the drives, the other loads folders. You will need to build logic that updates the folder combobox from the current value of the drives combobox.

This is if you want full browsing capabilities. If you know a number of specific folders that will take the files, then it is MUCH easier. Then you simply fill a combobox with that list. The user selects the folder from the list, and that can be passed to the procedure that is generating the files.

Full browsing, but NOT actually doing something - in other word, the user looks at a folder will be tricky. Do you understand the problem. I see you have posted another request for the same information.

You can open a folder browsing dialog to the user. The user browses and find the folder they want to have the program send the files to. Think about it.

Are they going to be saving a file with this dialog? No. The program will be doing that. Therefore if you display the Save dialog, the user will eventually cancel out. You are asking for something to remember where they were. That is why I asked the questions above. You can ignore it and post another post, but the problem is not going to go away.

Good luck. It can be done however.

Gerry

 

[IvanMoala]

If you just need to browse for a Folder and NOT files then try this.......

'// Minimum DLL version shell32.dll version 4.71 or later
'// Minimum operating systems   Windows 2000, Windows NT 4.0 with Internet Explorer 4.0,
'// Windows 98, Windows 95 with Internet Explorer 4.0
'// objFolder = objShell.BrowseForFolder(Hwnd, sTitle, BIF_Options [, vRootFolder])

Public Function BrowseForFolderShell( _
    Optional Hwnd As Long = 0, _
    Optional sTitle As String = "Browse for Folder", _
    Optional BIF_Options As Integer = BIF_VALIDATE, _
    Optional vRootFolder As Variant) As String

Dim objShell As Object
Dim objFolder As Variant
Dim strFolderFullPath As String

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(Hwnd, sTitle, BIF_Options, vRootFolder)

If (Not objFolder Is Nothing) Then
    '// NB: If SpecFolder= 0 = Desktop then ....
    On Error Resume Next
    If IsError(objFolder.Items.Item.path) Then strFolderFullPath = CStr(objFolder): GoTo GotIt
    On Error GoTo 0
    '// Is it the Root Dir?...if so change
    If Len(objFolder.Items.Item.path) > 3 Then
        strFolderFullPath = objFolder.Items.Item.path & Application.PathSeparator
    Else
        strFolderFullPath = objFolder.Items.Item.path
    End If
Else
    '// User cancelled
    GoTo XitProperly
End If

GotIt:
BrowseForFolderShell = strFolderFullPath

XitProperly:
Set objFolder = Nothing
Set objShell = Nothing

End Function

Sub TesterI()
'// STD test
Dim strFolder As String

strFolder = BrowseForFolderShell(, , , 0)

If strFolder = vbNullString Then
    MsgBox "You cancelled"
Else
    MsgBox strFolder
End If

End Sub

Ivan F Moala

http://www.xcelfiles.com

 

[fumei]

Curious to try this, but
Optional BIF_Options As Integer = BIF_VALIDATE

gives an error as variable not defined. This states BIF_Options = another variable not declared in this function.

I have shell32.dll explicitly referenced.

Gerry

 

[TonyJollans]

Hi Gerry,

In this case the Validate option (which should have a value of &H20) will be ignored - it is only relevant if BIF_EDITBOX (&H10) is also specified, to force validation of a user-typed folder name in the optional edit box.

You can change the line of code to simply ..

[blue]Optional BIF_Options As Integer, _[/blue]

btw, do you sleep? or have you moved timezones?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[IvanMoala]

Sorry .... missed the const

Thanks Tony

Ivan F Moala

http://www.xcelfiles.com

 

[KenWright]

Dump this little lot into a module and then edit the last block ( Sub YourMacro() ) to put your code into it. It is the GetDir bit that goes out and gets your path for you. I have this in a routine where we generate a .csv file from an Excel sheet and it gives you the option of where to store the output file, so although irrelevant to the rest of the code I have left it in that first block so you can see where it is used:-

Not my code I hasten to add, but pinched unashamedly from one of the other guys in the MS groups.

Dim UserFile As String
Dim whatnow As String

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer

'   Root folder = Desktop
    bInfo.pidlRoot = 0&

'   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If

'   Type of directory to return
    bInfo.ulFlags = &H1

'   Display the dialog
    x = SHBrowseForFolder(bInfo)

'   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function

Sub GetDir()
     
Dim Msg As String
whatnow = "continue"

On Error Resume Next

Msg = "Select Directory to Store Output File in."
UserFile = GetDirectory(Msg) & "\"
If UserFile = "" Then
   whatnow = "finish"
   MsgBox "Canceled"
      ElseIf Not ContinueProcedure Then
        whatnow = "finish"
        Exit Sub
   
End If

End Sub

Private Function ContinueProcedure() As Boolean
   Dim Config As Integer
   Dim Ans As Integer
   Config = vbYesNo + vbQuestion + vbDefaultButton2
   Ans = MsgBox(UserFile & "    <<< Is This The Correct Directory?", Config)
   If Ans = vbYes Then
      ContinueProcedure = True
      Else: ContinueProcedure = False
   End If
   
End Function


'Routines above generate Folder Browse
'====================================================================================
'Routines below need to call GetDir to generate Browse window.



Sub YourMacro()

'your code
'------------------------------------------
    Filename = InputBox("Enter the Task filename.  You do not need to add the .TSK extension. It will be added automatically", "Task File - filename.TSK")
    
    'Get the Directory Filepath for the output file
    Call GetDir
        
    Filename = UserFile & Filename + ".TSK"
    ActiveWorkbook.SaveAs Filename:=Filename, FileFormat:=xlCSV, _
        CreateBackup:=False
'------------------------------------------
'your code

End Sub

Regards
Ken..................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[fumei]

Hi Tony, that is what I figured, but wanted to make sure...so I asked.

To answer your question - no, I don't sleep. I have a sleep disorder that fires a pulse into my nervous system and wakes me up every 90 seconds. It is really cool seeing it on the computer in sleep labs. This major spike in all the brain waves. It is like a clock, which is probably why I have an acute sense of time.

I average about 2 hours of (interrupted) sleep a day. Been this way for as long as I remember. I think I had about four hours of sleep one night about 15 years ago.

BTW: I'm serious.

Gerry

 

[PHV]

And what about this ?
Function PickFolder(strStartDir As Variant) As String
Dim SA As Object, F As Object
Set SA = CreateObject("Shell.Application")
Set F = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
If (Not F Is Nothing) Then
PickFolder = F.Items.Item.Path
End If
Set F = Nothing
Set SA = Nothing
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Sorry to hear that, Gerry. Sometimes I think it would be nice not to need sleep, but not to be able to, that's a different thing altogether. Perhaps I could lend you a bit of my partially dead nervous system - the original cause is lost in the mists of time but I had a (reasonably successful) operation about 10 years ago on the vertibrae in my neck to ease the pressure on my spinal cord.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[fumei]

Note to jsnunez - use PHV's code. It does exactly what you want. The function returns a string that is the folder path that is selected. Clean and simple.

Star to you PHV.

Gerry

 

[IvanMoala]

Yes, use PHV's code for std browsing. It is only when you need to get a little more control and ensure no Errors that you will need to look @ the CLSID const.

Ivan F Moala

http://www.xcelfiles.com