Repetitive Work (2003)

Topic

New Reply

Hi all

My current project at work is for the replacement of transformers and rectifiers at 9 different sites. Each site requires between 9 and 12 method statements depending on the type of substation. We have just been awarded another 18 sites. This means that I still have about 200 documents to compile.

At the moment I do this by opening an existing method statement, doing a save as and changing all the site specific stuff. Each one takes between 2 or 3 hours depending on the content.

Can anyone tell me if I would be likely to save much time overall if I were to spend time setting up custom fields in a template document’s properties and entering these for each document. Or if these is a better way of doing this?

Thanks

Regards

Graeme

Reply | Quote

Replies

If it’s a matter of changing specific information in fixed locations in the documents, you could store that information in a table (in a Word document, Excel workbook or Access database), with a column for each piece of information, and a row for each site. Use this table as the data source for a mail merge in Word, i.e. place merge fields in your method statement documents.

Reply | Quote

Thanks Hans

Is it possible to do a mail merge to produce new, additional documents? When I have used the mail merge function in the past, a one page letter, for instance, merged with 20 addresses would produce a single 20 page document. I would need the merge to be able to produce seperate documents per task per site. Most of the document information is, as you say, in fixed locations in the method statements.

Can I populate a table with all the variables and merge just one row at a time with a template document in order that I create the 200 finished documents as separate files?

And most importantly, do you think that doing the job this way is likely to save time?

Regards

Graeme

Reply | Quote

In the final step in the mail merge wizard, select Merge to a new document. Word will let you specify which records (=rows) you want to merge. You can choose a single record.

Or you can merge all records to a single document, then use the macro described in post 663,727 (originally by Doug Robbins) to split the document into separate documents.

I do think mail merge will save time – you’ll have to spend some time to set it up, but after that, producing the documents becomes very easy. If you get new contracts, producing the necessary documents will be a matter of entering the data in the table, then executing the mail merge.

Reply | Quote

Latest update! I’ve created 9 templates in Word one for each method statement type and a data file using Excel for the site specific stuff. One of the fields holds the document date. In Excel the cell is formatted to show 26/01/09. After merging the date displays in Word as 01/26/2009. Anyone know how to keep the original formating?

Thanks

Regards

Graeme

Reply | Quote

One option is to add a format switch to the date mailmerge field in Word:
– Right-click the merge field.
– Select Toggle Field Codes from the popup menu.
– Insert the following before the closing brace } but after the merge field name.

@ "dd/MM/yy"

– Press F9 to hide the field code and to update the field.

Another method:
– In Word, select Tools | Options…
– Activate the General tab.
– Tick the “Confirm conversion at Open” check box.
– Click OK.
– Go back to step 3 of the mail merge wizard (task pane).
– Click “Select a different list”.
– Locate your workbook again.
– This time, you’ll be prompted for a method to connect to the workbook.
– Select “MS Excel worksheets via DDE (*.xls)”, then click OK.
– Continue as usual.
The mail merge should now preserve the formatting from Excel.

Reply | Quote

Woody’s 4 minute answer Lounge!

Thanks Hans, option 1 implemented and working.

Regards

Graeme

Reply | Quote

I don’t think fields would be much faster than using text placeholders (e.g., ==SITENAME==) along with Find and Replace. Another possibility is to use Word’s merge feature. You can store your data in a very wide table in a Word document, and then merge in your data file to create the document. If your data changes, you can merge again. However, if the information might changes a few times, then the DOCPROPERTY fields would make subsequent changes much easier that either of those approaches.

One danger with using fields linked to custom document properties is that a metadata cleaner might wipe out your properties unexpectedly down the road and zero out all the work. You can preempt this by unlinking the fields manually before you close the file to hard-code the information. Of course, this wipes out the advantage noted above…

Reply | Quote

Thanks for your input Jefferson. Sorry I didn’t spot it last night!

It would seem that the way forward is to use the Mail Merge feature. Once signed off by the H&S people the document is PDFed and does not change unless it is revved up due to a change in the works. In that case it becomes a new document anyway. I don’t know what a metadata cleaner is so I had best avoid using the document properties.

Thanks for the macro Hans. I’ll study it once the Mail Merge is up and running. We are tendering for more of this type of work so its great to get this sorted at the early stages.

Regards

Graeme

Reply | Quote