As my username suggests,I'm a novice programmer. I want to write a code which enables me to send data from a number of queries in MS Access into an MS Excel spreadsheet in one go. The code should be written within MS access in VBA.If its not possible then, even a VB form would do. Please help....

You will want to set up 1 or more variables which will be used to keep track of where you are in the process, such as an incrementable row counter and one for the column. (Long integers “Dim x As Long” would be a good variable-type choice for that.)

After opening 1 or more recordsets, loop through that/those recordset(s), filling in the Excel file using the row and column counters to determine where to put the data. (You will need to open Excel through CreateObject() or GetObject() [see VBA help for how to do that], and use the available methods to open the target spreadsheet.)

Alternatively, you can export the results of a query to excel through the processes available on the File menu of Access.

What you can do with these two methods is different—they each have their strengths and weaknesses.

Thanks for the reply Brian. I tried using the 'Export' and 'Analyze it with Microsoft Excel' but I have to select each query and export it. I want all the queries to be exported at one time. I am very new to programming and couldnt understand your reply(the first part) properly. Can you please code a little part(opening recordsets and loops) so that I can understand it easily??? Thanks in advance!

One way to learn the innards of Access programming with VBA is to create macros that do what you want, then convert them to VBA and have a look at how the task was done.

That is one thing I would do in your case. Since you can accomplish what you want one-at-a-time from the menu system, you should be able to create a macro that does the same thing.

Having done that, you can convert the macro to VBA to see how to set that sort of thing up in code. (That is not a step to facilitate what you are doing, since, if the macro does the job “you’re there!” so to speak.)

One advantage to converting to VBA though, is that you can add error handling, which will allow you to step over a 'stumble,' and will make it so that you can include notification on the screen of errors or progress.

Hey Thank you soo much Brain!!!That works!!!