I need help getting started.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick A - 14 Feb 2006 13:38 GMT

I need help getting started.

I have an access report that needs to be printed in booklet format. Publisher does this real nice.

Today I create the report in Access, publish it to Word, format it in Word, copy a paste it into Publisher, modify it in publisher and print it. All manual. I'd like to automate it with a push of a button.

With the advent of VBA in publisher in 2003 I'm thinking I could take the report directly from Access into Publisher.

Where do I look to understand how to do this? Where do I look to find out how to control Word from Access and Publisher from Access.

This might be the wrong group but I posted here because I'm working with VBA. If there is a better group for this question, please direct me there. I also posted this on the Access.reports newsgroup.

Thanks,

Signature

Rick

Reply to this Message

Ed Bennett - 14 Feb 2006 14:22 GMT

Rick A <allisonrja@hotmail.com> was very recently heard to utter:

> I have an access report that needs to be printed in booklet format.
> Publisher does this real nice.

...

> This might be the wrong group but I posted here because I'm working
> with VBA. If there is a better group for this question, please
> direct me there. I also posted this on the Access.reports newsgroup.

This is the right group for VBA, but before we launch into a massive
code-fest, is there any reason that what you want to do can't be done with
Mail Merge?

Signature

Ed Bennett - MVP Microsoft Publisher

Reply to this Message

Rick A - 14 Feb 2006 15:23 GMT

Ed,

>be done with Mail Merge?

I don't know. Perhaps it can.

I understand that Mail Merge can populate fields in a document from a
database. right?

My report is much more involved than a standard form letter where you want
to insert name, address, and other information to create multiple letters.
I am creating a catalog and I do not know how many entries I will have. I
would need a template, form letter if you will, that repeats but also needs
to have generic headers and footers. I do not know how to do that with Mail
Merge. If there is an example somewhere that would be fantastic.

I just figured I needed to write code to automate the manual process.

Thank you for your help.

Signature

Rick Allison

> Rick A <allisonrja@hotmail.com> was very recently heard to utter:
>> I have an access report that needs to be printed in booklet format.
[quoted text clipped - 7 lines]
> code-fest, is there any reason that what you want to do can't be done with
> Mail Merge?

Reply to this Message

Ed Bennett - 14 Feb 2006 17:32 GMT

Rick A <allisonrja@hotmail.com> was very recently heard to utter:

> My report is much more involved than a standard form letter where you
> want to insert name, address, and other information to create
> multiple letters. I am creating a catalog and I do not know how many
> entries I will have. I would need a template, form letter if you
> will, that repeats but also needs to have generic headers and
> footers. I do not know how to do that with Mail Merge.

You don't want mail merge, but Catalog Merge.

Start here: http://office.microsoft.com/en-us/assistance/HP010036071033.aspx

Signature

Ed Bennett - MVP Microsoft Publisher

Reply to this Message

Rick A - 14 Feb 2006 18:59 GMT

Ed,

>Catalog Merge

Thanks. I'll check into this.

Can this feature be invoked from Access? My Access application is used by
people across the US and Canada. I need to make this easy to use and
maintain. Is there anyplace to go that shows how to program Publisher's
Catalog Merge from Access?

Thanks,

Signature

Rick Allison

> Rick A <allisonrja@hotmail.com> was very recently heard to utter:
>> My report is much more involved than a standard form letter where you
[quoted text clipped - 8 lines]
> Start here:
> http://office.microsoft.com/en-us/assistance/HP010036071033.aspx

Reply to this Message

Ed Bennett - 14 Feb 2006 22:27 GMT

Rick A <allisonrja@hotmail.com> was very recently heard to utter:

> Can this feature be invoked from Access? My Access application is
> used by people across the US and Canada. I need to make this easy to
> use and maintain. Is there anyplace to go that shows how to program
> Publisher's Catalog Merge from Access?

Publisher can be used as a COM server, this allows you to call Publisher
from Access VBA. This is not that hard to work out how to do.

Articles on programming Publisher's Catalog Merge functions can be found
here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_pb2003_ta/h
tml/ODC_PB_CatalogMergeOverview.asp

Signature

Ed Bennett - MVP Microsoft Publisher

Reply to this Message

Rick A - 15 Feb 2006 13:24 GMT

Ed,

Great. Thanks.

One more question. In Access the report that creates the catalog uses
subreports. Any thoughts on how to manage that in a Catalog Merge?

I am well on my way.

Thank you,

Signature

Rick Allison

> Rick A <allisonrja@hotmail.com> was very recently heard to utter:
>> Can this feature be invoked from Access? My Access application is
[quoted text clipped - 8 lines]
> here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_pb2003_ta/h
tml/ODC_PB_CatalogMergeOverview.asp

