Need to save 303 page report by group as 144 separate files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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


:

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top