How to prevent saving over Excel and Word Files

[majd.alwajeeh]

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]

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.

 

[majd.alwajeeh]

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]

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.

 

[majd.alwajeeh]

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]

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.

 

[majd.alwajeeh]

Hey Dave,

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

Mjd