Reply to this Message

Ed Bennett - 15 Feb 2006 13:41 GMT

Rick A <allisonrja@hotmail.com> was very recently heard to utter:

> One more question. In Access the report that creates the catalog uses
> subreports. Any thoughts on how to manage that in a Catalog Merge?

I have never used subreports, so forgive me if I misinterpred what I think
they mean.

If they mean what I think they do, then I would do separate merges, each
merging to the end of the previous output file, selecting a different
dataset each time. (Note that Publisher will often have problems merging
from queries, so workarounds are necessary. I normally suggest using
MakeTable queries and merging from the resultant tables)

Signature

Ed Bennett - MVP Microsoft Publisher

Reply to this Message

craig.matichuk@gmail.com - 17 Feb 2006 19:49 GMT

Rick,

I have a similar objective of using subreports linked to a field in the
parent report and exporting to Publisher. Are you having any luck
doing this with access data in Publisher? I can only seem to get it to
use one data source at a time with catalog merge.

Thanks,
Craig Matichuk

Reply to this Message

Rick A - 17 Feb 2006 21:16 GMT

Craig,

I just started. I set up a data source to a simple query at this time. I
do see your point that it might not allow two or more sources. If it does
allow multiple sources, how are they tie together?

I am also concerned that the "text box" created by catalog merge cannot
grow. While I want to repeat the input , what I have varies from two line
to six lines and I do not want to have all that extra white space in the
catalog. Any thoughts on that?

Maybe we can figure this out together. It would be nice to know up front
what publisher can or cannot handle. Do you know of any samples?

Signature

Rick Allison

> Rick,
>
[quoted text clipped - 5 lines]
> Thanks,
> Craig Matichuk

Reply to this Message

Ed Bennett - 17 Feb 2006 21:47 GMT

Rick A <allisonrja@hotmail.com> was very recently heard to utter:

> I just started. I set up a data source to a simple query at this
> time. I do see your point that it might not allow two or more
> sources.

You have to write a script to do the first merge from one source, then the
second merge from the second source, attaching to the end of the publication
made by the first merge.

> I am also concerned that the "text box" created by catalog merge
> cannot grow. While I want to repeat the input , what I have varies
> from two line to six lines and I do not want to have all that extra
> white space in the catalog. Any thoughts on that?

That requires manual tweaking. Is it likely that the extra space gained
from removing four lines every so often would allow you to fit one extra
line per page? Otherwise, you'll simply be displacing the whitespace to the
bottom of the page, where it would be more unsightly.

The only way to automatically do this would be to bypass Publisher's Catalog
Merge and construct your own merge scripts manually.

Signature

Ed Bennett - MVP Microsoft Publisher

Reply to this Message

Rick A - 18 Feb 2006 01:51 GMT

Ed,

Do you know of any samples showing how to construct a merge script? I learn
so much faster when I can start with an example to get started and grow from
there.

I'm thinking that catalog merge will be too limiting for me.

Signature

Rick Allison

> Rick A <allisonrja@hotmail.com> was very recently heard to utter:
>> I just started. I set up a data source to a simple query at this
[quoted text clipped - 17 lines]
> The only way to automatically do this would be to bypass Publisher's
> Catalog Merge and construct your own merge scripts manually.

Reply to this Message

Ed Bennett - 18 Feb 2006 23:59 GMT

Rick A <allisonrja@hotmail.com> was very recently heard to utter:

> Do you know of any samples showing how to construct a merge script? I
> learn so much faster when I can start with an example to get
> started and grow from there.

Nope, you'll have to piece bits together.

If you're automating straight from Access, you might not need the data
access scripts, just a publication construction script. It's pretty
autonomous coding once you've worked out exactly where you want all your
objects positioned relative to each other in each catalog box. You just
have to code in all those objects and positions, add an offset for each
subsequent item, and fill in the data. (Plus housekeeping tasks such as
keeping track of when you reach the end of a page, taking a new page when
you start a new subreport, etc etc.)

Nested loops are how I would do this.

Signature

Ed Bennett - MVP Microsoft Publisher

Reply to this Message

craig.matichuk@gmail.com - 20 Feb 2006 22:39 GMT

Rick,

No such luck here. I haven't had much time to work on it since I
posted, but I will give some of Ed's recommendations a try and let you
know what I get. I think this is going to be a bit more difficult than
it would seem. I have to check out my relationships and see if I can
manipulate them to make the subreports more transparent.

Thanks,
Craig

Reply to this Message