OutputTo Excel Spreadsheet

[JV]

If I have a report made up of subreports, is it possible when using the
OutputTo procedure in macros to have have each subreport go on a separate
worksheet in the workbook?

Also, is there a way to keep the exact format of the report? For example,
the report has 1 column of information but the exported report in excel has 2
columns one for the data and the then on the next column it has the total. Is
there a way to make it stay in one column?

 

[AnandaSim]

If I have a report made up of subreports, is it possible when using the
OutputTo procedure in macros to have have each subreport go on a separate
worksheet in the workbook?

Also, is there a way to keep the exact format of the report? For example,
the report has 1 column of information but the exported report in excel has 2
columns one for the data and the then on the next column it has the total. Is
there a way to make it stay in one column?

AFAIK, the Access Report Designer produces its own layout - it can
have lines, shades, subreports, graphics logos etc...

On the other hand, Excel is a grid based program - the two have very
little in common.

They have so little in common that in Access 2007 Microsoft gave up
any pretense of Output To from an Access Report.

On the other hand, with VBA and lots of careful pulling and poking,
one can grab any data from Access and poke it into Excel.

HTH
Ananda

 

[JV]

Thanks Ananda, may you suggest where I may find information about the what
you mentioned below?

 

[AnandaSim]

I don't have something I can point you to offhand, I just did a search
here's an example:

http://groups.google.com/group/comp...ad/5ff76f9dc4fa484a/831f5d4cc9276092?lnk=raot

Essentially you will need skills in

VBA
DAO or ADO
Excel programming

There are lots of Excel VBA programming specific books and reading
material that you can get....

HTH
Ananda

 

[jwarnold]

If I have a report made up of subreports, is it possible when using the
OutputTo procedure in macros to have have each subreport go on a separate
worksheet in the workbook?

Also, is there a way to keep the exact format of the report? For example,
the report has 1 column of information but the exported report in excel has 2
columns one for the data and the then on the next column it has the total. Is
there a way to make it stay in one column?

I did a quick Google, and was able to find these two functions that
may be of use to you:

* DoCmd.TransferSpreadsheet http://msdn2.microsoft.com/en-us/library/aa164087(office.10).aspx
* DoCmd.OutputTo http://www.blueclaw-db.com/docmd_outputto_example.htm

TransferSpreadsheet seems to be more what you are looking for.
You will have to do a little VBA action to make this work, but those
are a good place to start!
~jonathan