Macro to copy Access table into excel

Hi all,

I was wondering if I could get help with a macro to copy a table into excel.

I am currently using Office 2010 and the database is an accdb and not mdb.

My knowledge is not in programming but I lurk on many a code forums putting snippets together of VB to do tasks such as merging multiple tabs on one workbook to opening multiple workbooks and copying tabs into one single workbook or running macros when certain cells are opened/workbooks are opened. So I can get my head around code I'm just not good at writing from scratch.

Anyway i have become stuck when it comes to pulling a table from access into excel.

What I want to happen is that when a workbook is opened a macro will run that copies all the data from a specific table in the access database and pastes this into a specific sheet in the active workbook.

This means that i will always have the latest data in the workbook as things are added to the database through the day without having to go and copy the table myself.

I have found a few examples of this online but none appear to work and I have exhausted my knowledge to do this

Unless of course I am missing a much simpler solution?

Thanks in advance.

Thanks for the swift response. I did try this but the DB is quite large and my excel jammed up.

The other issue is the db is usually open all the time by other people and when I tried the external access it wasn't allowing me to do it due to it being open.

I will explore this possibility again, but the reason why I thought about the macro was to avoid any slowness and avoid the access problems.

Originally Posted by buran

you can do this with macro, but I would suggest that you explore the functionality to import data from database (Data tab->Get external data group->From Access). You can set to refresh the query at workbook open

Thank you. I have just done this and noticed the "read only" option and the table opened immediately in excel and had no other issues.

Thank you for steering me in the right direction.