PDA

View Full Version : Is it possible to automate results from an access query into Excel?



wedd
07-25-2011, 05:01 AM
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.:friends:

Aflatoon
07-25-2011, 05:18 AM
You can, subject to certain limitations, link a query directly into an Excel worksheet. Which version of Excel are you using?

wedd
07-25-2011, 05:19 AM
Hi, I am using Excel 2010.

wedd
07-25-2011, 05:20 AM
Aflatoon.

Aflatoon
07-25-2011, 05:25 AM
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
07-25-2011, 05:33 AM
Thanks for that Aflatoon...Is it possible to link all the query results on to the same work sheet?

Aflatoon
07-25-2011, 05:35 AM
Yes - you can put the results anywhere you want.

wedd
07-25-2011, 05:36 AM
Excuse me if this is a silly question. Is this what is automation?

Aflatoon
07-25-2011, 05:41 AM
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
07-25-2011, 06:38 AM
Aflatoon?

Aflatoon
07-25-2011, 06:43 AM
Yes?

wedd
07-25-2011, 06:46 AM
Thank you sir! :friends:

wedd
07-25-2011, 08:27 AM
Aflatoon, do you know where I can find sample code to perform what I want to do?

Aflatoon
07-25-2011, 08:37 AM
Can you not just set them up manually in the workbook? They will update automatically after that when you open the workbook.

Kenneth Hobs
07-25-2011, 08:49 AM
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
07-25-2011, 08:51 AM
Thanks Kenneth!

wedd
07-25-2011, 09:11 AM
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.