VBA and mail merge for word - editing datasources automatically 1

[rcavill]

Hi,

I'm trying to create a number of sets of labels. Each set will have all combinations of a set of options eg.
[a-e][1-5][m-p][2-4] would give a list of labels a1m2, a1m3, a1m4, a1n2 etc...

I've not done any vba before, but thought this might be the way to go, since these will need to be produced in word. Anyway... I've written the code to read in the various options to be combined, and that all works. And I've set up mail merge documents (data source and template). But the data source is obviously empty, until the user has run the macro and filled in the form.

So I tried using
ActiveDocument.Mailmerge.DataSource.ActiveRecord("fieldname") =value

to set the field value for each field name - in a loop one record for each combination (incrementing activerecord each time). But it appears this property is read only. So how do I edit my Datasource from within the VBA code?

Thanks

 

[SkipVought]

Hi,

'...since these will need to be produced in word."

And the reason is...?

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[rcavill]

normally I'd be skeptical of a statement like that too. But this needs to be simple to use, and run on a range of systems across a wide geographical area. (Almost) everyone has word, and since the setup for the labels is already in word, it made sense to mail merge the table of labels into that. And then it was a question of how to generate that table automatically - and word vba seemed easier than trying to mesh everything with some other language which might or might not work on their systems.

 

[SkipVought]

Well, it could also be argued that "(Almost) everyone has" Excel, and setting up lables in Excel, takes a bit more work than it would in Word, a ONE TIME THING, but the execution of what you want to do, would be much simpler in Excel, IMHO.

But that might be, 1) because I do not fully understand your requirement and 2) I am predisposed to using Excel.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[fumei]

2) I am predisposed to using Excel.

Is that like being blinded by the light?

All kidding aside, Skip is right in that holding data is quite definitely more of an Excel thing, than a Word thing.

I would also agree that it is not quite clear what you are saying. What, exactly, are you trying to do regarding:

"So how do I edit my Datasource from within the VBA code?"

faq219-2884

Gerry
My paintings and sculpture

 

[rcavill]

Yes I agree, if I wanted to store this data I would use Excel. This query isn't about data storage (sorry if I wasn't clear enough). It's about generating a set of identifying labels in a particular format for printing. The people generating the labels will know that they have 'n' options for the first bit, 'm' options for the second bit etc.

So I ask them to fill in a form (written in word vba, as to how many things they want there to be for each option).

So say I was generating labels for coloured shapes of different sizes:

I want one label for every possible shape, colour and size
So I ask the user - how many colours? how many shapes? how many sizes?

I have a mailmerge data source with the field names <<colour>> <<shape>> and <<size>>, which starts off empty. So now I want to dynamically create the records, to match the options the user inputted. So that I can execute the mail merge.

 

[fumei]

So you are having the users create the datasource?

"Yes I agree, if I wanted to store this data I would use Excel. "

Excuse me, but are you not in fact wanting to store this data? Is that not exactly what you are trying to do?

faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

Let's be very specific.

What data values are the user selecting and storing that would be used in the MailMerge?

Attempting to go the Word route with you, what if you pre set the source and then FILTER the source according to the user's selections?

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[rcavill]

Thanks for all the help, but I seem to have solved it.

If you use ActiveDocument.MailMerge.EditDataSource, then that switches the active document to be the data source table (which is initially set up empty because I have no records in my datasource). I can then edit ActiveDocument.Tables(1) (the table of records really) to add in all the entries I need, with a series of for loops based on the user input. Using .Rows.Add and .Cell.Range.InsertAfter Test:="text to put in cell"

In order to do the mail merge you then need to switch the active document back to the main document with Documents("filename.doc").Activate

Then ActiveDocument.MailMerge.Execute will do the merge.

 

[SkipVought]

rcavill,

Thanks for sharing your solution. Good tip! ==> *

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}