Exporting a report to multiple files

  • Thread starter Thread starter Landrew via AccessMonster.com
  • Start date Start date
L

Landrew via AccessMonster.com

I have been able to export a large report to seperate files by use of a
macro. However since there are a large number of files created (over 1,000),
I need to find an automated way to use one of the fields in the report in the
name of the files. For instance if one of the fields is [Name], I would like
the file to be named John Smith.html. The format I used was html, but text
would be fine too.
I understand that some form of VBA is required to make this happen but I am
unable to understand/figure out the posts on the subject.
Can anyone help?
Thanks in advance.
 
I assume that you have a table, say tblNames for which you produce the reports.
Then (if you use DAO) you could write something similar to the following:

Private Sub printMultipleReports()
On Error GoTo sub_Err
Dim sql As String
Dim where As String
Dim fileName As String
DoCmd.Hourglass True

sql = "Select * From tblNames"
' add where clause if you need to

With CurrentDb.OpenRecordset(sql, dbOpenForwardOnly)
Do While Not .EOF
where = "MyIdFieldName = " & !MyIdFieldName
fileName = !Name & ".rtf"
Application.Echo False, "Exporting report to '" & fileName & "' ..."
DoCmd.OpenReport MyReportName, acViewPreview, , where
DoCmd.OutputTo acOutputReport, MyReportName, acFormatRTF, fileName
.MoveNext
Loop
.Close()
End With
sub_Exit:
Application.Echo True
DoCmd.Hourglass False
Exit Sub
sub_Err:
MsgBox Err.Description
Resume sub_Exit
End Sub

HTH
 
Thanks for the quick response. It looks great, but where do I put it?
Where do I enter this code, and how do I execute it?
 
I should add that I don't know what DAO is. And I'm trying to give names to
an established report. It is necessary to keep the formating on the report,
so the report should be exported, not the table.
Thanks.
 
1. You put this code in let's say a button click event that triggers printing
of all your reports.
2. DAO - is a dll that allows manipulating data in code. To ensure that you
can use it - open Visual Basic Editor and select Tools-Refernces menu.
Then look for "Microsoft DAO 3.6 Object library" (if you use A'2K or above)
and make sure that checkbox is selected.
3. You create a report that can output the whole table, and then apply
filter on it as I mentioned in the code.

One more thing - please do not erase the original post from your replies -
it makes it very hard to trace what has been said before.
 
Sorry about deleting the posts.
The report is already built. Its name is "Lic Reports 09-09-05". With a
macro I can spit it out into multiple html files, I just need the person's
name, or another unique identifier which already exists in the report, such
as "Entity Code", in the file name to tell who it belongs too.
I assume I must modify your code to do that. I'm not sure where to put the
name of the report, or the name of the field to get the results. When I
convert the macro to VBA code I get:

Function Macro1()
On Error GoTo Macro1_Err

DoCmd.OutputTo acReport, "Lic Report 09-09-05", "HTML(*.html)", "C:\My
Documents\Lic Report 09-09-05 & .html", False, "", 1200

Macro1_Exit:
Exit Function
Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function
Thanks for your help!
 
Back
Top