Exporting reports by group (1 Viewer)

e-negron

New member
Local time
Today, 12:32
Joined
Mar 8, 2011
Messages
9
Hi, I have a report in which I want to create individual reports by group in "pdf" format, save it in a folder with the name of the group and name the report in a standard way.

For example, let's say that the group is "Plan ID" and has the values: 101, 102, 204, 405 and in the report there is a variable called: service month (example: 201101). I want to be able to create a report for each Plan ID, save 101 in folder name 101 and so on and in the name it has the month and plan id like: "this_is_thereport_101_201101.pdf"
 

DCrake

Remembered
Local time
Today, 20:32
Joined
Jun 8, 2005
Messages
8,634
This is achievable, however you would need to be quite conversant and confident with VBA as it would involve recordsets, Do While Looping, Checking for the existance of file paths, creation of file paths, Output to PDF, etc. are you up to that?
 

vbaInet

AWF VIP
Local time
Today, 20:32
Joined
Jan 22, 2010
Messages
26,374
Is there any point creating separate folders when you know that the you can sort the files in one folder? When you sort in the Window/Folder Browser you get them in this order:
Code:
this_is_thereport_101_201101.pdf
this_is_thereport_102_201101.pdf
this_is_thereport_204_201101.pdf
this_is_thereport_405_201101.pdf
.
.
.
etc
 

waiwaiet

Registered User.
Local time
Today, 14:32
Joined
Mar 8, 2011
Messages
4
I am having the same question, can anyone provide some code? Thanks!
 

e-negron

New member
Local time
Today, 12:32
Joined
Mar 8, 2011
Messages
9
I don't program in VBA but at least I would like to receive as much detail as posible to do it in order for me to follow. I am a data analyst and do monthly reports for more than 20 groups so it is very exhausting already. Right now I create individual queries and individual reports and then have an Outputto Macro do do the process.
 

waiwaiet

Registered User.
Local time
Today, 14:32
Joined
Mar 8, 2011
Messages
4
The reason I want it to be automated is because I have 70 groups that needs its own report.....
 

vbaInet

AWF VIP
Local time
Today, 20:32
Joined
Jan 22, 2010
Messages
26,374
Still one more point to clarify -->
Is there any point creating separate folders when you know that the you can sort the files in one folder? When you sort in the Window/Folder Browser you get them in this order:
Code:
this_is_thereport_101_201101.pdf
this_is_thereport_102_201101.pdf
this_is_thereport_204_201101.pdf
this_is_thereport_405_201101.pdf
.
.
.
etc
 

waiwaiet

Registered User.
Local time
Today, 14:32
Joined
Mar 8, 2011
Messages
4
but still.......i don't know how to spilt the report and then export into files and auto save its name...can anyone provide some code?
 

vbaInet

AWF VIP
Local time
Today, 20:32
Joined
Jan 22, 2010
Messages
26,374
The following is the barebones of the process.

Declare a variable in a Module (as Public):
Code:
Public strRptFilter As String
In the Open event of your report put this:
Code:
If Len(strRptFilter) <> 0 Then
     Me.Filter = strRptFilter
     Me.FilterOn = True
End If
In the Close event of the report, remember to reset the variable:
Code:
strRptFilter = vbNullString
The OutputTo part:
Code:
Dim rst As DAO.Recordset

set rst = currentdb.openrecordset("SELECT DISTINCT [[COLOR=Red]GroupID[/COLOR]] FROM [[COLOR=Red]TableName[/COLOR]] WHERE[COLOR=Red] ...[/COLOR] ORDER BY [[COLOR=Red]GroupID[/COLOR]];", dbOpenSnapshot)

do while not rst.eof
    strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & rst![[COLOR=Red]GroupID[/COLOR]]

    DoCmd.OutputTo acOutputReport, "[COLOR=Red]ReportName[/COLOR]", acFormatPDF, "[COLOR=Red]Path to folder[/COLOR]" & "\" & rst![[COLOR=Red]GroupID[/COLOR]] & ".pdf"
    doevents
    rst.movenext
loop

rst.close
set rst = nothing
Amend the bits in red and change the path name to suit your needs.

For the SQL statement, simply copy the SQL from your report's record source and only SELECT the GroupID field. If the data type of the GroupID field is Text then you need to change this:
Code:
    strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & rst![[COLOR=Red]GroupID[/COLOR]]
to this
Code:
    strRptFilter = "[[COLOR=Red]GroupID[/COLOR]] = " & [COLOR=Blue]chr(34)[/COLOR] & rst![[COLOR=Red]GroupID[/COLOR]] & [COLOR=Blue]chr(34)[/COLOR]
 

e-negron

New member
Local time
Today, 12:32
Joined
Mar 8, 2011
Messages
9
In my case I do need the reports in separate folders since for each group I prepare several reports as a package.
 

e-negron

New member
Local time
Today, 12:32
Joined
Mar 8, 2011
Messages
9
I have used the standard Outputto macro utility which is is not VBA... so I am bit lost ;)
 

vbaInet

AWF VIP
Local time
Today, 20:32
Joined
Jan 22, 2010
Messages
26,374
To start with, have even done what was explained in post #10?
 

vbaInet

AWF VIP
Local time
Today, 20:32
Joined
Jan 22, 2010
Messages
26,374
On the far right of a user's post you will see the numbers next to what looks like a scale, just above the Join Date.
 

e-negron

New member
Local time
Today, 12:32
Joined
Mar 8, 2011
Messages
9
I did wrote the first 3 instructions, but I could not follow the rest...
It is not crucial that the program creates the folders to save the reports of each group. I can create them manually because they are standard. What I really would like to be done is that report be saved in the particular folder which has the same name as the group Id and the name of the reports contain the month that they belong. The data from which the report is based has the variable of the month. As an example, the name of one report could be "101_201103.xls" to be saved in the folder named "101".
 

vbaInet

AWF VIP
Local time
Today, 20:32
Joined
Jan 22, 2010
Messages
26,374
What was explained in post #10 is the first step to achieving what you want. You have to get that working before even thinking about trying to save them in different folders. In programming you don't jump the gun.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom