Macro to tranfer data from access to excel

[bartoni]

Hi.

Ive got a list of 10 querys which i would like to transfer to Excel into the relevatn query worksheet within a workbook called test.xls

i.e table1 should transfer to test.xls and into the worksheet named table1. Can anybody start me off. Is it best to write the macro in Excel or Access and it is possible.

Man Thanks

Ian

 

[reclusivemonkey]

You can link to tables/queries in Access from Excel without the need for macros/VBA. Simply open your workbook and use the Data...Import External Data...New Database Query.

 

[bartoni]

I was hoping to create an automated process as ive got 20 queries to run and wanted then to all be run using one macro and pasted into certain sheets in a workbook with minimal user intervention.
Any ideas?

Ian

 

[reclusivemonkey]

The method I have just given you will have all the data for every query you have linked to in Access without having to run any queries. The data is refreshed on opening a spreadsheet, and can be refreshed manually and automatically a number of ways. Unless I am missing something, this will do exactly what you want, without any need for macros, VBA, copying and pasting or user intervention.