Need help with creating files from templates

[Zelgadas]

I'm a contractor working for a client company. My client has a template (template.dot, we'll call it) in Word format. They also have an Access database (data.mdb) that contains data that they want plugged into the template. Now, normally I would simply use Word's mail merge utility to facilitate this request, but in this particular case there's a problem with that method. My client wants a few hundred separate, unique documents (in electronic format) to be generated from the merging of template.dot and data.mdb. The problem with mail merge is that it dumps all of the documents that it creates into a sincle .doc file, after which someone would have to go through this massive .doc file, copying and pasting each individual document into its own separate .doc file and saving it. This would be extraordinarily time consuming, and very inefficient on the scale that we're talking about here. What I need is this: a VBA module that creates a new Word document, imports the format from template.doc, plugs in the data from data.mdb, and saves the resulting document with a unique file name (probably based on the data from one of the columns in data.mdb), and closes the file. I also need this to be in a loop, so that I can simply set it off and let it create a few hundred documents while I'm doing something else. Now, I've tried to create the program, but the book that I'm using as a resource seems to be assuming that I have a more recent version of VBA than I actually do (I have Access 97, and no, I cannot upgrade as this is a company computer), so I'm naturally running into lots of problems. If anyone could give me some help here, point me in the right direction, I would really appreciate it. Thanks very much!

 

[ritchieroo]

Assuming that your Word Document is pulling data from a query within Access you could set up a loop that does the following

apologies for pseudo-code:

begin-loop
  "Create source query to return a single row of data, different criteria each time"
  "Open Word Document that is linked to source query"
  "Merge document to file, and save specifying required name"
end-loop

You should look in the VBA help for Word in order to find out the exact methods needed.

 

[Zelgadas]

Yeah, I'd pretty much figured out what the pseudocode version of my program was already. It's just the translating it into actual code that I'm having trouble with. Thanks for trying, though.

 

[Tim K.]

Search this forum for Mailmerge keyword and also search the Microsoft Knowledge Based Articles for send data to word keyword. You should find what you are after then.

Or give me more detail about the mailmerge in word. What fields you're sending out? What's in the template file? What field you want to base the doc file name with?

You know if you tell what you want only in general idea, you'll get only the general idea how to do it as well.

 

[ritchieroo]

Here's the basis for recreating query...

  With CurrentDb
    On Error Resume Next
    .QueryDefs.Delete "qryTest"
    On Error GoTo 0
    .CreateQueryDef "qryTest", "select * from [Table1] where [idWhatever] = " & lID
  End With

You should be able to get the merge code from MSDN as suggested.