Renaming a file

12-09-2003, 02:40 AM
First a little background:

I have a macro in word which prompts the user for a file name. The file contains data which will be imported and placed in bookmarks within the document.

Because of the way the application works, all the data files are in a common area (this is the way it has to be). This does mean however that more than one user can select the same data file at the same time. The data file is read only so this, in itself is not a problem but it does cause other problems further down the macro.

What I want to be able to do is:

When the first user selects a data file, rename it temporarily until they have finished with it and then, on exit from Word, rename it back.

Below is a cut-down version of the code I have at the moment.

Dim vrtSelectedItem As String
Dim vrtSelectedItemUse As String
Dim oFSO As FileSystemObject

CurrentDir = Left(ActiveDocument.AttachedTemplate.Path, 3)

fd = InputBox("Please Enter the Unison Quote No.", "Enter Quote Number")

If fd = "" Then AllDone

vrtSelectedItem = CurrentDir & "data\" & fd & ".txt"

Set oFSO = New FileSystemObject

If Not oFSO.FileExists(vrtSelectedItem) Then
MsgBox ("The file " & fd & ".txt does not exist")
vrtSelectedItemUse = CurrentDir & "data\" & fd & ".use"
' I know I need something here but I'm not sure what
End If

I have two questions:

1. What code do I need above in the "I know I need something here..." section to rename the file vrtSelectedItem to vrtSelectedItemUse?

2. How do I trap the fact that the user is exiting Word so that I can perform the rename the other way round?

I hope this is clear enough and that someone out there can point me in the correct direction.

12-09-2003, 02:56 AM
1. Look up the the 'CopyFile Method' in the VB help
and choose another event to suit yout needs... :)

12-09-2003, 03:01 AM
Thanks Timbo, I will check those out.

12-09-2003, 03:16 AM
Having looked up the CopyFile method (and from there found the MoveFile method which I think is more what I need), I tried to include the following line into my macro:

FileSystemObject.MoveFile(vrtSelectedItem, vrtSelectedItemUse)

At which point I get "Compile error, expected ="

As far as I can see from the (limited) help text I have the format ok. Anyone tell me what I have done wrong please?

12-09-2003, 03:26 AM
Having made a slight modification to the above (removed the brackets) the compiler now likes it but, unfortunately, the MoveFile doesn't seem to move the file. ANy ideas anyone please?

12-09-2003, 03:28 AM
A "(" and a ")"... ;) :)

12-09-2003, 03:41 AM

Thank you, I had spotted that (makes note to read help text properly in the future, lol).

I have also manage to make it work by using

oFSO.MoveFile vrtSelectedItem, vrtSelectedItemUse

Off to address point 2 now. I'll let you know if I get it working.

Once again, many thanks for your assistance.

12-09-2003, 06:08 AM
I've coded the event handler and proced that it works by putting various events into it BUT it doesn't work for the quit event.

Any thoughts as to why this should be?

I really need to get this working.

12-09-2003, 07:06 AM
Does it work for other events? Do you initialise the class first?..

12-09-2003, 07:16 AM

Yes on both counts.

I have initialised the class and I have put other events in there just to prove that they are being trapped.

Everything is fine apart from trapping the quit event.

All I have done at the moment is put a MsgBox in the various events to prove I have trapped them. The message appears fine for everything I've tried except for the quit (which justs exits without any sign of a message).


12-09-2003, 07:20 AM
Humm, that is odd. I guess we'll have to see your code! :-\

12-09-2003, 07:52 AM
The following is a cut down version of my code:

Public ECM As New EventClassModule

Sub autoNew()

' Lot's of other code here
End Sub

Sub Register_Event_Handler()
Set ECM.WdApp = Word.Application
End Sub

In the class module (which is called EventClassModule) I have the following code.

Public WithEvents WdApp As Word.Application

Private Sub WdApp_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
MsgBox "Got to doc before close"
End Sub

