Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Saving files

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    New Member
    Join Date
    Feb 2002
    Location
    Frederick, MD
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •