View Full Version : Repeatedly creating a package/group of documents with similar information

11-28-2017, 07:43 AM

I've been experimenting with VBA for a while now and the most valuable thing I have learned hands-down is to thoroughly define the requirements of a project before it starts, and ask for advice & suggestions. Here are the fundamentals of the project:

A company has a group of documents (around 60 or so documents with .doc & .docx extension) which they use regularly to produce a document package. At the moment, the 60 base documents are stored in a folder (I'll call it the source folder) and a document package is created by copying the required documents from the source folder into a new target folder. After the target folder is created, the individual documents are populated manually then converted to PDF and sent for internal review.

The process works for them but it is slow. When a new document package is created, there is certain project data that is populated throughout all the required documents in that package. I also understand that there is plethora of ways that this repeated data could be populated into the document. All users have access to the source folder via a network location.

The idea is to make the process as fast as possible and automate everything from choosing the required documents, creating the new target folder, populating the target documents (as much as possible), and then creating PDF copies for review.

How would you approach a project like this?


11-29-2017, 08:09 AM
A user Form to select Docs with

Since there may be some Doc Groups that always go in every Doc Package and some groups tat always go together

I would have a Standard Module listing those groups and all other Docs that the Form could read from.This allows for easy modification of Packages. These lists can also be kept on a worksheet so anybody can modify the Packages

I would have a Standard Module that can write to all Docs. Another to save As PDFs

When the "Done Selecting " button on the Form is clicked, it would call a Main Sub in yet another Standard Module that would run all the "Working" subs.

So you have a Doc Selector, A Doc Writer, a PDF Writer, and a Main Sub. I would copy the Docs to a different folder with the Form.

This lets you code in each module as the Muse strikes. It also lets you use Stubs for Compiling as you write code that needs to call a sub to compile.

12-01-2017, 08:12 AM
SamT, thanks for the response.

Sounds like a good structured way to go about it. What do you mean by "I would have a Standard Module listing those groups and all other Docs that the Form could read from."?


12-01-2017, 09:38 AM
Module listing those groups and all other Docs that the Form could read from."
The Form's listboxes, or whatever controls you use, must be filled from somewhere,

Code to read the files in the folder, but they wolnt bo orgnised.
A list or lists, in a sheet, that anybody can edit
A list in a code module that only the code maintainers can edit

Using defined lists allows you to set up Groups so that, for instance, selecting a group or two is all the User would need

Say thagt all packages get a certain set of docs. THe User wouldn't have to select those. They would always be in every Package
Say that certain types of PAckages get some groups, but other packages get different groups of Docs, The Form can list groups of docs to rduce the workload of the USer in selecting. This also helps eliminate Errors and ommisions in Packages

If you have any standard packages. The Form can be set to allow merely selecting one standard Package and the code would fill it with all the Docs that type Package needs.

I can see a Form with a Select Package command, A Add Groups of Doces command and a Add Docs command

Each command would create on the Form a list of Packages, or Groups, or Unused Docs.

12-03-2017, 09:19 AM
I see what you mean now. I hadn't thought of grouping documents together but it's a good idea. There is actually a few core documents which get used repeatedly, so grouping them would save time - like you said. Any additional docs can simply be added to a predefined package of docs.

Thanks for the input SamT. I will put it to use.