Restrict File Name with getsaveasfilename

[millzner]

I have created an Inventory System using Excel with Userforms. I am trying to create a "Backup" function into the program where I create a new workbook and copy all my sheets to the new workbook. I am using the getsaveasfilename option to save the backup file. I set my initial file name in the "Save As" dialog box and all is working great. My question is, can I restrict the user from changing the initial file name I load? Basically I only want the user to be able to change the file path and not the file name before the save takes place.

 

[GTO]

What version of Excel?

 

[GTO]

You could strip the path, like:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ChooseFolder()<br><SPAN style="color:#00007F">Dim</SPAN> strFolder <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    strFolder = Application.GetSaveAsFilename(InitialFilename:="WhateverYouWant.xls", _<br>                                              FileFilter:="Excel Workbooks (*.xls),*.xls")<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> strFolder = "False" <SPAN style="color:#00007F">Then</SPAN><br>        strFolder = Left(String:=strFolder, _<br>                         Length:=Len(strFolder) - _<br>                                 (Len(strFolder) - _<br>                                    InStrRev(strFolder, "\", -1, vbTextCompare)))<br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "something esle, like bailing the sub..."<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

...or if xl2000, you could use an API:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=246

...or if 2003 or 07, I think maybe FolderPicker dialog. (I'm at home and on 2000 - and a bad memory...)

Hope this helps,

Mark

 

[millzner]

GTO - Using Excel 2000. Just ran a quick test using the API and should be able to do exactly what I want. Thanks for the help.