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

GetSaveAsFilename ?

Regards,
Per

"Ludo" skrev i meddelelsen
...
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

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.

Mike F
"Per Jessen" wrote in message
...
GetSaveAsFilename ?

Regards,
Per

"Ludo" skrev i meddelelsen
...
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

Another routine using the function Get Folder to create a list of sub
folders within a particular folder.

Sub FolderList()
Dim iFolder As Long
Dim oFSO As Object
Dim oFolder As Object
Dim oFldr As Object

iFolder = 11
Set oFSO = CreateObject("Scripting.FileSystemobject")
Set oFolder = oFSO.GetFolder("C:\Documents and Settings\Mike\My
Documents\Excel")

For Each oFldr In oFolder.subfolders
iFolder = iFolder + 1
Cells(iFolder, "H").Value = oFldr.Name
Next oFldr

Set oFolder = Nothing
Set oFSO = Nothing

End Sub

Mike F
"Mike Fogleman" wrote in message
...
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.

Mike F
"Per Jessen" wrote in message
...
GetSaveAsFilename ?

Regards,
Per

"Ludo" skrev i meddelelsen
...
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

On 1 sep, 15:02, "Mike Fogleman" wrote:
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.

Mike F"Per Jessen" wrote in message

...



GetSaveAsFilename ?

Regards,
Per

"Ludo" skrev i meddelelsen
...
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- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Thanks a lot Mike,

Works perfect, also for Excel 2003

Regards,
Ludo

On 1 sep, 15:02, "Mike Fogleman" wrote:
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

Then perhaps you should scrap my original method and use something by Chip
Pearson that has an option for a starting folder.

First, you must set a reference to the "Microsoft Shell Controls And
Automation" library. In VBA, go to the Tools menu, choose References, and
scroll down in the list to "Microsoft Shell Controls And Automation" and
check the checkbox. Then, copy the following code into a standard code
module.


Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Function BrowseFolder(Optional Caption As String, _
Optional InitialFolder As String) As String

Dim SH As Shell32.Shell
Dim F As Shell32.Folder

Set SH = New Shell32.Shell
Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, InitialFolder)
If Not F Is Nothing Then
BrowseFolder = F.Items.Item.Path
End If

End Function

You can call the function above with code like the following:


Dim FName As String
FName = BrowseFolder(Caption:="Select A
Folder",InitialFolder:="C:\MyFolder")
If FName = vbNullString Then
Debug.Print "No folder selected."
Else
Debug.Print "Folder Selected: " & FName
End If



Mike F

"Ludo" wrote in message
...
On 1 sep, 15:02, "Mike Fogleman" wrote:
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

Watch for word wrap in example code - lines 2 & 3 should be all one line for
FName =

Mike F
"Mike Fogleman" wrote in message
. ..
Then perhaps you should scrap my original method and use something by Chip
Pearson that has an option for a starting folder.

First, you must set a reference to the "Microsoft Shell Controls And
Automation" library. In VBA, go to the Tools menu, choose References, and
scroll down in the list to "Microsoft Shell Controls And Automation" and
check the checkbox. Then, copy the following code into a standard code
module.


Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Function BrowseFolder(Optional Caption As String, _
Optional InitialFolder As String) As String

Dim SH As Shell32.Shell
Dim F As Shell32.Folder

Set SH = New Shell32.Shell
Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS,
InitialFolder)
If Not F Is Nothing Then
BrowseFolder = F.Items.Item.Path
End If

End Function

You can call the function above with code like the following:


Dim FName As String
FName = BrowseFolder(Caption:="Select A
Folder",InitialFolder:="C:\MyFolder")
If FName = vbNullString Then
Debug.Print "No folder selected."
Else
Debug.Print "Folder Selected: " & FName
End If



Mike F

"Ludo" wrote in message
...
On 1 sep, 15:02, "Mike Fogleman" wrote:
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