I need help getting started.

[Rick A]

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,

 

[Ed Bennett]

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?

 

[Rick A]

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.

 

[Ed Bennett]

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

 

[Rick A]

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,

 

[Ed Bennett]

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/d...b2003_ta/html/ODC_PB_CatalogMergeOverview.asp

 

[Rick A]

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,

 

[Ed Bennett]

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)

 

[craig.matichuk]

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

 

[Rick A]

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?

 

[Ed Bennett]

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.

 

[Rick A]

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.

 

[Ed Bennett]

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.

 

[craig.matichuk]

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