Formatting File Names for OutputTo

[dancole42]

I have a question regarding the DoCmd.OutputTo function.

I have an invoice report that shows all of the invoices for a
particular date. I want to create a button that will export each page
of the report (i.e. each separate invoice) as a separate RTF file, and
save each file with the date, a sequential number (starting with 01
for the first invoice on that date), and the value of the LastName
field for that particular invoice in the filename, so something like:

20070322_Smith. 01.rtf
20070322_Jones. 02.rtf
20070322_Cole.0 3.rtf

So how do I do that?

My biggest weakness in Access is how to properly format this sort of
thing, so please include examples in your response. I really
appreciate it!

Thanks!

-Dan

[dancole42]

Ok, I'm part way there:

Dim FileName As String

DoCmd.OpenRepor t "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DR NOD\" & Format(Date, "yyyy_mm_dd ") & "." &
"01" & " " & Reports![Report Export]![BillLastName] & ".rtf"
DoCmd.OutputTo acOutputReport, "Report Export", acFormatRTF, FileName,
False
DoCmd.Close acReport, "Report Export"
Of course, instead of "01" I want it to give me a sequential number.
And obviously this exports the entire report as one file. And it names
the file as whatever the first page's BillLastName value is.

Thoughts?

[Larry Linson]

AFAIK, there is no capability to access individual pages of a report, as
you wish.

You might use a query that limits the data to that which would appear on a
single page, and, in VBA code, modify the query for each page and execute a
report for that page only, until you have completed the list of pages you
want to save.

Alternatively, it might be as simple to control Microsoft Word though COM
automation, and create the "reports" in that manner.

Either of these will require competence with VBA code, and though not
"rocket science", neither is "trivially simple", either. Finally, neither,
IMNSHO, can be adequately covered in a newsgroup response.

Larry Linson
Microsoft Access MVP
<da*******@gmai l.comwrote in message
news:11******** **************@ n76g2000hsh.goo glegroups.com.. .

Ok, I'm part way there:

Dim FileName As String

DoCmd.OpenRepor t "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DR NOD\" & Format(Date, "yyyy_mm_dd ") & "." &
"01" & " " & Reports![Report Export]![BillLastName] & ".rtf"
DoCmd.OutputTo acOutputReport, "Report Export", acFormatRTF, FileName,
False
DoCmd.Close acReport, "Report Export"
Of course, instead of "01" I want it to give me a sequential number.
And obviously this exports the entire report as one file. And it names
the file as whatever the first page's BillLastName value is.

Thoughts?

[dancole42]

On Mar 22, 3:16 pm, "Larry Linson" <boun...@localh ost.notwrote:

AFAIK, there is no capability to access individual pages of a report, as
you wish.

You might use a query that limits the data to that which would appear on a
single page, and, in VBA code, modify the query for each page and execute a
report for that page only, until you have completed the list of pages you
want to save.

Alternatively, it might be as simple to control Microsoft Word though COM
automation, and create the "reports" in that manner.

Either of these will require competence with VBA code, and though not
"rocket science", neither is "trivially simple", either. Finally, neither,
IMNSHO, can be adequately covered in a newsgroup response.

Larry Linson
Microsoft Access MVP

<dancol...@gmai l.comwrote in message

news:11******** **************@ n76g2000hsh.goo glegroups.com.. .

Ok, I'm part way there:

Dim FileName As String

DoCmd.OpenRepor t "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DR NOD\" & Format(Date, "yyyy_mm_dd ") & "." &
"01" & " " & Reports![Report Export]![BillLastName] & ".rtf"
DoCmd.OutputTo acOutputReport, "Report Export", acFormatRTF, FileName,
False
DoCmd.Close acReport, "Report Export"

Of course, instead of "01" I want it to give me a sequential number.
And obviously this exports the entire report as one file. And it names
the file as whatever the first page's BillLastName value is.

Thoughts?

Fair enough.

Ok, how about this (ignore my previous questions)... I want my
OutputTo command to put the files in a new directory. It won't do it
if I just tell it to OutputTo a directory that doesn't exist. How to I
get Access to do THIS:
------------------------------
If DirectoryX isn't there, create DirectoryX
OutputTo DirectoryX

If Directory X is there, OutputTo DirectoryX
-----------------------------

My code (produced entirely by trial and error) as it stands looks like
this:

Dim FileName As String
Dim DailyOrderNumbe r

Forms![Batch]![OrderCounter] = 1 + Forms![Batch]![OrderCounter]
DailyOrderNumbe r = Format(Forms![Batch]![OrderCounter], "00")

DoCmd.OpenRepor t "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DR NOD\" & Format(Date, "yyyy_mm_dd ") & "\" &
Format(Date, "yyyy_mm_dd ") & "." & DailyOrderNumbe r & " " & Reports!
[Report Export]![BillLastName] & ".rtf"
DoCmd.OutputTo acOutputReport, "Report Export", acFormatRTF, FileName,
False
DoCmd.Close acReport, "Report Export"
Thanks for all your help!

[dancole42]

On Mar 22, 4:22 pm, dancol...@gmail .com wrote:

On Mar 22, 3:16 pm, "Larry Linson" <boun...@localh ost.notwrote:

AFAIK, there is no capability to access individual pages of a report, as
you wish.

You might use a query that limits the data to that which would appear on a
single page, and, in VBA code, modify the query for each page and execute a
report for that page only, until you have completed the list of pages you
want to save.

Alternatively, it might be as simple to control Microsoft Word though COM
automation, and create the "reports" in that manner.

Either of these will require competence with VBA code, and though not
"rocket science", neither is "trivially simple", either. Finally, neither,
IMNSHO, can be adequately covered in a newsgroup response.

Larry Linson
Microsoft Access MVP

<dancol...@gmai l.comwrote in message

news:11******** **************@ n76g2000hsh.goo glegroups.com.. .

Ok, I'm part way there:

Dim FileName As String

DoCmd.OpenRepor t "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DR NOD\" & Format(Date, "yyyy_mm_dd ") & "." &
"01" & " " & Reports![Report Export]![BillLastName] & ".rtf"
DoCmd.OutputTo acOutputReport, "Report Export", acFormatRTF, FileName,
False
DoCmd.Close acReport, "Report Export"

Of course, instead of "01" I want it to give me a sequential number.
And obviously this exports the entire report as one file. And it names
the file as whatever the first page's BillLastName value is.

Thoughts?

Fair enough.

Ok, how about this (ignore my previous questions)... I want my
OutputTo command to put the files in a new directory. It won't do it
if I just tell it to OutputTo a directory that doesn't exist. How to I
get Access to do THIS:

------------------------------
If DirectoryX isn't there, create DirectoryX
OutputTo DirectoryX

If Directory X is there, OutputTo DirectoryX
-----------------------------

My code (produced entirely by trial and error) as it stands looks like
this:

Dim FileName As String
Dim DailyOrderNumbe r

Forms![Batch]![OrderCounter] = 1 + Forms![Batch]![OrderCounter]
DailyOrderNumbe r = Format(Forms![Batch]![OrderCounter], "00")

DoCmd.OpenRepor t "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DR NOD\" & Format(Date, "yyyy_mm_dd ") & "\" &
Format(Date, "yyyy_mm_dd ") & "." & DailyOrderNumbe r & " " & Reports!
[Report Export]![BillLastName] & ".rtf"
DoCmd.OutputTo acOutputReport, "Report Export", acFormatRTF, FileName,
False
DoCmd.Close acReport, "Report Export"

Thanks for all your help!

I think I figured it out...

I just added the following code to the beginning and end:

On Error GoTo Generate_Error

DirectoryTest = "f:\Research\DR NOD\" & Format(Date, "yyyy_mm_dd ")
MkDir (DirectoryTest)

------------------------------

Egress:
Exit Sub

GenerateHighlin e_Error:
Select Case Err.Number
Case 75
Resume Next
Case Else
MsgBox Err.Description
Resume Egress
End Select

[Larry Linson]

Glad to see you got it working.

Now I have thought of one possible approach to dealing with "pages of a
report"... export the Report from Preview to HTML format, and that will
generate one HTML file per page. That's not exactly what you wanted, but it
does allow doing something with "pages" of the report, after the fact. And,
it my experience, the HTML maintains the formatting somewhat better than
..RTF.

I had a colleague who used HTML reports to make data available to a wide
audience of people in a large organization, all of whom had some capability
to view HTML, but whose IT department did not want to install the Snapshot
Reader nor Adobe Acrobat Reader, and relatively few of whom had Access
installed.

Larry Linson
Microsoft Access MVP

<da*******@gmai l.comwrote in message
news:11******** **************@ y80g2000hsf.goo glegroups.com.. .

On Mar 22, 4:22 pm, dancol...@gmail .com wrote:

>On Mar 22, 3:16 pm, "Larry Linson" <boun...@localh ost.notwrote:

AFAIK, there is no capability to access individual pages of a report,
as
you wish.

You might use a query that limits the data to that which would appear
on a
single page, and, in VBA code, modify the query for each page and
execute a
report for that page only, until you have completed the list of pages
you
want to save.

Alternatively, it might be as simple to control Microsoft Word though
COM
automation, and create the "reports" in that manner.

Either of these will require competence with VBA code, and though not
"rocket science", neither is "trivially simple", either. Finally,
neither,
IMNSHO, can be adequately covered in a newsgroup response.

Larry Linson
Microsoft Access MVP

<dancol...@gmai l.comwrote in message

>news:11******* *************** @n76g2000hsh.go oglegroups.com. ..

Ok, I'm part way there:

Dim FileName As String

DoCmd.OpenRepor t "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DR NOD\" & Format(Date, "yyyy_mm_dd ") & "." &
"01" & " " & Reports![Report Export]![BillLastName] & ".rtf"
DoCmd.OutputTo acOutputReport, "Report Export", acFormatRTF,
FileName,
False
DoCmd.Close acReport, "Report Export"

Of course, instead of "01" I want it to give me a sequential number.
And obviously this exports the entire report as one file. And it
names
the file as whatever the first page's BillLastName value is.

Thoughts?

Fair enough.

Ok, how about this (ignore my previous questions)... I want my
OutputTo command to put the files in a new directory. It won't do it
if I just tell it to OutputTo a directory that doesn't exist. How to I
get Access to do THIS:

------------------------------
If DirectoryX isn't there, create DirectoryX
OutputTo DirectoryX

If Directory X is there, OutputTo DirectoryX
-----------------------------

My code (produced entirely by trial and error) as it stands looks like
this:

Dim FileName As String
Dim DailyOrderNumbe r

Forms![Batch]![OrderCounter] = 1 + Forms![Batch]![OrderCounter]
DailyOrderNumb er = Format(Forms![Batch]![OrderCounter], "00")

DoCmd.OpenRepo rt "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DR NOD\" & Format(Date, "yyyy_mm_dd ") & "\" &
Format(Date, "yyyy_mm_dd ") & "." & DailyOrderNumbe r & " " & Reports!
[Report Export]![BillLastName] & ".rtf"
DoCmd.Output To acOutputReport, "Report Export", acFormatRTF, FileName,
False
DoCmd.Close acReport, "Report Export"

Thanks for all your help!

I think I figured it out...

I just added the following code to the beginning and end:

On Error GoTo Generate_Error

DirectoryTest = "f:\Research\DR NOD\" & Format(Date, "yyyy_mm_dd ")
MkDir (DirectoryTest)

------------------------------

Egress:
Exit Sub

GenerateHighlin e_Error:
Select Case Err.Number
Case 75
Resume Next
Case Else
MsgBox Err.Description
Resume Egress
End Select