Select folder path

[Ludo]

Hi,

How can i return a folder path into a variable from an empty folder?

What i have to do is as follow.

I need to create a PDF file from an Excel sheet and save this on a
server.
Depending on the type of unit, need i to save this in a different
folder

What i like to do is something similar as with the GetOpenFileName.
Problem is that this does'n return anything if the folder is empty.

Any help is welcome,
Regards,
Ludo

 

[Per Jessen]

GetSaveAsFilename ?

Regards,
Per

 

[Ludo]

The following is a Function that will bring up a browser window to select
your folder:

Option Explicit

'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

Sub Folder()
Dim c As Range, rng As Range
Set rng = Range("H1:H10")
For Each c In rng
c.Value = GetFolder
Next c
End Sub

Sub Folder is an example of how you can use the function to put the folder
name on the worksheet.






- Tekst uit oorspronkelijk bericht weergeven -

Thanks a lot Mike,

Works perfect, also for Excel 2003

Regards,
Ludo

 

[Ludo]

The following is a Function that will bring up a browser window to select
your folder:

Option Explicit

'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

Hi Mike,

I'm using this routine and it works perfect, but i still have a
question.
The folder path i want to select (most cases) is a 'base' path and a
subdirectory in the base path.
example of base path : \\bvwsrv01\BVW\AV\Operations\HASS\2.Utility and
Log file\ErrorDataBase Logging\
sub path : KDU1080
Result = \\bvwsrv01\BVW\AV\Operations\HASS\2.Utility and Log file
\ErrorDataBase Logging\KDU1080

Is it possible to 'preset' the base path, so that i dont have to start
searching form the Desctop up to where i need to be?

Any help welcome.

Regards and thanks a lot for the help
Ludo