Excel macro to execute Access query

[Denise]

Can I execute an Access query from an Excel Macro and bring back the data
into the spreadsheet?

 

[Joel]

The best way to start is to record a macro while setting up the query manually.

From Worksheet menu
1) Tools - Macro - Record new macro
2) Data - Import External Data - New Database Query
3) Tools - Macro - Stop Recording.

 

[FSt1]

hi,
technically this built into xl via microsoft query.
2003 on the menu bar....data>inport external data>new database query
set access db as the data source.
scroll to your access db.
when the table source apears, scroll down to the query and pull all of the
queries fields into MSQ then just follow the rest of the wizzard.

regards
FSt1

 

[FSt1]

hi
if you have 2007 see this site
http://office.microsoft.com/en-us/help/HA101491511033.aspx

regards
FSt1

 

[Ronald R. Dodge, Jr.]

I have attempted to use MS Query in the past with XL97, XL2000, and XL2002
only to run into Excel crashing on account that MS Query uses ADO, and
within Excel, there is a documented issue with memory leaks when using ADO.
I don't know if this issue has been fixed in XL2007 or not, but it is
documented for prior versions.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[FSt1]

hi
i've been using MSQ since xl97 and have had no such issues.

regards
FSt1

 

[Ronald R. Dodge, Jr.]

Here's the link to the issue that I have faced.

http://support.microsoft.com/kb/319998

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000