Reports in Excel

[ConstantaFotea]

Hi,
How can I save a Report in an Excel Template, or to
transfer (copy) the data in an Excel Template?
Is not about Tools, Ofice Links, Analysing it with Excel.
Thank you.
Maria

 

[SA]

Constanta:

There's really no easy way to output report data to an Excel template,
especially if that template has formatting applied where data doesn't start
at A1. Access outputs data from your report generally in rows and columns
starting at A1 in very simple rows and columns.

If you really need to fill a template with data there are two ways to go
about it.

1.) First make sure Microsot Query is installed. Then in your Excel
template create a new (eventually hidden) worksheet and use the Data ->
Import External Data command to import the data from a query you build that
runs against the Access db. This will import it again in rows and columns
starting at A1 on that worksheet page from the database. Then in the
worksheets that form your main template, simply reference the cells on the
worksheet page that contain the imported data for display purposes (e.g. =
AccData!A12. Obviously this is starting from the Excel side rather than
the Access side.

2.) Much more complex, but certainly doable from Access is to use automation
to open your template in Excel, open a recordset that contains the report's
underlying data and then using Visual Basic push the data over to the
template in what ever format or cell targets you need to fill.

HTH

 

[ConstantaFotea]

Hi Steve,

But how I should create a new (eventually hidden)
worksheet in an Excell template?

Constanta