Select Directory Only

[RECrerar]

Hi

I would like my user to be able to select the driectory that they wish to save the document in, but not to actually save it or specify the name of the document.

This is because, once the directory has been specified I will create a new folder in the directory and then save the document in that, with a name that is automatically generated by the program.

Thanks, in advance for any help

[lucas]

See if this helps.....Ken Puls contribution that I use daily.

[RECrerar]

Thanks, just had a quick look and seems to be exacty what I need. Will have a go at incorporating it in a bit and get back to you is i hit any problems.

Thanks to Ken Puls as well

[RECrerar]

If there a way to ammend the code in the box so that it inly populates the list box with just folders not all files?

I assume that if a file name had a dot in it, you can tell that it can't be a folder and therefore should not be added to the list. Or can you define the type of item added to the list to just be file folders?

[lucas]

Yeah, just comment out the file search section. See attached.

[RECrerar]

ha ha, that's hillarious, you should have seen my attempts to make it not let you select the files, that was so simple. Thanks

[lucas]

Glad to help.

[fumei]

Interesting. The first version hangs every time. Not only that but - and I didn't know this before - the userform title itself changes to File List (Not Responding)! I have to reboot out.

[RECrerar]

I've just found that if I just run the userform on its own, it works perfectly, but have just tried to run it as part of the sub and it doesn't stop the sub from continuing, that is the userform unloads as soon as the userform initialise sub has completed.

[lucas]

I would like to have more feedback on this because I have not been having any problems at all with this form.

[RECrerar]

The form is working perfectly for me, my problems with it shutting early I think were just due to my missunderstandings of how word works.

It is working just how I want it to as is the whole program.

[fumei]

As an alternative, you can use this Function from PHV (from Tek-Tips). This is what I use generally. The function returns the selected folder.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", _
16 + 32 + 64, strStartDir)
If (Not f Is Nothing) Then
PickFolder = f.Items.Item.Path
End If
Set f = Nothing
Set SA = Nothing
End Function

Sub ExampleTest()
Msgbox PickFolder("c:\")
End SubWhatever folder the user selected - and you can drill down to where ever you want - would be returned to the messagebox.

Or:Sub GetUserFolder()
Dim strTheirPick As String
strTheirPick = PickFolder("c:\")
' do whatever with the folder name
' they selected - make a new folder...whatever
End Sub

Note: PickFolder("") will display the dialog with the default, ie. MyDocuments. All drives are visible, including network drives, and also includes access to adminstrative folders via Control Panel.

[lucas]

That's very handy Gerry....got one for browse for file?

[fumei]

There are various ways. Here is one using API.Option Explicit
Private Declare Function GetOpenFileName _
Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
(pOpenfilename As OpenFileName) As Long

Private Type OpenFileName
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Function BrowseForFile(sInitDir As String, _
Optional ByVal sFileFilters As String, _
Optional sTitle As String = "Open File", _
Optional lParentHwnd As Long) As String

Dim tFileBrowse As OpenFileName
Const clMaxLen As Long = 254

tFileBrowse.lStructSize = Len(tFileBrowse)

'Replace friendly deliminators with nulls
sFileFilters = Replace(sFileFilters, "|", vbNullChar)
sFileFilters = Replace(sFileFilters, ";", vbNullChar)
If Right$(sFileFilters, 1) <> vbNullChar Then
'Add final delimiter
sFileFilters = sFileFilters & vbNullChar
End If

'Select a filter
tFileBrowse.lpstrFilter = sFileFilters & _
"All Files (*.*)" & vbNullChar & "*.*" _
& vbNullChar
'create a buffer for the file
tFileBrowse.lpstrFile = String(clMaxLen, " ")
'set the maximum length of a returned file
tFileBrowse.nMaxFile = clMaxLen + 1
'Create a buffer for the file title
tFileBrowse.lpstrFileTitle = Space$(clMaxLen)
'Set the maximum length of a returned file title
tFileBrowse.nMaxFileTitle = clMaxLen + 1
'Set the initial directory
tFileBrowse.lpstrInitialDir = sInitDir
'Set the parent handle
tFileBrowse.hwndOwner = lParentHwnd
'Set the title
tFileBrowse.lpstrTitle = sTitle

'No flags
tFileBrowse.flags = 0

'Show the dialog
If GetOpenFileName(tFileBrowse) Then
BrowseForFile = Trim$(tFileBrowse.lpstrFile)
If Right$(BrowseForFile, 1) = vbNullChar Then
'Remove trailing null
BrowseForFile = Left$(BrowseForFile, _
Len(BrowseForFile) - 1)
End If
End If
End Function

Sub Test()
' this will return any file
MsgBox BrowseForFile("c:\")
' this will return only .xls files
MsgBox BrowseForFile("c:\", "Excel File (*.xls);*.xls", _
"Open Workbook")

End Sub

[lucas]

Another for the library...thanks Gerry.

[fumei]

Hopefully you are putting your collection/library in a global template. In my Word VBA course the very very first thing I start them with is making a code holder. For the rest of the course ALL code is put in there. That way they can walk away with their own add-ins essentially.

I think I have about three hundred procedures in my main global. Some very tiny ones 'tis true. VBA XTools.dot is a fairly big file - 413 Kb. But as globals are not "really" loaded - just parsed, I have never noticed any performance liability. Mind you four Gb of RAM helps....ahem. But wait, at work I only I GB, and I don't notice anything there either. Nah.

[lucas]

Yep, global with all my macro's and menu's. I've been paying attention Gerry. Nothing in Normal...

[fumei]

Good. A blossoming Word person. Although that may not be all that much of a compliment....after all, it IS different.

[lucas]

I guess a lot of it has to do with which application you started with. I used to think I didn't really need Word but I have discovered that some things are easier and quicker if done with Word.

[fumei]

Oh....like what? Something to do with.....text perhaps?

[lucas]

Yep, and template use as taught by you Gerry was a major breakthrough.