Word macro to retrieve information from Access Table

[HalPlz]

Hello everyone, I think this should be relatively simple, but I have no idea how to incorporate Access queries into a word macro with VBA.

I have a word form that I would like to do something like this:

Upon opening -
Fill each bookmark with its respective information from the Access database.

Each bookmark would have a string to go there. So [last name] [phone number] and [email address]. The word VBA would look up the Plan Code in the Access table, and insert the Last name of the person from that plan code, email address from the plan code, and phone number from the plan code.

I just need the code and I can fill in the locations of the information. We may have to go through this with a few steps. Any help is greatly appreciated.

[macropod]

You could use Word's mailmerge tools for this - no bookmarks or vba required.

[macropod]

Cross-posted at: http://www.excelforum.com/word-programming-vba-macros/1017139-word-macro-to-retrieve-information-from-access-table.html
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

[HalPlz]

Thanks, I've been reading up on mail merge tutorials.

A bit more advanced of a question - I don't even know if this is possible - but,

I need a word document that when opened, will fill in the fields through mailmerge, but it needs to depend on the location of the file.

So, if the file is in folder 0001, then it will look up 0001's data to put into the mail merge fields. And can mailmerge do this automatically upon opening, or will I need to use VBA to incorporate this?

Thanks again.

[macropod]

If by that you mean:
• the mailmerge main document always has the same data source, but the parameters vary according to which folder it's in, you would need to have a separate mailmerge main document in each folder that corresponds with that folder's requirements. No VBA required; or
• there is only one mailmerge main document but it uses different data sources and the parameters remain the same regardless of which data source is used, you would ideally have a separate mailmerge main document for each data source. Either that, or you would have to change the data source to the correct one each time the mailmerge main document is opened. Again, no VBA required.

[HalPlz]

Hi again - sorry for the delay. I'm finally getting time to come back to this.


If by that you mean:
• the mailmerge main document always has the same data source, but the parameters vary according to which folder it's in, you would need to have a separate mailmerge main document in each folder that corresponds with that folder's requirements. No VBA required; or
• there is only one mailmerge main document but it uses different data sources and the parameters remain the same regardless of which data source is used, you would ideally have a separate mailmerge main document for each data source. Either that, or you would have to change the data source to the correct one each time the mailmerge main document is opened. Again, no VBA required.


I think the first scenario is what I'm thinking of. Basically, right now my mail merge fills in the information for 400 different forms, unless I filter the records, which is what I want to do. I want it to filter the records depending on the location of the word document. So if it's in the folder C:\Something\0001 , then I want it to filter to find 0001.

12673



Like in the above pic, except, instead of 0001, it has the variable information for the last 4 digits of the location of the file. Is that still doable without VBA?

Thanks again. This would help me out a lot.

[macropod]

There is no way to automatically apply a filter based on a folder name. You'd have to configure the filtering for each folder, either manually or via VBA but, since it's a once-off exercise for each folder, it hardly seems worth the effort writing a macro to do it. It's also not apparent why you'd want a separate folder containing a mailmerge main document for each filter - for one thing, maintenance would be a nightmare. Surely a single mailmerge main document could be used, for which you apply the appropriate filter before executing, then save just the output to the desired folder (if required). In such a scenario, the filtering could be applied via a combined SKIPIF/FILLIN filed combination, so there's no need to go through the 'Filters' process each time. Such a field might be coded as:
{SKIPIF {MERGEFIELD Plan_Code} <> {FILLIN "Plan Code to merge" \d 0000 \o}}
A field coded like this would prompt the user for the Plan Code to merge each time the merge is executed. No VBA required.

Note: The field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues.

[HalPlz]

There is no way to automatically apply a filter based on a folder name. You'd have to configure the filtering for each folder, either manually or via VBA but, since it's a once-off exercise for each folder, it hardly seems worth the effort writing a macro to do it. It's also not apparent why you'd want a separate folder containing a mailmerge main document for each filter - for one thing, maintenance would be a nightmare. Surely a single mailmerge main document could be used, for which you apply the appropriate filter before executing, then save just the output to the desired folder (if required). In such a scenario, the filtering could be applied via a combined SKIPIF/FILLIN filed combination, so there's no need to go through the 'Filters' process each time. Such a field might be coded as:
{SKIPIF {MERGEFIELD Plan_Code} <> {FILLIN "Plan Code to merge" \d 0000 \o}}
A field coded like this would prompt the user for the Plan Code to merge each time the merge is executed. No VBA required.

Note: The field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues.

Awesome - I did not know there was such a thing.

I can't quite get it to work though. So if I have these merge fields:

{MERGEFIELD "Company_Name"}
{MERGEFIELD "FIRST"}
{MERGEFIELD "LAST"}

and if I want all of these fields to look up the mergefield data of the specified plan code (in this case 0001) when prompted, what would the coding look like?

Thanks again

[HalPlz]

EDIT: OK - scratch that - apparently the change just doesn't show up until the document is merged. It seems to be working now, thanks to Macropod's Microsoft magic.