Export Queries To Current Active Spreadsheet

[carlnewboult]

Hiya,

Not sure if this is possible but am really hope that it is as it takes me ages to enter it manually. I have several queries which need to enter the information they produce into a spreadsheet. I have done this before simply by linking the spreadsheet to the access database. However this time the spreadsheet could be a wide range of different names and stored in different locations. What I want to know is can I export a query to the spreadsheet which is currently open ?

Thanks in advance people.

 

[RCurtin]

Don't really understand why you need to have this information in a spreadsheet?

By the way, you can delete posts if you accidently posted it a few times by going to the post and clicking 'edit' below your message.

 

[carlnewboult]

This information needs to be in a spreadsheet as after completion it is emailed off to various people who have not used access.

 

[Rank Am]

If you don't know the name and path of the spreadsheet it will cause you problems.
The easiest way of doing this would be to use the Microsoft Query bundled with office to access the database via ODBC directly from excel. You can access both tables and queries using this - however it does not contain the full functionality of Access SQL. Also will fail if the query has parameters as it won't be able to evaluate them
If you want to use VBA say from a command button on a form the two ways I use are to create and executue a forward only ADODB recordset from a command object using the stored query or SQL statement.
Set an instance of XL running in memory (you will need to ensure that the XL object library is selected in references) - You will need to know the filename, path and worksheet references for the XL object. I usually use a series of XL templates for stored in the same directory as the db with named ranges.
You can either then copy the recordset into an array and assign it to a data range, antoher method is to use copyfromrecordset.
I can send you a sample db that demos this tomorrow when I am back in the office if you like

 

[carlnewboult]

Rankam that would be great if you could post a demo as having troubles setting this up