Merge multiple Word-documents into one using Access

[spikepl]

How can I best go about merging existing Word-documents into one, from within Access?

A list of Word-documents exists in the DB, and I need to create one Word-file.

This file contains the List of contents, based on which documents were selected by the user, some other db-generated data, and of course the documents themselves.

The question is about the generalt tactic (the programming mines/explosions I will get to when I have an idea as to how to procede : should I in Access create a new Word-doc object, add my db-generated data, and then the contents of each Word-doc on the list? Should I just create the Word-doc with db-generated data only, and somehow programmatically append the other relevant files? Or just dump a Report to a Word doc, and then append the rest?

Any suggestions? Just a few thoughts on the sensible approach would be nice, the coding part I think I can deal with.

 

[vbaInet]

I don't do Word VBA but since you've asked for ideas here's one:

* Create a new document
* Cycle through your docs
* For each doc: Select All > Copy > go to new doc > vbNewLine > Paste
* Save and Close

I would imagine the Select All/Highlight All, Copy and Paste routines are commands available in DoCmd or the Application object.

 

[ted.martin]

Not a bad suggestion. Having just completed a major merge to word from Access, I would recommend developing the Word object code by creating dummy macros in word and then tweak the code in Access to suit.

 

[darbid]

How many word docs? Or what is the expected number of pagesin the end?
Is there much formatting involved and do som docs differ in formatting?

 

[spikepl]

1. About 15-30 existing docs, each one or two pages, some containing pictures. All this to be collected into one document, where only the first part (two or three pages) is generated from the the db - some fields, and a table or two.
2. Formatting is a timeconsuming beast I try to stay away from. The idea is that all the docs are based on the same template, and the lot is collected into one section (so no separate sections) as is.

(A new "fun" requirement has emerged: the backend is to be at an ISP, and could therefore be MS SQL, this is under discussion. This entails either some upload/download of the Word-files, with the corresponding mainenace mess, or eg placing them in the db as Attachments. Access.ADP talking to MSSQL knows no such things as an " Attachment" - an A2007 invention. ) Hmmm...

 

[darbid]

1. About 15-30 existing docs, each one or two pages, some containing pictures. All this to be collected into one document, where only the first part (two or three pages) is generated from the the db - some fields, and a table or two.
2. Formatting is a timeconsuming beast I try to stay away from. The idea is that all the docs are based on the same template, and the lot is collected into one section (so no separate sections) as is.

I do not know off hand how to handle this with word. But to get your core word code - just record a macro in word of the actions you want to do. I would first try a ctrl A - ctrl C - ctrl V - and see what the macro looks like. I would also make a section break between each doc so that formatting does not get mixed up.

 

[DCrake]

Not sure on the correct syntax but in word vba you can use Select.EntireStory Which effectively is a combination of Ctrl+A plus Ctrl+C.

Again I recommend a pagebreak between each document to preserve formatting.

 

[spikepl]

Word used to have an "Append file" command - it's probably hidden on the ribbon somewhere, in the new version. In any case, recording a macro in Word seems like a good idea. (thanks Darbid & vbaInet) .

So, by implication, I would open a Word-object inside Access, and then fiddle around with adjusting the Word-vba-code. One way to go, for sure. Maybe the only way.

I'll have to wait though, until some other details get sorted out.

 

[ted.martin]

Yes thats it - "fiddle around in Word". The Word Object model in Access can be quite complicated so what I always do is break it all down into small tasks and separately record the macro in Word for each task. Then take this code and insert it into your Access module.

One word of caution, do not make the module Sub code too big as there is a limit on the amount of code that you can insert into a Sub. I recently fell foul of this and then had to do what I probably should have done anyway, is break the tasks into separate Subs and then call them one by one from a Master sub.

Make sure you declare the various Word objects as Private rather than just Dim them in a sub.

good luck (you may need it)

 

[spikepl]

#9 Thanks for the cautions.

 

[DCrake]

I have never heard of size limitations in subs before? how big was big?

 

[ted.martin]

Well when the Compile size warning came up it said 64k was the limit which to me is not that big. Unfortunately, I have not found a way of determining the size of the Sub. Looked on google and everywhere and even there it said that your can't really measure it.

This size limit is for a Sub, not an entire module!

All I did was remove about 25 lines of code into a separate Sub and the error cleared.

One method I did find for estimating the size of the Sub was to copy the code into a Wordpad file and see how big that was. Then remove some code and compare it again and the proportional difference would give the estimated size.

I wouldn't worry about it unless your code in a module gets very large and trust me, you will know when it does. Printing my code out it went onto 15 pages of A4 landscape. If you are well within this, you should be fine.

 

[DCrake]

Also remember to keep modules secular. Don't include sub or functions that are not related. Access opens the module to find a sub or function and at the same time commits all the subs and functions to memory so it does not have to reopen it again if needed. By keeping modules small increases performance. Better to have a lot of small ones than fewer big ones. This is maybe why I have never encountered it.