|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have a 303 page report that is the detail for 144 different groups. I need
to export this report into separate pdf files one for each of the 144 groups.
Would it be possible to create a macro within the report placed in the group
header and footer that will break the report into separate output files as
the group changes?
Or will I need to create a macro that prints the reports one at a time for
each group and then increments to the next group?
The field name of the group I am splitting the report on is [BU#]. Examples
of how to set up the macro either way would be very helpful
|
|
BF Consultants,
Since this process involves looping through data, using a macro is
possible but very awkward. Using a VBA procedure here will be a lot
smoother. You can do code like this...
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT BU FROM YourTable")
With rst
Do Until .EOF
DoCmd.OpenReport "YourReport", , , "[BU]=" & ![BU]
.MoveNext
Loop
End With
By the way, as an aside, it is not a good idea to use a # as part of the
name of a field or control, and I have exluded it from my example.
--
Steve Schapel, Microsoft Access MVP
BF Consultants wrote:
[Quoted Text] > I have a 303 page report that is the detail for 144 different groups. I need
> to export this report into separate pdf files one for each of the 144 groups.
>
>
> Would it be possible to create a macro within the report placed in the group
> header and footer that will break the report into separate output files as
> the group changes?
>
> Or will I need to create a macro that prints the reports one at a time for
> each group and then increments to the next group?
>
> The field name of the group I am splitting the report on is [BU#]. Examples
> of how to set up the macro either way would be very helpful
|
|
Thank you!
Not too familiar with VBA either. I do not know how to designate that each
report should print to file as a pdf with a unique name to a specific
location within the code you provided.
Additional details appreciated,
-
Atim
"Steve Schapel" wrote:
[Quoted Text] > BF Consultants,
>
> Since this process involves looping through data, using a macro is
> possible but very awkward. Using a VBA procedure here will be a lot
> smoother. You can do code like this...
>
> Dim rst As DAO.Recordset
> Set rst = CurrentDb.OpenRecordset("SELECT BU FROM YourTable")
> With rst
> Do Until .EOF
> DoCmd.OpenReport "YourReport", , , "[BU]=" & ![BU]
> .MoveNext
> Loop
> End With
>
> By the way, as an aside, it is not a good idea to use a # as part of the
> name of a field or control, and I have exluded it from my example.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> BF Consultants wrote:
> > I have a 303 page report that is the detail for 144 different groups. I need
> > to export this report into separate pdf files one for each of the 144 groups.
> >
> >
> > Would it be possible to create a macro within the report placed in the group
> > header and footer that will break the report into separate output files as
> > the group changes?
> >
> > Or will I need to create a macro that prints the reports one at a time for
> > each group and then increments to the next group?
> >
> > The field name of the group I am splitting the report on is [BU#]. Examples
> > of how to set up the macro either way would be very helpful
>
|
|
Atim,
Most likely you can set up your PDF writer software to save all files
produced with a default file name. If so, that would probably be the
easiest approach, and then use code to rename this file to what you want
each time. Here is some sample "air code" to point you in the right
direction...
Dim rst As DAO.Recordset
Dim OutputFile As String
Dim FileSavePath As String
OutputFile = "C:\SomeFolder\output.pdf"
FileSavePath = "C:\AnotherFolder\"
Set rst = CurrentDb.OpenRecordset("SELECT BU FROM YourTable")
With rst
Do Until .EOF
DoCmd.OpenReport "YourReport", , , "[BU]=" & ![BU]
Name OutputFile As FileSavePath & ![BU] & "_" &
Format(Date(),"mmddyy") & ".pdf"
.MoveNext
Loop
End With
--
Steve Schapel, Microsoft Access MVP
BF Consultants wrote:
[Quoted Text] > Thank you!
>
> Not too familiar with VBA either. I do not know how to designate that each
> report should print to file as a pdf with a unique name to a specific
> location within the code you provided.
>
> Additional details appreciated,
>
> -
> Atim
>
>
> "Steve Schapel" wrote:
>
>
>>BF Consultants,
>>
>>Since this process involves looping through data, using a macro is
>>possible but very awkward. Using a VBA procedure here will be a lot
>>smoother. You can do code like this...
>>
>> Dim rst As DAO.Recordset
>> Set rst = CurrentDb.OpenRecordset("SELECT BU FROM YourTable")
>> With rst
>> Do Until .EOF
>> DoCmd.OpenReport "YourReport", , , "[BU]=" & ![BU]
>> .MoveNext
>> Loop
>> End With
>>
>>By the way, as an aside, it is not a good idea to use a # as part of the
>>name of a field or control, and I have exluded it from my example.
>>
>>--
>>Steve Schapel, Microsoft Access MVP
>>
>>
>>BF Consultants wrote:
>>
>>>I have a 303 page report that is the detail for 144 different groups. I need
>>>to export this report into separate pdf files one for each of the 144 groups.
>>>
>>>
>>>Would it be possible to create a macro within the report placed in the group
>>>header and footer that will break the report into separate output files as
>>>the group changes?
>>>
>>>Or will I need to create a macro that prints the reports one at a time for
>>>each group and then increments to the next group?
>>>
>>>The field name of the group I am splitting the report on is [BU#]. Examples
>>>of how to set up the macro either way would be very helpful
>>
|
|
|