export query to excel and do calculations

[Guest]

dear friends,
i have a table name "dutyroster"
field employeename
" onduty
" offduty
" date
now i would like to transfer the query output of emloyeename , onduty ,
offduty , date, to excel file using docmd.outputto method and do calculation
at excelsheet
of totaldutyof employee.
but i need to calculate the totalduty and the other cell formats such as
color and font size etc , as because of the excel file not retaining the
formula and format
each and every time the command runs . how to come out of this problom please
kindly help me.

 

[Guest]

Rather than exporting to Excel, I prefer to write my query to give me the
data structure that I want, then I go to Excel and use the Data -> Import
External Data -> New Database Query option.

If you have it installed, selecting this option will popup a "Choose Data
Source" dialog box. Select MS Access Database and click OK.

This will popup another dialog box to select the data file. Choose the file
you want and select OK, then in the next dialog box, select the query you
want and click the ">" button to move the columns from that query into your
query. Then click "Next>"

If you want to filter the data, this is where you do it, then hit "Next>"
again.

If you want to sort the data, this is where you do it, then hit "Next>" again.

Then click Finish and tell Access where (what cell) you want to put the data.

Now you can setup your formatting, a totals column, or whatever formatting
you want. The next time you want to update this Worksheet, select one of the
cells inside the data range, right click, and select "Refresh Data"

I've got a number of Excel reports and graphs setup to take advantage of
this capability.

HTH
Dale

 

[Guest]

great thanks dear friend.
i have two doubts remained.
(1) an ordinary user working on a data base who needs the report at a click
only that to in excel format.
(2) on using docmd.outputo or transferspred sheet method definietly
datasource will be exported and autostart its an great advantage, but why the
excelfile not retaining my additional calculations done on it and the format
of cells
kindly advise the remedy please
thanking you

 

[Pieter Wijnen]

Because the OutputTo does not <g>
AFAIK you'll need to use automation to retain the calculations
The format you may be able to retain by using an Excel Template

Pieter

 

[Guest]

dearsir,
exactly, but i recquest your kindself shall teach me an programatic demo
code that does the job please.

 

[Pieter Wijnen]

Try google search for automate excel code sample

Pieter