A few questions regarding exporting query to Excel for a newb

[analystkyo]

Hi all,
I've taken on the task of automating some reporting systems at work that will significantly alleviate some heavily manual database reporting processes for at least 5 of my co-workers. I don't have a background in programming but am a fairly quick learner. Here is part of the process that I am trying to work out now. I am trying to sort out the VBA code such that I can export the output from a query to a specific row in an Excel spreadsheet. I have done a fair amount of reading on VBA and I was hoping someone could help me to clear up a few questions I have regarding this process.
I understand that I am going to be referring to the recordset object of the query; my question regarding this is if it is necessary to refer to the SQL at all in the VBA or can I just simply refer to the query?
Another question that I have is that I've noticed in a lot of the code I've seen for exporting Access data to Excel, there is code written that references loop procedures; is this because each individual row of the query is being exported one at a time into the Excel worksheet object, is this necessary? Can't I just export the entire result of the query to the Excel worksheet object at once without having to use a loop control procedure?
Finally, my last question relates to referencing VBA code from a Macro in Access. If I wish to do this I use the runcode module and reference the VBA module I wish to run. I have copied some VBA code into the VBE, and pointed to the module in the expression builder; however nothing shows up in the box of the expression builder that is used to populated the function name field.
It's probably obvious that I'm a little confused and my questions are all over the place. I do appreciate anyone's input or advice they will think will be useful in helping for me to understand this better. I'm just in a bit of a rush to put all the pieces together as quickly as possible. Thanks in advance.

 

[analystkyo]

I have one more question that relates to the task I am trying to accomplish. I have created a macro that ties the query referenced above to a combo box with a list of movie titles on a form. I have the macro written as follows; starts with an open query action(the query has a reference in the criteria field to the combo box on the form from which the user inputs movie titles), the second action will reference the code that I will be writing in VBA. I am a little confused if in the VBA code I am writing if I can just refer to the query as the recordset or do I also have to explicitly detail in the VBA that the query has to point to the combo box in the form with the movie titles. I'm assuming that since the query already contains the reference to the combo box in the form that all I have to do in the VBA is refer to the query. Is that correct?

 

[PHV]

Why not simply use a QueryTable in EXCEL ?
menu Data -> External data -> ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[analystkyo]

The issue is that users need to input movie titles into a form that links to the query in Access and so it seems the most direct route would be to have Access output to Excel. Thanks for your input, I appreciate it.

 

[JerryKlmns]

analystkyo

>refer to the SQL at all in the VBA or can I just simply refer to the query?
The source property of the recordset object could refer to a table/query or valid SQL statement.

> Can't I just export ... at once without ... loop procedure?
You could use the CopyFromRecordset method of the excel's Range object if you use Excel2000 or above. Unless you have some fields that need to be treated in a special way and haven't done it in the query, so you loop records and treat the fields accordingly

> referencing VBA code from a Macro
Macros execute only Functions and not Subs. If you need to run a sub make a function that calls the sub or change the sub to a function

Your 2nd post is a bit vague...