Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    151
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hello again, Colleagues all !

    I am working on a project to create letters and other business documents form inside MS Access. The idea is to have a number of different templates, the simplest one having only name and address, salutation and signoff. This info is provided by a standard SQL query, dropped into a data source file and used for a mail-merge. These templates can be used by users to create other permanent templates, or they can simply write ad-hoc letters using an empty template. The intention is to get away from using MS Access reports for the purpose, with an undue reliance on programming expertise.

    At the next level, I have created a template with a bookmark which is used to locate a table in the document. The table currently in focus is a list of items with units, quantities, rates and totals which will be used for estimates, quotations, accounts and so forth and this is where I hope that I can call upon the expertise of some of you clever guys out there.

    I know how to create the table, set the column widths and enter the first row with column headings. The VBA code will then pull off the data it needs from the database, generate the rows one by one, append them to the table and populate the cells with the results of its computations. I have to do it this way, because the number of rows required is not known when the function starts up. One particular feature I need is to be able to apply a heavy border to the top edge and/or bottom edge of certain cells which are subtotals and (of course) the grand total. Also, some formatting to highlight bits of text, etc. would also be helpful.

    I previously used Excel to create the table, then embedded it in the document. However, I found that Word did not always retain the cell features (such as those I have described) when converted to a Word table.

    My stuff is all pretty crude and basic, so if anyone can provide me with a solution to the borders issue and any smart ways of carrying out the other work, I would much appreciate it.

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    I assume that you have variables pointing to the Table and to the Cell you are currently working on. I will refer to these as tblJim and clJim.

    Each of these objects has a Borders collection that you can manage with commands like

    tblJim.Borders(wdBorderBottom).LineStyle = wdLineStyleSingle
    tblJim.Borders(wdBorderBottom).LineWidth = wdLineWidth300pt

    clJim.Borders(wdBorderLeft).LineStyle = wdLineStyleDouble
    clJim.Borders(wdBorderLeft).LineWidth = wdLineWidth025pt

    Is this enough to get you started?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    151
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Spot on, Stuart, thanks a million.
    I had done something similar in Excel, but there was an odd thing happening. The borders button on the menu would only allow a border change at the bottom of the cell and similarly, the VBA would only do the same thing. This meant that after I had identified a row as being a subtotal, I had to revisit the previous row to put an underline in. Therefore, I thought I should consult the experts to see if there were any caveats in this regard. Apparently not.

    Anyway, I will proceed and see how I go. Obviously, with this being correspondence and other external stuff, the client will be looking for his documents to be presented as smartly as possible (especially after I had sold him on the idea ! ).

    Jim (b. Newcastle-on-Tyne, incidentally)

  4. #4
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    151
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Moving on, I can now use mail-merge to generate single letters which have a table of data inserted in them. The query which populates the data source refers to a field in a form to provide its filter. The next stage is to run a batch process to print multiple letters with the same content as the single ones. The query which populates this data source refers to a start date and an end date in a different form to provide its filter. This creates a problem in that I need to access the VBA function which creates and inserts the table for each letter in the batch. The question that I have therefore is: can I somehow insert a function in the document which will (using an argument specific to the individual letter) generate a table or a multi-line body of text in each letter. I know you can put a function in a cell in Excel, but I am unsure whether this works in Word and even more specifically, I am not sure that it would be called for each letter inside the mail-merge process. As a last resort, I can, of course, apply an external loop and print out each letter separately, using the single-letter process I have already established, but this is very inefficient as it sets up a separate mail-merge for each letter.
    Can any of you magicians out there wave your magic wands and give me an elegant solution (or transform mine!)

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Word's feature most similar to an Excel function is a field. However, that is not relevant to running a merge or VBA code.

    You can merge a single "main document" to a new document containing hundreds of letters. Word will keep generating new letters in individual sections in the new document until your data source is exhausted. Does that seem like a good approach for your needs?

  6. #6
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    151
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks for the info, jscher2000. I would imagine that is how a batch mail-merge works anyway, where each record in the datasource sparks off another letter on the next page. However, my problem is really the converse of this. In effect, the records in the datasource are all master records, whereas I am trying to add multiple subrecords, usually in the form of a table, though it could be simply data embedded in text generated in VBA (e.g. one paragraph per subrecord). I could even have two parallel sets of subrecords (or a hierarchy of subrecords) conceptually in the same document. I assume this would not work directly in mail-merge, because this relies on the Newline character to signal a move to the next letter, whereas each subrecord would also have a Newline, but with the master record data repeated many times, probably sparking a duplicate letter for the client, with each letter containing the data from one subrecord.
    The way I do it now is to create an empty file with possibly default text, and with a bookmark at the place where the table or paragraph is required. The main function takes the template from the library, copies it to a file in the work area, closes it (step 1), then calls another function which opens the file, inserts the subrecord stuff and closes it again (step 2). The main function then reopens the completed file and directs it to the mail-merge controller (step 3). Currently, for batch processing, this is done in a loop with each letter being generated separately.
    This sounds like a rather messy process, but you must remember that this is designed as a generalised system, where the users can create templates for new documents, place the document specifications in an Access table and select records for the batch from parameters in a form, all without programmer support. My main interest is in getting step 2 carried out within the mail-merge process itself, but I am beginning to think this is a big ask. Anyway, your remarks have given me food for thought and may feature in the system somewhere down the track, so thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •