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.03.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 5 9293
Ok, I'm part way there:
Dim FileName As String
DoCmd.OpenReport "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DRNOD\" & 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?
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*******@gmail.comwrote in message
news:11**********************@n76g2000hsh.googlegr oups.com...
Ok, I'm part way there:
Dim FileName As String
DoCmd.OpenReport "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DRNOD\" & 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?
On Mar 22, 3:16 pm, "Larry Linson" <boun...@localhost.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...@gmail.comwrote in message
news:11**********************@n76g2000hsh.googlegr oups.com...
Ok, I'm part way there:
Dim FileName As String
DoCmd.OpenReport "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DRNOD\" & 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 DailyOrderNumber
Forms![Batch]![OrderCounter] = 1 + Forms![Batch]![OrderCounter]
DailyOrderNumber = Format(Forms![Batch]![OrderCounter], "00")
DoCmd.OpenReport "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DRNOD\" & Format(Date, "yyyy_mm_dd") & "\" &
Format(Date, "yyyy_mm_dd") & "." & DailyOrderNumber & " " & Reports!
[Report Export]![BillLastName] & ".rtf"
DoCmd.OutputTo acOutputReport, "Report Export", acFormatRTF, FileName,
False
DoCmd.Close acReport, "Report Export"
Thanks for all your help!
On Mar 22, 4:22 pm, dancol...@gmail.com wrote:
On Mar 22, 3:16 pm, "Larry Linson" <boun...@localhost.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...@gmail.comwrote in message
news:11**********************@n76g2000hsh.googlegr oups.com...
Ok, I'm part way there:
Dim FileName As String
DoCmd.OpenReport "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DRNOD\" & 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 DailyOrderNumber
Forms![Batch]![OrderCounter] = 1 + Forms![Batch]![OrderCounter]
DailyOrderNumber = Format(Forms![Batch]![OrderCounter], "00")
DoCmd.OpenReport "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DRNOD\" & Format(Date, "yyyy_mm_dd") & "\" &
Format(Date, "yyyy_mm_dd") & "." & DailyOrderNumber & " " & 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\DRNOD\" & Format(Date, "yyyy_mm_dd")
MkDir (DirectoryTest)
------------------------------
Egress:
Exit Sub
GenerateHighline_Error:
Select Case Err.Number
Case 75
Resume Next
Case Else
MsgBox Err.Description
Resume Egress
End Select
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*******@gmail.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...
On Mar 22, 4:22 pm, dancol...@gmail.com wrote:
>On Mar 22, 3:16 pm, "Larry Linson" <boun...@localhost.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...@gmail.comwrote in message
>news:11**********************@n76g2000hsh.googleg roups.com...
Ok, I'm part way there:
Dim FileName As String
DoCmd.OpenReport "Report Export", acViewPreview, , , acIcon
FileName = "f:\Research\DRNOD\" & 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 DailyOrderNumber
Forms![Batch]![OrderCounter] = 1 + Forms![Batch]![OrderCounter] DailyOrderNumber = Format(Forms![Batch]![OrderCounter], "00")
DoCmd.OpenReport "Report Export", acViewPreview, , , acIcon FileName = "f:\Research\DRNOD\" & Format(Date, "yyyy_mm_dd") & "\" & Format(Date, "yyyy_mm_dd") & "." & DailyOrderNumber & " " & 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\DRNOD\" & Format(Date, "yyyy_mm_dd")
MkDir (DirectoryTest)
------------------------------
Egress:
Exit Sub
GenerateHighline_Error:
Select Case Err.Number
Case 75
Resume Next
Case Else
MsgBox Err.Description
Resume Egress
End Select This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Randy Harris |
last post by:
I'm using OutputTo to send data into Excel. It has been working nicely.
Now, I need to add more worksheets using OutputTo. Whenever I use it, it
replaces the existing worksheet. Is there any way...
|
by: Carl |
last post by:
Please can anyone tell me how I can create a macro to save the results
of a query as an excel file? The query is called Student List and I
would like to save it to "My Documents".
We have had...
|
by: MLH |
last post by:
A97 has menu options that support exporting table
data to ms excel data file format. Is this easily implemented
from within code? Any examples? I looked in A97 HELP
for the TransferSpreadsheet...
|
by: lushh |
last post by:
hi guys. i am currently using ms access 2003. i want to export my reports to an excel file so i did a code like this:
Private Sub cmdActiveListByEmployeeID_Click()
On Error GoTo...
|
by: MLH |
last post by:
Searching at http://support.microsoft.com/search/?adv=0 for Access 97
and "templatefile" returns the following:
There are no documents that match your search for "templatefile"
Has anyone ever...
|
by: dbushcmohle |
last post by:
Hello,
I am having problems exporting a stored procedure's results to an Excel
file... I've done this many times successfully, but never had to introduce a
variable. Now that I've introduced...
|
by: pdxrichard |
last post by:
Windows XP - Server 2003 - Access 2007 VBA - Code behind a form
Here is my code.
Why can't I find the exported file if the directories exist and there are no permission problems? Isn't a save...
|
by: Wayne |
last post by:
I'm trying to automate the export of a query to a text file using
code. If I export the query manually I get the exact result that I
want i.e. If I select the query then choose File/Export from...
|
by: riaane |
last post by:
Please help: I have Conditional Formatting on a Report Field that fills the background Red if the criteria is met. This displays correctly in Report View, however, when I "OutputTo" this report to...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |