SaveAs with a pre-defined filename
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 |
SaveAs with a pre-defined filename
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 |
SaveAs with a pre-defined filename
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 |
SaveAs with a pre-defined filename
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 |
SaveAs with a pre-defined filename
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 |
SaveAs with a pre-defined filename
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 |
All times are GMT +1. The time now is 10:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com