Access macros in Excel - populate results in spreadsheet
 

I've got a macro in Access that runs two Append Queries to a table. I want
the resulting table to populate an Excel worksheet - can I use the Import
Data function in some way to do this? I already use it to Import some
regular query results.

Access macros in Excel - populate results in spreadsheet
 

In access, right click on the query that generates your data, or the table
that contains the data and select Export. At the bottom of the Export dialog
box select the Save File As Type combo box and select the Excel flavor of
your choice. You should have Excel 3, Excel 4, Excel 5-7 and 97-2000 if
you're using Office 2000.

You could also do this running MS Query from Excel too.
--
Kevin Backmann


"Dawn" wrote:

I've got a macro in Access that runs two Append Queries to a table. I want
the resulting table to populate an Excel worksheet - can I use the Import
Data function in some way to do this? I already use it to Import some
regular query results.

Access macros in Excel - populate results in spreadsheet
 

Thanks Kevin - Ideally, I want my end-user to not have to go into access -
but click "refresh all" in Excel and have it go to access and pull in the
results - is that doable?

"Kevin B" wrote:

In access, right click on the query that generates your data, or the table
that contains the data and select Export. At the bottom of the Export dialog
box select the Save File As Type combo box and select the Excel flavor of
your choice. You should have Excel 3, Excel 4, Excel 5-7 and 97-2000 if
you're using Office 2000.

You could also do this running MS Query from Excel too.
--
Kevin Backmann


"Dawn" wrote:

I've got a macro in Access that runs two Append Queries to a table. I want
the resulting table to populate an Excel worksheet - can I use the Import
Data function in some way to do this? I already use it to Import some
regular query results.

Access macros in Excel - populate results in spreadsheet
 

If you have MS Query installed (and you should, unless you did a custom
install) you can connect to your Access database using MS Query.

Click Data in the Excel menu, select Import External Data, New Database Query.

If necessary, click the Databases tab and select MS Access Database, then
click OK.

In the SElect Database dialog box, locate your MS Access database, and then
select the table you wish to query, select your fields, state your criteria,
save the query so it can be run again without rebuilding it and return the
data to Excel.

An External Data tool bar should display as a floating tool bar, but if not
just click on View in the menu, click Tool Bars and locate the External data
tool bar. The ! button refreshes the data in the workbook.

If that doesn't give you enough flexibility you could write a macro in
Access, or Excel to do that, but you might want to post that in the Excel
Macro forum. I'll take a look around and see if I have some code that can at
least point you in the proper direction.
--
Kevin Backmann


"Dawn" wrote:

Thanks Kevin - Ideally, I want my end-user to not have to go into access -
but click "refresh all" in Excel and have it go to access and pull in the
results - is that doable?

"Kevin B" wrote:

In access, right click on the query that generates your data, or the table
that contains the data and select Export. At the bottom of the Export dialog
box select the Save File As Type combo box and select the Excel flavor of
your choice. You should have Excel 3, Excel 4, Excel 5-7 and 97-2000 if
you're using Office 2000.

You could also do this running MS Query from Excel too.
--
Kevin Backmann


"Dawn" wrote:

I've got a macro in Access that runs two Append Queries to a table. I want
the resulting table to populate an Excel worksheet - can I use the Import
Data function in some way to do this? I already use it to Import some
regular query results.