Saving files

[DaKen]

I have a spreadsheet I use as a form. After the user opens it, they create their work and save it as a seperate file. They then open the form again and do the same thing, saving it as another different name. I know that is what a template is for, but even with templates, if you use the save icon, it saves over the top of the existing template, therefore erasing the form. Is there any way to force the save process to a standard .xls file or some form of user box with .xls as the extension where the user has to change the name of the file!?

 

[Mudface]

Would making the original file read-only get round your problem?

 

[NateO]

Daken,

Perhaps the code below will help:


Sub Svr()

Name = Range("e1") & ".xls"
On Error GoTo errorhandler
ActiveWorkbook.SaveAs Filename:=Name, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
SetAttr Name, vbReadOnly 'this is the key

Exit Sub

errorhandler:

MsgBox ("Guess Again")

End Sub



The "SetAttr Name, vbReadOnly" sets the windows file setting to read-only, you can't overwrite the file from Excel. You have to go into your Windows Explorer, check out "properties" and manually adjust the settings.

Cheers, Nate

 

[DaKen]

Where would you put this macro and how would it run?

 

[NateO]

Put it in a normal module. Run it how you please, from the toolbar, another macro, from the Visual Basic Editor, an Event procedure. It should be ready to go under all scenarios. Did you have something in mind?

Cheers, Nate

 

[NateO]

The following will prompt the user to create the name at open. Place it in a normal module:

Private f As String

Private Sub auto_open()
Application.Run ("testit")
Name = f & ".xls"
On Error GoTo errorhandler
ActiveWorkbook.SaveAs Filename:=Name, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
SetAttr Name, vbReadOnly 'this is the key
Exit Sub
errorhandler:
MsgBox ("Guess Again")
End Sub

Private Sub testit()
On Error GoTo errorhandler
f = Application.InputBox("Enter a filename")
If f <> False Then
Application.Run ("auto_open")
Else: Application.Run ("testit")
End If
Exit Sub
errorhandler:
End Sub


HTH. Cheers, Nate

 

[DaKen]

What I'm trying to accomplish is to prevent a user from replacing an existing spreadsheet, be it a template or standard .xls file. Some way to "force" them to save it under a different name. The read-only suggestion seems to be ok, but they can still save over it if they want.

 

[NateO]

Not on my computer..... Are you sure? If you right-click on file in your Windows Explorer, make a file read-only, I don't think you can delete or save over it from Excel without "special" code to do so. This macro doesn't work? Works for me, I can't save the file with the same name in the same directory.

Sorry this wasn't as good for you. Cheers,

Nate
This message was edited by NateO on 2002-02-28 09:20

 

[abrownbear]

When you save a form as a template, you then click NEW on the file menu (not the toolbar button) and then select the template. This is then a new document and when you click save, it will ask you to name the document. You're opening the actual template file instead of a new document based on the template.

Oh, and password protect the actual template file.
This message was edited by abrownbear on 2002-02-28 09:20