export a query using macro

[luvbite38]

Hi,

Thanks in advance for any help

is there a way to automatically run and export the query into a specific sheet of a Excel workbook ?

Please help

 

[Colby]

Yes, you can either use the MSQuery engine and drop it on a worksheet, then record a macro to refresh the query, or if you need something more advanced you can use ADO.

 

[luvbite38]

thanks mate...
and how do I do this :S

I am totally new to access and excel...
pls help

 

[Colby]

data ribbon->From other sources -> microsoft query.

Or you might want to use the data connection wizard. Either way, you will end up in MSQuery engine which is a graphical query builder the same as in Access.

Once you have connected to the data source, you can select the tables / columns you want, test the query a bit, etc, then return the results to Excel by clicking on the little door icon (just to the right of the old school save disk icon).

 

[alansidman]

Here are two possible solutions for you.

http://www.exceluser.com/explore/msquery1_1.htm

http://datapigtechnologies.com/blog/index.php/running-an-access-parameter-query-from-excel/

Alan