Is it possible to automate results from an access query into Excel?

[wedd]

Is it possible to automate results from an access query into Excel?
Calling all VBA experienced gurus, geniuses etc. I have created several queries in different databases. What I would like to do is rather than running the query, and copy and pasting the results into Excel...I would prefer if the queries were linked to the different databases and automatically displayed the results on a excel worksheet. Can this be done? If so, how can this be done in as straightforward and logical way? I am not an expert at vba...so if you have any example of vba code or steps in access how to perform this process would be very helpful in my learning...


Thanks for your contributions I appreciate it.

[Aflatoon]

You can, subject to certain limitations, link a query directly into an Excel worksheet. Which version of Excel are you using?

[wedd]

Hi, I am using Excel 2010.

[wedd]

Aflatoon.

[Aflatoon]

In that case:
Data tab, click From Access, select your database, then select the query you want, choose where you want it put, then OK out. Repeat as required for each query you wish to link to.

[wedd]

Thanks for that Aflatoon...Is it possible to link all the query results on to the same work sheet?

[Aflatoon]

Yes - you can put the results anywhere you want.

[wedd]

Excuse me if this is a silly question. Is this what is automation?

[Aflatoon]

No - automation is controlling another application through code. For example, in VBA in Access, you could start an instance of Excel, open a workbook, update some information, then save and close it.

[wedd]

Aflatoon?

[Aflatoon]

Yes?

[wedd]

Thank you sir!

[wedd]

Aflatoon, do you know where I can find sample code to perform what I want to do?

[Aflatoon]

Can you not just set them up manually in the workbook? They will update automatically after that when you open the workbook.

[Kenneth Hobs]

If you want to code it, see threads:
http://vbaexpress.com/kb/getarticle.php?kb_id=889
http://www.vbaexpress.com/forum/showthread.php?t=26145

[wedd]

Thanks Kenneth!

[wedd]

I've done exactly that Aflatoon. But I was thinking it would be quicker to run code since I have to update the query every day. Rather than importing data sets into Excel...I thought running code would automatically do the same thing and open the worksheet and display the data load quicker and may save time...I think your idea is good...but it's just incase I have 20 to 30 queries to link to excel could take a longer time...by running code it will be done in an instant if that makes sense.