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

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".


On 2 May 2006 14:40:17 -0700, (E-Mail Removed) wrote:

>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 [Microsoft Access MVP]

Please respond in the newgroup and not by email.

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

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

On 5 May 2006 15:30:14 -0700, (E-Mail Removed) wrote:

>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 [Microsoft Access MVP]

Please respond in the newgroup and not by email.

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

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.

On 6 May 2006 09:14:28 -0700, (E-Mail Removed) wrote:

>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 [Microsoft Access MVP]

Please respond in the newgroup and not by email.

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

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.


On 7 May 2006 08:45:50 -0700, (E-Mail Removed) wrote:

>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 [Microsoft Access MVP]

Please respond in the newgroup and not by email.

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...en-us%3B207615

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

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.



On 10 May 2006 15:40:17 -0700, (E-Mail Removed) wrote:

>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...en-us%3B207615
>
>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 [Microsoft Access MVP]

Please respond in the newgroup and not by email.