Hi All,

I'd like to save a workbook created from a template with a macro-generated
filename, not allowing the user to change the filename, but allowing to
select a path for the workbook. How can I do that?

TIA,
Stefi

Stefi,

Here is one way. 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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Hi All,

I'd like to save a workbook created from a template with a macro-generated
filename, not allowing the user to change the filename, but allowing to
select a path for the workbook. How can I do that?

TIA,
Stefi

Thanks, Bob, I forgot to mention that I use XL2000, but I will consider to
upgrade to 2002 or 2003, because the 2002 solution is so much simpler!

Regards,
Stefi


€žBob Phillips€� ezt Ã*rta:

Stefi,

Here is one way. 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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Hi All,

I'd like to save a workbook created from a template with a macro-generated
filename, not allowing the user to change the filename, but allowing to
select a path for the workbook. How can I do that?

TIA,
Stefi

Hardly seems justification for spending that much to me :-))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Thanks, Bob, I forgot to mention that I use XL2000, but I will consider to
upgrade to 2002 or 2003, because the 2002 solution is so much simpler!

Regards,
Stefi


"Bob Phillips" ezt írta:

Stefi,

Here is one way. 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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Hi All,

I'd like to save a workbook created from a template with a
macro-generated
filename, not allowing the user to change the filename, but allowing
to
select a path for the workbook. How can I do that?

TIA,
Stefi

Hi Bob,

I changed my mind, because I gave an early answer without testing your
solution. It thought it a difficult method but it worked at once just like I
wanted, although I can't follow it. How can one learn such things?

I appreciate very much your efforts!

Regards,
Stefi


€žBob Phillips€� ezt Ã*rta:

Hardly seems justification for spending that much to me :-))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Thanks, Bob, I forgot to mention that I use XL2000, but I will consider to
upgrade to 2002 or 2003, because the 2002 solution is so much simpler!

Regards,
Stefi


"Bob Phillips" ezt Ã*rta:

Stefi,

Here is one way. 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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Hi All,

I'd like to save a workbook created from a template with a
macro-generated
filename, not allowing the user to change the filename, but allowing
to
select a path for the workbook. How can I do that?

TIA,
Stefi

Wise move, especially as Office 12 is coming out next year (ish), and that
will be worth upgrading to.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Hi Bob,

I changed my mind, because I gave an early answer without testing your
solution. It thought it a difficult method but it worked at once just like
I
wanted, although I can't follow it. How can one learn such things?

I appreciate very much your efforts!

Regards,
Stefi


"Bob Phillips" ezt írta:

Hardly seems justification for spending that much to me :-))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Thanks, Bob, I forgot to mention that I use XL2000, but I will
consider to
upgrade to 2002 or 2003, because the 2002 solution is so much simpler!

Regards,
Stefi


"Bob Phillips" ezt írta:

Stefi,

Here is one way. 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

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Hi All,

I'd like to save a workbook created from a template with a
macro-generated
filename, not allowing the user to change the filename, but
allowing
to
select a path for the workbook. How can I do that?

TIA,
Stefi