Hello World,

I am working on a workbook which takes user inputs and selects and manipulates appropriate data from a database to return a condensed list of results. Depending on the user's inputs, the number of results may change from 1 - 25 line items. So far, this part has been successful. Each line item can be one of two basic types, with different sub-types and attributes. i.e. each line item could be an apple or a pear, but an apple could be an ambrosia, a granny smith, or a gala; and each pear could be a bosc, an anjou etc.

What I am now trying to accomplish, is to take those line items and generate a report that can be saved as a .pdf. Each line item would get its own page, based on a template for either basic type; to the example above, apples would have one template, pears would have another. The template then fills in the type as well as the sub-types & attributes. So it needs to have fields for these. Each template is equal to or less than one page in length on a document.

The report also has a title page, index etc. which are fairly static, though they will have the odd field that also needs to be grabbed from the user entered data in excel.

My goal is, from the open excel workbook to have a macro, which either within excel or word:
  • Opens / Creates Template File
  • Creates One Template Sheet Per Line Item Based on Style (i.e. Apple or Pear)
  • Grabs appropriate data from workbook and populates template for each line item
  • (Optional) Saves as .pdf automatically to desktop

I have looked at mail merge as an option but it appears, at face value to require much user manipulation, and this is meant to be automated from the excel sheet. I've been scouring the internet in hopes of finding a solution or at least a starting point, but haven't had much success. Most seems to be going from Word to Excel, not the other way around. Any insights you could provide would be most appreciated!

Cheers!