Private Sub WdApp_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "Got to doc before save"
End Sub

Private Sub WdApp_Quit()
MsgBox "YES - You got to the application quit subroutine"
End Sub

When I run my macro I see the message for DocumentBeforeClose and for DocumentBeforeSave. I don't see the message for quit.

If you need any more info please let me know.

12-09-2003, 08:20 AM
I beg your pardon - click and drag both the module, and the class module into the 'Normal' VB project in the Project Explorer window, save and run.

Working from a memory as bad as mine - well... :-\ :)

12-10-2003, 01:18 AM

I've decided that I can get the functionality I need by putting my code into DocumentBeforeClose, this triggers on quit as well so that does the job for me :)

Thanks again for your assistance.

12-10-2003, 03:13 AM
Unless there's another document open, in which case the Document event will fire, but the Application will still be open... :-\

Didn't the code above work?

12-10-2003, 03:29 AM
In this case there will only be one document open, the application is designed to work that way.

However I have discovered another problem:

The document is automatically saved as part of the macro, if later the user opens the document and selects save as (they shouldn't but users, being users...) the save as defaults to type .dot NOT .doc.

I got round that by putting FileFormat:=wdFormatDocument on the save.

but, when that is specified the document close event doesn't fire! Also it seems to leave a recovery document around if I do this - any thoughts please?

12-10-2003, 03:41 AM

With regard to your comments about dragging the module and the class module into the normal vb project in the project explorer window:

The template I am using is a specific template in a common area which the users can access, they open this template directly from a desktop icon. Surely if I dragged the modules into normal that would require me to modify on each machine which would be using this (unless I am misunderstanding something).

That would cause 2 problems:

The behaviour I am trying to acheive here is specific to this application and must not be the default for ordinary word documents.

It would be a logistical nightmarre trying to modify the for each of the pc's in question.

12-10-2003, 03:48 AM
Actually you could write some code to add those code modules to the users .dot project every time they open your file, and remove it when they close it. But if they can only open 1 document anyway, there's not much point.

I got round that by putting FileFormat:=wdFormatDocument on the save.
but, when that is specified the document close event doesn't fire! Also it seems to leave a recovery document around if I do this - any thoughts please?
I suspect these to be symptoms of the same cause - what do you mean by "it seems to leave a recovery document around"

12-10-2003, 04:05 AM
If I specify the file format as stated above, the next time I open the template (or a document), Word opens the "Document Recovery" pane and tells me "Word has recovered the following files. Save the ones you wish to keep." and lists the quote I have just been working on as "22 [Original] last saved by user 10:50 10 December 2003".

As stated putting the file format in causes this problem and also stops the document close event triggering. The implication seems to be that the save fails for some reason with the file format specified. I am going to check that out and will let you know what I discover.

12-10-2003, 07:42 AM
I've done some more testing on this and have come up with an alternative to specifying the file format on the save.

I think perhaps I need to supply more background on what I'm doing to see if anyone can shed any light.

The company I work for has a package written in Progress 4gl. One function of this package is to produce quotations. Previously they have been printed on a dot matrix directly from the package - not very attractive in appearence.

The company who supply the package modified it for us to cause it to output some data files which we can import into Word. By various bits of "black magic" these data files are placed onto a mapped drive which our users can access. The company also supplied a Word template which would, when opened, automatically run a Word macro which would prompt for the style of output required (choice of 4 different styles) and would also prompt for the quote number required, access the data file for this quote and copy the data into bookmarks in the document to produce the quote via Word.

I have been modifying the supplied Word macro to make it more suitable to our company requirements.

I made comment in an earlier post that once the generated quote had been saved if someone opened the Word document and then selected Save As it defaulted to .dot. I found a workround for that above but have now discovered that the reason for that was the base documents for the four formats had been set up as .dot files (originally for some reason each had the macro present even though there was no need for that). I have changed them from .dot to .doc files and the save now works without me having to specify the file format.

However (and this is the crunch) the event trapping module detailed above will not trap the "quit" event and now also will not trap the "document before close" event although it does trap other events (thus proving it is active and working even if not in every situation).

Please, Please, Please, Please can someone give me a pointer as to why this is or an alternative suggestion as to how I can trap the fact that the user is closing the document / quitting word?

12-10-2003, 08:45 AM
Chris, am I correct in assuming that you are writing this in VBA rather than in VB6?

12-10-2003, 09:58 AM

Yes that's correct it is in vba within Word

12-10-2003, 11:19 AM
If you create a Word addin using the prescribed code above, I'm reasonably confident that it will continue to operate normally.
You could use one of the CustomDocumentProperties in the templates, to allow the addin to detect if the current Document is one it should be moderating...

12-11-2003, 01:33 AM

Could you expand a little on creating an addin please? It's not something I've ever done before and I'm not even sure where I would start.

12-11-2003, 02:29 AM
Rather than waffle on any more myself, I found this handy tutorial on it:

Let us know if you're still having trouble after...:)

12-11-2003, 02:40 AM
Many thanks Timbo,

I will read through that and see if I can make any progress.

12-11-2003, 07:16 AM

I have read the information and successfully created a global add-in which detects the event I want but:

As the addin is global it triggers no matter where Word is called from. What I now need to do is detect that I am in the event trapper from a quotation. In the tutorial it suggests that this can be done by using ActiveDocument.AttachedTemplate. The problem I now have is that my original macro contains the following statements:


Set newDoc = Documents.Open(strTemplate)

When it closes the first document the attached template is (as I would expect) When it closes the second document the attached template is As it is the closure of the second document which I am interested in I cannot use the ActiveDocument.AttachedTemplate to establish that I am coming from Quotes. Can you suggest any alternative way of doing this, please?

I really am ripping my hair out over this "simple" fix.

12-11-2003, 08:06 AM as I was saying before my ISP cut me off so rudely... :mad:

How about setting one of the CustomDocumentProperties of the Quote docs to a value which is unlikely to be used in any other file, and have the template proceedure check that value before executing the rest of your routine?...

12-11-2003, 08:19 AM
Nice thought Timbo,

Is there a list of the CustomDocumentProperties anywhere?
I know I saw a routine for listing them somewhere in the help text, I will try to find that again and see what I can discover but if you know of a list of them anywhere it would save a little time.

Your help is VERY much appreciated.

12-11-2003, 09:29 AM

Once again a step forward (and two steps back).

I have discovered that you can add CustomDocumentProperties (basically anything you wish) and have added the appropriate properties to my document.

What I now want to do when the event is trapped and control passes to my global addin is this:

Taking the details passed through as custom document properties rename a file to something else (doesn't this sound very much like where I came in).

To do that previously I used the following code:

Dim oFSO As FileSystemObject

Set oFSO = New FileSystemObject

oFSO.MoveFile vrtSelectedItem, vrtSelectedItemUse

Where vrtSelectedItem was the file name to rename from and vrtSelectedItemUse was the file name to rename to.

The problem I have now is that, in my global addin, I get a compile error if I try to Dim oFSO as FileSystemObject. The compiler thinks FileSystemObject is invalid. Aaaaaaarrrrrrgh!!

What do I need to do to make this work? :confused: :( :( :confused:

12-11-2003, 09:55 AM
Got a little further:

I suddenly had a thought that it might be a missing reference, added the appropriate reference and sure enough, it runs through without a compiler error. Unfortunately it doesn't do exactly what I want yet but I will have a closer look tomorrow - think it's time to give my brain a rest.

12-12-2003, 04:30 AM

Once again many, many thanks for your assistance. I have now got the macro working exactly as I want it to! :)

12-12-2003, 10:26 PM
Glad to hear it :)

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum