How do I output Access Report via Word Macro to Word/Excel instead of Printer?

[chris]

Hi everyone. Im trying to update a Word VB project which accesses an
Access database for various things (and the original programmer is no
longer available). project originally created in OfficeXP but now using
2003.

One section of the VB project prints out an Access report including a
number of fields matching certain criteria using the following code;

Set acc = CreateObject("Access.application")
acc.opencurrentdatabase dba$
For tx = 0 To (cbogfile.ListCount - 2) \ 30
acc.DoCmd.OpenReport xreport$, 0, xreport$, tstring$(tx)
Next
acc.Quit

Thats fine, but I want to be able to export the report to Word or Excel
or some other editable format instead of sending it straight to the
printer.
xreport$ = "TMReport" so tried the following lines to replace the
DoCmd.OpenReport line;

acc.DoCmd.OpenReport "TMReport", acPreview, "", tstring$(tx)
acc.DoCmd.OutputTo acOutputReport, "TMReport", acCmdOutputToExcel,
"f:\Test.rtf"

which prints (why - shouldnt this just preview? - have also tried
acViewPreview but no difference) and then stops with error 56
'application-defined or object-defined error' on the OutputTo line. How
do I fix this and get the report into Word, Excel or any other editable
format? I nievely thought this would be simple as Im a bit new to
programming - but after 2 weeks of looking without success thought I
would ask a group again for help.

Thanks in advance
Chris

 

[John Nurick]

Hi Chris

This line's unnecessary. You don't have to open a report in preview
before exporting it.

acc.DoCmd.OpenReport "TMReport", acPreview, "", tstring$(tx)

acc.DoCmd.OutputTo acOutputReport, "TMReport", acCmdOutputToExcel,
"f:\Test.rtf"

Here, acCmdOutputToExcel is wrong: that's the constant used for a
similar purpose with DoCmd.RunCommand. Use acFormatXLS instead. You also
want to use ".xls" for an Excel file, not ".rtf".

 

[chris]

Thanks for your help so far
Ok so now Ive got;

Set acc = CreateObject("Access.application")
acc.opencurrentdatabase dba$
For tx = 0 To (cbogfile.ListCount - 2) \ 30
acc.DoCmd.OutputTo acOutputReport, "TMReport", acFormatXLS,
"F:\Test.xls"
Next
acc.Quit

but this gives me 'Run time error 3021 - no current record'
what next?
Chris

 

[John Nurick]

There's no point asking if you don't say which line of code is raising
the error!

 

[chris]

Sorry.
The acc.DoCmd.OutputTo line raises the error, after it asks which
format I want the file to be in.
When I hover over acOutputReport or acFormatXLS in Debug mode, they
both say "Empty".
What should I do?
Chris

 

[John Nurick]

This probably means

(a) that you have not declared

Option Explicit

at the start of the module. Doing this requires all variables to be
declared before use, which catches a lot of typos; and

(b) that your VBA project does not have a reference to the Access object
library in which constants such as acOutputReport are defined. Without
that and without Option Explicit, acOutputReport and acFormatXLS are
interpreted as uninitialised (i.e. Empty) Variables of type Variant.

To fix it, do one of these things:

-add a reference to the Access object library
-declare these constants yourself, using the values they have in Access
-replace the constant names with the literal values.

 

[chris]

Hi John
Ok I found and added the Reference to Access under the Tools menu, and
that solved the error. Thanks for that - looks like it was something
fairly simple after all!

And by using the Where condition associated with acViewPreview on the
line before the acOutputReport line, I can limit the number of records
output (so that 60 of 5000+ are output to the XLS file). This is the
code I have now:

Set acc = CreateObject("Access.application")
acc.opencurrentdatabase dba$
temptext = ""
For tx = 0 To (cbogfile.ListCount - 2) \ 30
If tx = 0 Then
temptext = tstring$(tx)
Else
temptext = temptext + "OR" + tstring$(tx)
End If
Next
acc.DoCmd.OpenReport xreport$, acViewPreview, xreport$, temptext
acc.DoCmd.OutputTo acOutputReport, xreport$, acFormatXLS, "F:\" +
xreport$ + Format$(Now, "ddmmyy") + ".xls"
acc.quit

However, this doesnt keep the formatting of the Access Report (when
printed) - so phone numbers lose the leading 0, dates get converted
into the internal Excel 5-digit number, etc.
I've tried using acFormatRTF which preserves the formatting set within
the design view of the Access Report (as far as I can tell) - but
annoyingly wordwraps some of the fields which throws the page numbering
out.

Is there any way of controlling the format of the resulting Excel file
- such as using some form of preformatted xls template?

Chris

 

[John Nurick]

Hi Chris,

Exporting reports is often very troublesome. If the layout is what
matters most, you can
-use Snapshot format (which may require people to install the free
Snapshot viewer)
-print the report to PDF (using Acrobat Distiller or another PDF-maker
such as CutePDF).
-use the Report Utilities from www.lebans.com

