To populate Excel sheet

[Roy]

Thanks for taking time to answer.I have a A2K application from which I
run series of Events by clicking few command buttons.This generates
queries,the result of which I copy and paste to worksheets in
Excel.There are 14-15 sheets which I populate manually.This I repeat
for 10-15 users.I use a Excel template which I copy and name it
according to the users and then populate.Is there a way to automate
this?I need to export these queries which are driven by the buttons
,open up the template and the corresponding worksheet.Is there a way to
insert rows based on the number of records in a query and then populate
,so that my formulas in the template are intact?Is this possible or too
tall a task?
Regards,

Roy

[pietlinden]

did you look at the code here?
http://www.mvps.org/access/modules/mdl0035.htm

[Roy]

Thanks for the reply.What I am looking is to have a table containing
the names of the function procedures and in a click event of a button
read all these as a recordset and start exporting one by one.Someone
had posted similar approach to this problem here ,but i am unable to
locate the same. I already know how to transfer this using VBA but it
is not a elegant way.Also is there a way to fcalculate the number of
rows to be inserted based on the number of records?

Thanks

Roy

[Jana]

Roy:

This may be way off base, but perhaps it would be better to have your
Excel templates grab external data from your Access app rather than
pushing data from your app to Excel? If your queries are always stored
under the same names, use MS Query to grab the query in your Access app
as your external data source, and only use your code to update the
queries. In Excel, the External Data Range Properties allow you to
designate that formulas automatically fill down in columns adjacent to
data as well as give you several other fine-tuning options.

On another note, you say that you "run series of Events by clicking few
command buttons". You should be able to program everything into one
button if there is logic behind the sequence that you execute them.
You can reference other subs within the same module by simply calling
them. For instance, if you have
Private Sub MyFirstButton_Click()
[Code for the first button]
End Sub

Private Sub MyNextButton_Click()
[Code for the second button]
End Sub
in the same module, you can trigger the code for the next button in the
first button's code by calling it like this:
Private Sub MyFirstButton_Click()
[All the original code for the first button]
MyNextButton_Click
End Sub

Hope this helps,
Jana