Using VBA to alter Template settings for many files

[redtwister]

I have a question. Do you know if it would be possible to automate the process of editing the document template locations for batches of Word documents based on their current values?

Just an example:
-----------------------
A program or batch file is run in S:\data which contains subdirectories full of word documents

word1.doc has a document template attached at G:\templates\template1.dot and so is skipped.
word2.doc has a document template attached at \\CCNPP-IT\Sup\template1.dot and so has this value changed to \\Nas-ccnpp-02\CCNPPshare\IT\template1.dot or some such new value.


The program or batch file goes through all the subdirectories and changes the document template location for every word document that matches certain set criteria to equal the new locations of those templates.

 

[PHV]

And what have so far ?
Take a look at FileSearch and AttachedTemplate.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[redtwister]

I didn't really ask, but is VBA the right tool for this? Should I be using VBScript instead? i am a real newb with VBA and have had this problem thrown into my lap, and I figured that VBA was the right tool to go out and check thousands of Word files for the proper Template path and then change it.

 

[fumei]

Yes VBA can handle this no problem. AttachedTemplate is a property of ActiveDocument, so remember that you will to open each file to set the template property.

Please do a search for how to go through a folder of Word files, and open each one. You will use Dir.

Also note that AttachTemplate returns, by default, only the .Name of the template, not the path. So you need to work with the path. Run this for each file.

Assumption: the name of the template is the same (template1.dot - or whatever). If the name is going to be different, then you would have to change this for the .Name property, and the .Path.

Select Case ActiveDocument.AttachTemplate.Path
  Case \\CCNPP-IT\Sup\  ' if path of template = this
        ' then change the attached template
        ' note you must use a fully qualified pointer including filename
        ActiveDocument.AttachedTemplate = _
             "\\Nas-ccnpp-02\CCNPPshare\IT\template1.dot"
        ActiveDocument.Save (wdaveChanges)
   Case Else
End Select

Gerry

 

[peed1]

hi All,

I am doing VBA programming in Microsoft word. I am using ASP to call the document thru hyperlink.
I want to access the hyperlink values in the VBA. How am able to do this?

Thanking u in anticipation
Regards to all

Sandeep

 

[redtwister]

Thanks Fumei.

More questions:

1. When you say 'open the documents', you mean that my script will have to go and open each document as it does its work? What if the document takes several minutes to open because of the improper template path? This was my concern from the beginning that the delay would cause problems. I do not want it to try and open 300 Word files that are all searching the network simultaneously. That might cause some problems.

2. What do you mean by "Run this for each file"? If this is a batch process, shouldn't it run for each folder? I am a VBA n00b, so please pardon my ignorance.

 

[fumei]

1. a) Yes. I wrote "open", and that is precisely what I meant. You have to open the file to get at the AttachedTemplate property.

1 b) "takes several minutes to open because of the improper template path" ....huh? You have a folder of Word files, you open each one and see what template is attached. There can NOT be a improper template path. The path location is stored as the AttachedTemplate property.

The VALUE may be incorrect, but the property is available immediately. The system is not doing any validation - it is not looking around for a "proper" anything. YOU are doing the validation. The code looks at the value, checks if it is "proper" - if it is, closes the file (nothing more to do); if it is not, sets the AttachedTemplate property to the "proper" value, closes the file. Done. Goes on to open the next file.

1 c) First of all, it is NOT going to look for 300 files simultaneously. It is sequential. Second, it is not going to be looking around the network at all. Why would it? It has no reason, or instructions, to do so. See a) above. You have a folder of Word files. You open each one at a time. You check a value of a property. If the value is the one you want, you close the file. If it is not, you change the value, and close the file. Go to the next one. It is doing nothing at all with the network - other than, I assume, the folder is a network folder.

2. "Run this for each file"? - again, see 1 a) above. You have to open each file. The code above (the Select Case) checks each file for the AttachedTemplate property. It is, sort of a batch process. Think though, if this was say a .bat file, it would still open each file individually. That is what bacthing does. It does not open them all simultaneously.

Here is the full code

Sub CheckAttachedTemplate()

Dim aDoc
Dim ThisDoc As Document
Dim i As Integer

  ' change the path to the folder you are checking
aDoc = Dir("c:\TempRun\*.DOC")

On Error Resume Next

Do While aDoc <> ""
' open the file and make it document object

   Application.Documents.Open FileName:=aDoc
   Set ThisDoc = ActiveDocument


Select Case ThisDoc.AttachTemplate.Path
  Case \\CCNPP-IT\Sup\  ' if path of template = this
        ' then change the attached template
        ' note you must use a fully qualified pointer including filename
        ThisDoc.AttachedTemplate = _
             "\\Nas-ccnpp-02\CCNPPshare\IT\template1.dot"
        ThisDoc.Save (wdaveChanges)
   Case Else
End Select
' close current doc, destroy doc object
     ThisDoc.Close
     Set ThisDoc = Nothing
     aDoc = Dir()
Loop ' go to next file
End Sub

Gerry

 

[redtwister]

Thanks.

I understand what you meant now. It was a little terminological difficulty. I am NOT a programmer, so when you say 'open a file' I was thinking of something different.

And yes, the VALUE is what is incorrect, as it points to a server that no longer exists where the template used to reside.

On the sequential, my concern was that if the script were opening the file in the sense your average user would use, then when the file would hang, it might try to go on to the next file (or it would timeout, which would be annoying, but not critical.)

And when i said network, I should have been more specific. Since we are running this from a server, I was concerned that we could run a process which might bring down the server and impact clients running files over the network, not that this would impact 'the network' ie routers, switches, etc.

The script runs this process for each file, right? Sorry, again this is terminological. I am not used to viewing this from a programming perspective.

Thanks for the help, I will modify this as necessary and keep it around for future use.

Chris

 

[fumei]

OK, fair enough. Actually, to be on the safe side, add a:

On Error Resume Next...ooops, no I already did that.

It, in fact, DOES open the file just like a user (or anyone else). And yes, if the file hangs, it will go to the next one. Although I do not see why it would hang. It is just opening the file and checking one property. But if it does...yes, it should dump that file, and go on to the next.

Remember you are going to put this code in a Word document. Actually, you COULD put it into any app that takes VBA - you would just have to make an instance of Word at the beginning.

This is not a script. This is VBA. It MUST be run from a VBA compliant application. Word, Excel, Access, etc. Anything that use VBA. You could make this a VB executable, if you want. Then you could run it as an .exe - however, I do not really see the point to that, because, as stated, AttachedTemplate is a property of ActiveDocument. You must have an instance of Word running, and you must have the document open to read that property.

So you may as well put it into Word. VBA code in Word, doing Word stuff, runs faster than VB making an instance of Word, anyway.



Gerry

 

[PHV]

This is not a script
It could however easily be converted to a VBS script...
Just my 0.02$

 

[redtwister]

Well, the problem is that we have to go through several thousand files. The files in question do hang when you try to open them (this is simply a fact), and the reason they hang is that they are searching for a non-existent template over the network (the server was decommissioned and the template path value was not changed.) I know it is this because when I open a file (which takes approx. 3 minutes), and change the Template Path value manually, save it and re-open it, it works just fine.

So I guess my hope was to 1) run a script or batch process that would check the properties of each file without necessarily opening it (which I was not sure could be done), 2) search through all of the files in a specific directory and subdirectories, prefereably by using a UNC path, and 3) change the AttachedTemplate setting without triggering the attempt to open multiple files (which would end up being simultaneous since once it hung, it might move on to the next file, and over 3 minutes, that is a lot of files potentially.)

Maybe I do end up needing a VBscript. I don't know. And that's the problem. If I can figure out what tool is the correct one and get some clues about syntax, I can figure out the rest or work with one of my desktop engineering folks to figure it out.

Chris

 

[fumei]

OK, I see your point. Nope. Sorry. I have been hacking away at trying to get at the AttachedTemplate property without actually opening the file. Bottom line? Unless you are running a Microsoft Sharepoint Portal Server, I do not think it can be done. All my attempts to even get at the property without it being open results in a run-time error " bad filename". AttachedTemplate is a property of either Documents, or ActiveDocument.

ActiveDocument means the file is open.
Documents() means the document is in the Documents collection, which still means it was opened.

The file must be loaded into Word. I do not think VBScript would do it either. It would still have to create an instance of Word, and still have to open the file. At least I think so.

Gerry

 

[mariod1014]

There's an article on Microsoft's web site that addresses this problem. (See

http://support.microsoft.com/?kbid=830561)

I haven't tried any of the methods mentioned in their article, but I agree with Chris: the best solution would be one that didn't have to open the Word file (in the traditional sense) due to the exhorbitant amount of time it takes (5-10 minutes) to open it when the attached template can no longer be found.

 

[redtwister]

Thank you all for the help.