Unfortunately the standard export routine doesn't allow one to use an
Excel template (life would be much easier if they did). For phone
numbers, try prepending an apostrophe, e.g. by using a calculated field
in the query that provides data to the report, e.g.

fPhone: "'" & [Phone]

The apostrophe should force Excel to treat the field as text and not a
number. For dates, I suppose you could try the same trick, e.g.

fTheDate: "'" & Format([TheDate], "dd mmm yyyy")

but I'm really not sure what other options might exist.

 

[chris]

Hi John
Ok Ive run into another problem now!
If the wherecondition (temptext) on the OpenReport line is more than
about 2000 characters or so I get 'error 7769 - filter would be too
long' which is a known error in the knowledgebase;

http://support.microsoft.com/default.aspx?scid=kb;en-us;207615

but I dont understand how to fix it based on the kb article (it talks
about using filtername instead of wherecondition but doesnt give an
example)
Can you help me amend my code?
Thanks
Chris

 

[John Nurick]

Hi Chris,

I've never needed to use FilterName myself, but as I read the Help it
appears that if your report's RecordSource is the table or query XXX,
you can create and save a query YYY like this:

SELECT * FROM XXX WHERE <long and complicated condition>;

and then use "YYY" as the FilterName in DoCmd.OpenReport. As I
understand it, this will be equivalent to *either* including

WHERE <long and complicated condition>

in XXX *or* passing

<long and complicated condition>

as OpenReport's WhereCondition.

 

[chris]

Hi John
So now Ive got:

Set acc = CreateObject("Access.application")
acc.opencurrentdatabase dba$
temptext = ""
For tx = 0 To (cbogfile.ListCount - 2) \ 30
If tx = 0 Then
temptext = tstring$(tx)
Else
temptext = temptext + "OR" + tstring$(tx)
End If
Next
Set qdf4 = db.CreateQueryDef("temp", "SELECT * FROM " + xreport$ +
" WHERE " + temptext)
acc.DoCmd.OpenReport xreport$, acViewPreview, xreport$, qdf4
acc.DoCmd.OutputTo acOutputReport, xreport$, acFormatRTF, "F:\" +
xreport$ + Format$(Now, "ddmmyy") + ".rtf", True
acc.Quit
db.QueryDefs.Delete "temp"

which creates the query in Access then stops on the OpenReport line
with error 2498 - expression entered is wrong data type for one of the
arguments.

Can you help me fix this?
Thanks
Chris

 

[John Nurick]

Hi Chris,

The querydef you're creating isn't available to use until you explicitly
add it (or append, I forget the exact process) to the database's
QueryDefs collection. But don't do it that way: instead, manually create
a query "qryTemp" and then have your code manipulate its SQL property as
required:

qryTemp.SQL = "SELECT * FROM " & xreport$ & " WHERE " & TempText

(Use & and not + for concatenating text unless you understand the
difference between the two and want to take advantage of it.)

Also, this is screwed up

acc.DoCmd.OpenReport xreport$, acViewPreview, xreport$, qdf4

You've got the arguments in the wrong order and used xreport$ twice. And
the Filter property expects the *name* of a query, not a QueryDef
object.

And are you sure that running OpenReport and then OutputTo does what you
need? I.e. will OutputTo use the Filter you passed to OpenReport?

 

[chris]

Hi John
I think I might need a bit more hand-holding here. Ive amended the code
to the following but Im not sure Ive done it right! I now get error
code 424 - object required.

Set acc = CreateObject("Access.application")
acc.opencurrentdatabase dba$
temptext = ""
For tx = 0 To (cbogfile.ListCount - 2) \ 30
If tx = 0 Then
temptext = tstring$(tx)
Else
temptext = temptext + "OR" + tstring$(tx)
End If
Next
qrytemp.SQL = "SELECT * FROM " & xreport$ & " WHERE " & temptext
acc.DoCmd.OpenReport xreport$, acViewPreview, qrytemp
acc.DoCmd.OutputTo acOutputReport, xreport$, acFormatRTF, "F:\" +
xreport$ + Format$(Now, "ddmmyy") + ".rtf", True
acc.Quit

What Im trying to do (and this might not be the correct 'language') is
to output a table (called "TMREPORT", which is xreport$), but filtered
(?) according to temptext (which is of the format "[Field1]='0001' OR
[Field2]='0002' OR ..."). The code above of May 7th worked but only if
temptext was less than about 2000 characters - and I now have a
situation which exceeds this.
I'd be grateful if you could guide me through the necessary steps to
get this working.
Thanks
Chris

 

[dbahooker]

yeah don't export; word and excel are a disease

keep your data in a database and report on it when you need to.
if you need to take a 'snapshot' of the data then spit out a 'report
snapshot'

pretty straight forward