+ Reply to Thread
Results 1 to 7 of 7

How to prevent saving over Excel and Word Files

  1. #1

    How to prevent saving over Excel and Word Files

    Hello,

    I'm new to VBA, but it seems quite powerful sometimes. We have a
    system, where the user browses to a folder on the server, and opens his
    choice of excel file. In this excel file, there is a form, with some
    VBA in the background, where one can fill out the form and press a
    control button in the excel file, which will place the gathered
    information in the excel into a word template with the same name. This
    word template exists in the same folder as the excel files.


    The assumption of the system is that people will open the excel file,
    fill out the form, link the information to word, then save the FINAL
    word file locally or simply print it, and then close both the excel
    file and the word file without saving.

    However, every once in a while, a user messes up and saves either the
    filled out excel file or the filled out word file over the template!
    and a restoration of the backup has to be performed.

    Now, I am new to VBA, but it seems to me like this should be very
    easily preventable, by simply opening the excel file and the word file
    without the SAVE AS path pointing to their original locations, I mean
    I've seen that behaviour before, where you try to save the file, and
    it'll prompt you for a new path that probably starts on your desktop or
    something like that. Basically, not giving you the option to press SAVE
    and overwrite the original templates.

    Notice that the files on the server are readOnly, but the saving over
    still happens.

    the code below is how the Word file is instantiated from within the VBA
    code:

    'Contains the name and path of the associated contract template
    Const csFilename As String = "---absoluteServerPathtoFile---"

    'Retrieve a word application instance if possible, else create one
    On Error Resume Next
    Set wd = GetObject(, "word.application")

    If Err.Number <> 0 Then
    Err.Clear ' Clear Err object in case error occured.
    Set wd = CreateObject("word.application")
    End If


    'Open the template file
    wd.documents.Open csFilename




    Any input is appreciated, I'm sure lots of you out there have enough
    experience to tackle this one, and I'm really appreciative if you can
    share you knowledge with the rest of us,
    Thanx alot in advance,

    Sincerely,
    Mjd


  2. #2
    Dave Peterson
    Guest

    Re: How to prevent saving over Excel and Word Files

    Instead of opening the MSWord document, maybe you could use it for the basis of
    the new document:

    wd.Documents.Add Template:=csFilename, _
    NewTemplate:=False, DocumentType:=0

    If you look at MSWord's VBA help, you'll see the syntax for this.



    [email protected] wrote:
    >
    > Hello,
    >
    > I'm new to VBA, but it seems quite powerful sometimes. We have a
    > system, where the user browses to a folder on the server, and opens his
    > choice of excel file. In this excel file, there is a form, with some
    > VBA in the background, where one can fill out the form and press a
    > control button in the excel file, which will place the gathered
    > information in the excel into a word template with the same name. This
    > word template exists in the same folder as the excel files.
    >
    > The assumption of the system is that people will open the excel file,
    > fill out the form, link the information to word, then save the FINAL
    > word file locally or simply print it, and then close both the excel
    > file and the word file without saving.
    >
    > However, every once in a while, a user messes up and saves either the
    > filled out excel file or the filled out word file over the template!
    > and a restoration of the backup has to be performed.
    >
    > Now, I am new to VBA, but it seems to me like this should be very
    > easily preventable, by simply opening the excel file and the word file
    > without the SAVE AS path pointing to their original locations, I mean
    > I've seen that behaviour before, where you try to save the file, and
    > it'll prompt you for a new path that probably starts on your desktop or
    > something like that. Basically, not giving you the option to press SAVE
    > and overwrite the original templates.
    >
    > Notice that the files on the server are readOnly, but the saving over
    > still happens.
    >
    > the code below is how the Word file is instantiated from within the VBA
    > code:
    >
    > 'Contains the name and path of the associated contract template
    > Const csFilename As String = "---absoluteServerPathtoFile---"
    >
    > 'Retrieve a word application instance if possible, else create one
    > On Error Resume Next
    > Set wd = GetObject(, "word.application")
    >
    > If Err.Number <> 0 Then
    > Err.Clear ' Clear Err object in case error occured.
    > Set wd = CreateObject("word.application")
    > End If
    >
    > 'Open the template file
    > wd.documents.Open csFilename
    >
    > Any input is appreciated, I'm sure lots of you out there have enough
    > experience to tackle this one, and I'm really appreciative if you can
    > share you knowledge with the rest of us,
    > Thanx alot in advance,
    >
    > Sincerely,
    > Mjd


    --

    Dave Peterson

  3. #3

    Re: How to prevent saving over Excel and Word Files

    Thank you so much for taking the time to reply, and the solution you
    suggested seems to work nicely (the only little catch is I wish I could
    still retain the title of the document from the template, just so when
    people want to save it to their desktops, they see the original name).


    The challenge now is figuring out how to update the macros in 500 excel
    files that did this differently.






    Dave Peterson wrote:
    > Instead of opening the MSWord document, maybe you could use it for the basis of
    > the new document:
    >
    > wd.Documents.Add Template:=csFilename, _
    > NewTemplate:=False, DocumentType:=0
    >
    > If you look at MSWord's VBA help, you'll see the syntax for this.
    >



  4. #4
    Dave Peterson
    Guest

    Re: How to prevent saving over Excel and Word Files

    Maybe you could just pick out the name of the document based on csFileName.

    Or just build that variable...

    Dim csPathName as string
    Dim csFilename As String
    dim csExtName as string
    dim csName as string

    csPathName = "\\yourpath\
    csFilename = "Template"
    csExtName = ".dot"
    csname = cspathname & csfilename & csextname

    And then build the filename just based on csfilename. (You could keep it a long
    string and extract the bits you need, but this seems simpler to me.)

    And you can get the desktoppath:

    Dim DeskTopPath As String
    DeskTopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") &
    "\"
    MsgBox DeskTopPath

    I don't have a good way to update 500 different excel files, though.


    [email protected] wrote:
    >
    > Thank you so much for taking the time to reply, and the solution you
    > suggested seems to work nicely (the only little catch is I wish I could
    > still retain the title of the document from the template, just so when
    > people want to save it to their desktops, they see the original name).
    >
    > The challenge now is figuring out how to update the macros in 500 excel
    > files that did this differently.
    >
    > Dave Peterson wrote:
    > > Instead of opening the MSWord document, maybe you could use it for the basis of
    > > the new document:
    > >
    > > wd.Documents.Add Template:=csFilename, _
    > > NewTemplate:=False, DocumentType:=0
    > >
    > > If you look at MSWord's VBA help, you'll see the syntax for this.
    > >


    --

    Dave Peterson

  5. #5

    Re: How to prevent saving over Excel and Word Files

    Again, thanks for your reply.

    About that name issue, I think I didn't explain myself fully. When
    using those lines of code that you suggested to avoid opening an
    instance of the word file on the server, I get a new document (usually
    titled 'Document1') that is based on the original template file. I
    would just like to retain the name/title of the original template file,
    because the name carries certain information, and I would like people
    to be able to save that file locally with the original name.

    So as you said, I can just pick out the name of the document based on
    the original csFileName, the only thing is I can't find out a method
    that let's you change the name of the document from 'Document1' to
    csFileName. It's probably either impossible, or very easy to do, but
    from the help files, I got no answer.


    And secondly, about updating the macros in the excel files, what I want
    is a script that will open these excel files, and get hold of the code
    in the Macro as text, and replace certain lines with these new lines.
    To me, writing that script should be no problem, it's just that I know
    how to access the data in worksheets for example, but I don't know how
    to refer to the text in a macro, that's all. (essentially, if these
    files were java files, then a script will just open the files as text
    files, and perform the changes).

    Thanks again Dave, I really appreciate you sharing your expertise with
    me.
    Mjd


  6. #6
    Dave Peterson
    Guest

    Re: How to prevent saving over Excel and Word Files

    If you break down your template name this way:

    Dim csPathName as string
    Dim csFilename As String
    dim csExtName as string
    dim csName as string

    csPathName = "\\yourpath\
    csFilename = "YourTemplateNameHere"
    csExtName = ".dot"
    csname = cspathname & csfilename & csextname

    you can save the MSWord document (the only way to Name an unsaved document)
    using that csFilename in the name to save:

    Dim csPathName as string
    Dim csFilename As String
    dim csExtName as string
    dim csName as string

    csPathName = "\\yourpath\
    csFilename = "YourTemplateNameHere"
    csExtName = ".dot"
    csname = cspathname & csfilename & csextname

    And then build the filename just based on csfilename. (You could keep it a long
    string and extract the bits you need, but this seems simpler to me.)

    And you can get the desktoppath:

    Dim DeskTopPath As String
    DeskTopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"

    Something like:

    wdDOC.saveas filename:=desktoppath & csfilename & ".doc"


    =======
    Chip Pearson has some tips on how to write code that changes code at:
    http://www.cpearson.com/excel/vbe.htm

    But this sounds like it would be very difficult (impossible for me) to make sure
    you fixed all that other code.







    [email protected] wrote:
    >
    > Again, thanks for your reply.
    >
    > About that name issue, I think I didn't explain myself fully. When
    > using those lines of code that you suggested to avoid opening an
    > instance of the word file on the server, I get a new document (usually
    > titled 'Document1') that is based on the original template file. I
    > would just like to retain the name/title of the original template file,
    > because the name carries certain information, and I would like people
    > to be able to save that file locally with the original name.
    >
    > So as you said, I can just pick out the name of the document based on
    > the original csFileName, the only thing is I can't find out a method
    > that let's you change the name of the document from 'Document1' to
    > csFileName. It's probably either impossible, or very easy to do, but
    > from the help files, I got no answer.
    >
    > And secondly, about updating the macros in the excel files, what I want
    > is a script that will open these excel files, and get hold of the code
    > in the Macro as text, and replace certain lines with these new lines.
    > To me, writing that script should be no problem, it's just that I know
    > how to access the data in worksheets for example, but I don't know how
    > to refer to the text in a macro, that's all. (essentially, if these
    > files were java files, then a script will just open the files as text
    > files, and perform the changes).
    >
    > Thanks again Dave, I really appreciate you sharing your expertise with
    > me.
    > Mjd


    --

    Dave Peterson

  7. #7

    Re: How to prevent saving over Excel and Word Files


    Hey Dave,

    Thank you for the details, and thanks for the link. You've been a
    tremendous help.

    Mjd


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1