Excel automation from Access

  • Thread starter Thread starter Bob Quintal
  • Start date Start date
B

Bob Quintal

I have a report which I export to Excel with the following line
(hidden in a button) :

DoCmd.OutputTo acOutputQuery, "qryTotal",
"MicrosoftExcelBiff8(*.xls)",
AutoStart:=-1

This works great.
It exports the data, opens Excel and displays my data.

The number of rows can of course vary, say I have in this
particular instance 20 rows.

How can I, from within Access, have Excel
- move to the first empty row (row 21),
- go to a predefined cell,
- there make the sum of the above numbers, without, of course the
first row, which contains the column headers ?

This sounds complicated, and for me it is.

Any help will be appreciated. So thanks beforehands,

W
Click to expand...
It is complicated.

Instead of opening the query with the autostart,
open the spreadsheet using code found her
http://www.mvps.org/access/modules/mdl0006.htm
the statement .Worksheets(1).Cells(1, 1) = "Hello World"
will need changing as follows.
use a dcount() to get the number of rows in your query, and add 2 (
1 for header, +1 to move past last line exported, the column number
you should know already.
and what you want to put in the cell is "=sum($X2:$Xn)"
where X is the letter identifier of the column you are summing, and
n is your column count +1.
 
I have a report which I export to Excel with the following line (hidden in a
button) :

DoCmd.OutputTo acOutputQuery, "qryTotal", "MicrosoftExcelBiff8(*.xls)",
AutoStart:=-1

This works great.
It exports the data, opens Excel and displays my data.

The number of rows can of course vary, say I have in this particular
instance 20 rows.

How can I, from within Access, have Excel
- move to the first empty row (row 21),
- go to a predefined cell,
- there make the sum of the above numbers, without, of course the first row,
which contains the column headers ?

This sounds complicated, and for me it is.

Any help will be appreciated. So thanks beforehands,

W
 
Hello Bob,

I tried the code, which seems to run without errors, but : Excel does not
start, nor do I see anything.
Did I miss something ?

Thanks for your answer,

W
 
Hello Bob,

I carefully reread your answer and a few other tips on other sites and, lo
and behold, I eventually got Excel working from Access.

Thank you very much.

W
 
Back
Top