Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent saving over Excel and Word Files
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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("Desk top") & "\" MsgBox DeskTopPath I don't have a good way to update 500 different excel files, though. 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Navigating to Excel files over network slow, but not Word files | Excel Discussion (Misc queries) | |||
HOW CAN I PREVENT EXCEL FILES FROM BEING DELETED OR PREVENT TRASH | Excel Discussion (Misc queries) | |||
How do I prevent saving an excel file if cells are blank? | Excel Worksheet Functions | |||
convert 400 word files to excel files fast? | Excel Programming | |||
prevent user from saving excel file to a folder | Excel Programming |