Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting an initial directory
xl2kPro
I'm looking for a "short" function that will allow the user to select an initial directory. My utility will then loop through the directory and subdirectories picking off specific filenames (common extensions). I have no problem with the second part (using the "Application.FileSearch") but cannot find a function that will allow a box to popup for the initial directory selection. I know I can "Slice/Dice" the directory name from a "GetOpenFileNames" function but that requires a file to exist in the directory, which may/will not always be the case. I could write my own routine to do this but was hoping for an "easier" way-in the deep dark (*very*) distant past I remember having to do this via an API call. Any suggestions, I've reached the end of the object browser! -- Regards; Rob ------------------------------------------------------------------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting an initial directory
I think you still need to use API or Shell commands. See:
http://www.cpearson.com/excel/BrowseFolder.htm Hope this helps Rowan RWN wrote: xl2kPro I'm looking for a "short" function that will allow the user to select an initial directory. My utility will then loop through the directory and subdirectories picking off specific filenames (common extensions). I have no problem with the second part (using the "Application.FileSearch") but cannot find a function that will allow a box to popup for the initial directory selection. I know I can "Slice/Dice" the directory name from a "GetOpenFileNames" function but that requires a file to exist in the directory, which may/will not always be the case. I could write my own routine to do this but was hoping for an "easier" way-in the deep dark (*very*) distant past I remember having to do this via an API call. Any suggestions, I've reached the end of the object browser! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting an initial directory
XL2002 has a browse dialog. I don't use 2002 myself, so you may need to play
with this, but this is basically it With Application.FileDialog(msoFileDialogFolderPicker) .Show MsgBox .SelectedItems(1) End With Look up FileDialog in the VBA help -- HTH RP (remove nothere from the email address if mailing direct) "RWN" wrote in message ... xl2kPro I'm looking for a "short" function that will allow the user to select an initial directory. My utility will then loop through the directory and subdirectories picking off specific filenames (common extensions). I have no problem with the second part (using the "Application.FileSearch") but cannot find a function that will allow a box to popup for the initial directory selection. I know I can "Slice/Dice" the directory name from a "GetOpenFileNames" function but that requires a file to exist in the directory, which may/will not always be the case. I could write my own routine to do this but was hoping for an "easier" way-in the deep dark (*very*) distant past I remember having to do this via an API call. Any suggestions, I've reached the end of the object browser! -- Regards; Rob ------------------------------------------------------------------------ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting an initial directory
'The pre XL2002 way is below. Just append the filename to the returned
'folder. Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, _ ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" _ (lpBrowseInfo As BROWSEINFO) As Long Private 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 GetFolder(Optional ByVal Name As String = _ "Select a folder.") As String '------------------------------------------------------------- Dim bInfo As BROWSEINFO Dim path As String Dim oDialog As Long bInfo.pidlRoot = 0& 'Root folder = Desktop bInfo.lpszTitle = Name bInfo.ulFlags = &H1 'Type of directory to Return oDialog = SHBrowseForFolder(bInfo) 'display the dialog 'Parse the result path = Space$(512) GetFolder = "" If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then GetFolder = Left(path, InStr(path, Chr$(0)) - 1) End If End Function Mike F "RWN" wrote in message ... xl2kPro I'm looking for a "short" function that will allow the user to select an initial directory. My utility will then loop through the directory and subdirectories picking off specific filenames (common extensions). I have no problem with the second part (using the "Application.FileSearch") but cannot find a function that will allow a box to popup for the initial directory selection. I know I can "Slice/Dice" the directory name from a "GetOpenFileNames" function but that requires a file to exist in the directory, which may/will not always be the case. I could write my own routine to do this but was hoping for an "easier" way-in the deep dark (*very*) distant past I remember having to do this via an API call. Any suggestions, I've reached the end of the object browser! -- Regards; Rob ------------------------------------------------------------------------ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting an initial directory
OP said .... xl2kPro
That is why I bothered posting after Rowan's response. Bob "Mike Fogleman" wrote in message ... 'The pre XL2002 way is below. Just append the filename to the returned 'folder. Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, _ ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" _ (lpBrowseInfo As BROWSEINFO) As Long Private 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 GetFolder(Optional ByVal Name As String = _ "Select a folder.") As String '------------------------------------------------------------- Dim bInfo As BROWSEINFO Dim path As String Dim oDialog As Long bInfo.pidlRoot = 0& 'Root folder = Desktop bInfo.lpszTitle = Name bInfo.ulFlags = &H1 'Type of directory to Return oDialog = SHBrowseForFolder(bInfo) 'display the dialog 'Parse the result path = Space$(512) GetFolder = "" If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then GetFolder = Left(path, InStr(path, Chr$(0)) - 1) End If End Function Mike F "RWN" wrote in message ... xl2kPro I'm looking for a "short" function that will allow the user to select an initial directory. My utility will then loop through the directory and subdirectories picking off specific filenames (common extensions). I have no problem with the second part (using the "Application.FileSearch") but cannot find a function that will allow a box to popup for the initial directory selection. I know I can "Slice/Dice" the directory name from a "GetOpenFileNames" function but that requires a file to exist in the directory, which may/will not always be the case. I could write my own routine to do this but was hoping for an "easier" way-in the deep dark (*very*) distant past I remember having to do this via an API call. Any suggestions, I've reached the end of the object browser! -- Regards; Rob ------------------------------------------------------------------------ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting an initial directory
Ignore me, I am cracking up.
Bob "Mike Fogleman" wrote in message ... 'The pre XL2002 way is below. Just append the filename to the returned 'folder. Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, _ ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" _ (lpBrowseInfo As BROWSEINFO) As Long Private 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 GetFolder(Optional ByVal Name As String = _ "Select a folder.") As String '------------------------------------------------------------- Dim bInfo As BROWSEINFO Dim path As String Dim oDialog As Long bInfo.pidlRoot = 0& 'Root folder = Desktop bInfo.lpszTitle = Name bInfo.ulFlags = &H1 'Type of directory to Return oDialog = SHBrowseForFolder(bInfo) 'display the dialog 'Parse the result path = Space$(512) GetFolder = "" If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then GetFolder = Left(path, InStr(path, Chr$(0)) - 1) End If End Function Mike F "RWN" wrote in message ... xl2kPro I'm looking for a "short" function that will allow the user to select an initial directory. My utility will then loop through the directory and subdirectories picking off specific filenames (common extensions). I have no problem with the second part (using the "Application.FileSearch") but cannot find a function that will allow a box to popup for the initial directory selection. I know I can "Slice/Dice" the directory name from a "GetOpenFileNames" function but that requires a file to exist in the directory, which may/will not always be the case. I could write my own routine to do this but was hoping for an "easier" way-in the deep dark (*very*) distant past I remember having to do this via an API call. Any suggestions, I've reached the end of the object browser! -- Regards; Rob ------------------------------------------------------------------------ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting an initial directory
Whew! Thought it was me!
Thanks to all for enlightening me. Rob ------------------------------------------------------------------------ "Bob Phillips" wrote in message ... Ignore me, I am cracking up. Bob <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove middle initial from "first name middle initial" | Excel Discussion (Misc queries) | |||
Selecting a Directory | Excel Programming | |||
Creating a macro that lists directory names within a directory.... | Excel Programming | |||
Selecting a directory from a form in excel | Excel Programming | |||
Directory "Locked" when selecting multiple files using GetOpenFilename | Excel Programming |