Active form without the upper part of the form for Word mail merge

[Wim Gielis]

Hello all,

Did anyone do something similar in the past using Active forms ?
I want to create a mail merge in Word using records in an Excel sheet, the records themselves are the result of an Active form.
The Excel sheet has to be as clean as possible though: only a header record and the records, no other stuff.

Can we tweak the Active form in such a way to have e.g. the formatting range and the SUBNM's and other formulas on separate sheets in the same Excel workbook ?
My attempts failed until now, when I try to use separate sheets even changing the defined names and so on.

The idea is to generate invoices based on the records that the Active form brings up.

Please note: I do not have the best relationships with Active forms as I mentioned elsewhere on the forum. I rather not use the Print report wizard, nor write a lot of VBA-code to obtain the result.

Thanks,

Wim

[lotsaram]

Format range and data range are local names so they can't be moved somewhere else.

You can define an Excel table from the active form and then reference the table on another worksheet.

[Wim Gielis]

Hello Lotsaram,

Thank you for your reply.

Do you mean that I should create an Excel table for the active form (the header and the records that are currently in the active form),
and then I use = cell references / column references to that table ?

If so, I'd rather not want to add many rows with = functions, just to have "enough" space to cover all lines returned by the Active form in the table.
If I understand you correcly I don't need a table and I can just use = type of cell references. =Sheet1!B15 for example and then copied to the right/down.

Can we have a solution where there is no manual maintenance and no VBA ?
Meaning, records in the output is equal to records in the Active form without VBA code and so on. If I use = cell references I still have to update them if I have more or less records.

Wim

[lotsaram]

If you define a table with the source at the active form data range then the table definition will automatically expand or contract when the active form is refreshed. You could then set up a pivot table with the excel table as a data source. Depending on how you arrange the pivot table you could get the exact same layout as the original table more or less. Then you don't need any formulas or any vba just to refresh the pivot table.

There's probably ways to do it with table formulas bypassing a pivot altogether.

That's if you rule out vba. But the vba needed would in this case be minimal and very simple, so although I'm generally against vba in this case it might just be the easiest solution.

[Wim Gielis]

Indeed, with VBA I can do this with 2 simple statements:
1 to clear the usedrange of the mail merge sheet, and 1 to transfer the values from the active form to the mail merge sheet.
That will probably be the easiest solution. Thanks.

[gtonkin]

HI Wim,
Have you tried linking to the workbook and selecting the Sheet/ActiveForm reference directly when you are prompted to select the Table? Also untick first row of data contains column headers - this has worked for me in the past but obviously references all columns.

What I tried before too but could not get it working for mailmerge specifically was a dynamic named range e.g.:

Code: Select all

=OFFSET(TM1Source!TM1RPTDATARNG1,-1,1,ROWS(TM1Source!TM1RPTDATARNG1)+1,COLUMNS(TM1Source!TM1RPTFMTRNG))

This always returns exactly what I need but does not show up as a valid table in the merge table selection dialogue. Converting the dynamic to a static with a new name works, but requires VBA again e.g.

Code: Select all

sheet1.Names.Add "tblMergeDataStatic",application.range("tblmergedata").address

[edit:] The other thing to do if you do not mind one blank record at the end is to define a static named range from the headings to one row below the active form. This should dynamically resize too however you will have the blank record at the end.

Maybe not a solution, but possibly something to try. Good luck.

[lotsaram]

Why not have a named formula referring to the static named range that used offset to snip off the last empty row?

[Wim Gielis]

Thank you, I will investigate further and let you know what I will use in the solution.

[paulsimon]

Hi Wim

The other possibility is to approach this from the MS-Word side. When you do a mail merge you can select the records that you want so why not just deselect the first n records that correspond to your Active Form Title Elements and Format Range?

Regards

Paul Simon