Batch Processing for Large Number of Documents

Question

I am a tech writer at a large corporation. We are an outsourced services provider and are in the middle of upgrading the platform we use to provide these services. As a result, we have to duplicate a large number of documents (>1,500) in both Word and Excel format and then designate one edition for Version 5 and one for Version 6. The V5 docs will be maintained for statutory purposes, the V6 editions will become the new 'working copies'.

We have a CRM doc management system which works OK. It has batch download, upload, and change attributes capabilities which are tricky to use, but workable.  I am using the Doc System to meta-tag and cross-reference the documents. But we also need to put a visual reminder on the docs as to which version they are for, so when the users download them they will see 'this is a document for Version 5' (or 6 as case may be). I have been doing this part manually but it is far too time consuming. For each country, there might be 20-30 documents, each one has to be opened, the text inserted, and the file saved, in addition to all the aformentioned Batch ops on the CRM.

What I am looking for is a way to batch process these docs so I can put a stamp on the cover of each one, like V5.0. Ideally, a watermark would be great, but a 'Rubber Stamp' type of notice on the top page of the word docs, or the first sheet in an Excel workbook, would be sufficient.  It is easy to segregate the docs into Word and Excel so I guess it doesn't have to be the same batch file routine for the different file-types. I have been doing a bit of research, there is an article about creating a Batch Find and Replace on the Word MVP site, but it pertains to Word Mac. I am using Office 2007 on Win XP SP2. I have some support, one of my co-workers is a programmer with a lot of MS experience, so he might be able to help, but wouldn't know where to start on this task.

Any tips or pointers greatly appreciated.

Answer 1

This is a VBA Find and Replace for PC:  http://gregmaxey.mvps.org/VBA_Find_And_Replace.htm

This might also be helpful:  http://gregmaxey.mvps.org/Process_Batch_Folder.htm

They were developed for Word so I don't know how they might be adapted for Excel.

Answer 2

Jonathan,

Glad I could help.

Answer 3

HI Greg

That VBA FInd and Replace is magic. Works brilliantly. The attached article on Wildcard searches is really helpful too.

And I think I can adapt the script from Andreas to look after the Excel files.

So - thanks both - problem solved!

Answer 4

That's great -  Even if I can only do the Word docs, it will account for 90% of the workflow, so that is a really big help. I think my programmer colleague will be able to assist on the basis of this example. A big Thank You!

Answer 5

As you said your co-worker is an experienced programmer, this VB-Scipt might be a good start.

Save the code below my name in a textfile and name the file test.vbs

Create 2 test files test.xls and test.doc in a directory, i.E: C:\temp

Open the command line prompt and execute "wscript test.vbs C:\temp\test.doc Version6.0" (without the "")

Andreas.

'Get the arguments from the command line

Set Args = WScript.Arguments

If Args.Count < 2 Then

  MsgBox "test.vbs FileName VersionMsg"

  WScript.Quit

Else

  Filename = Args(0)

  VersionMsg = Args(1)

End If

'Check if the file exists

Set fso = CreateObject("Scripting.FileSystemObject")

If Not fso.FileExists(Filename) Then

  MsgBox "File not found:" & vbCrLf & Filename

  WScript.Quit

End If

'The extension let us know which filetype we should open

Select Case LCase(fso.GetExtensionName(Filename))

  Case "xls"

    'Get or open Excel

    On Error Resume Next

    Set objExcel = GetObject(, "Excel.Application")

    CloseApp = False

    If objExcel Is Nothing Then

      Set objExcel = CreateObject("Excel.Application")

      CloseApp = True

    End If

    If objExcel Is Nothing Then

      MsgBox "Excel is not accessible"

      WScript.Quit

    End If

    'Try open the file

    Set objBook = objExcel.Workbooks.Open(Filename)

    If objBook Is Nothing Then

      MsgBox "Can not open " & Filename

      WScript.Quit

    End If

    'Remove an existing one if any

    Set objShape = objBook.ActiveSheet.Shapes("VersionMsg")

    If Not objShape Is Nothing Then objShape.Delete

    'Add a textbox to the left upper edge

    Set objShape = objBook.ActiveSheet.Shapes.AddTextbox(1, 1, 1, 100, 50)

    objShape.Name = "VersionMsg"

    'Place the text into

    objShape.TextFrame.Characters.Text = VersionMsg

    'Save and close the file

    objBook.Close True

    'Close the application if wasn't open

    If CloseApp Then objExcel.Quit

  Case "doc"

    On Error Resume Next

    Set objWord = GetObject(, "Word.Application")

    CloseApp = False

    If objWord Is Nothing Then

      Set objWord = CreateObject("Word.Application")

      CloseApp = True

    End If

    If objWord Is Nothing Then

      MsgBox "Word is not accessible"

      WScript.Quit

    End If

    Set objDoc = objWord.Documents.Open(Filename)

    If objDoc Is Nothing Then

      MsgBox "Can not open " & Filename

      WScript.Quit

    End If

    Set objShape = objDoc.Shapes("VersionMsg")

    If Not objShape Is Nothing Then objShape.Delete

    Set objShape = objDoc.Shapes.AddTextbox(1, 1, 1, 100, 50)

    objShape.Name = "VersionMsg"

    objShape.TextFrame.TextRange.Text = VersionMsg

    objDoc.Close True

    If CloseApp Then objWord.Quit

  Case Else

    MsgBox "Unknown extension"

End Select