Automatically transfering data from Acces to Excel (2000)

Topic

New Reply

Is there any way in Access to automatically transfer some data from specified cells in Access to Excel

see screenshot for what l mean.

Reply | Quote

Replies

You can use Data | Get External Data | New Database Query in Excel to import data from Access into an Excel worksheet.
You can select Data | Refresh Data or click the corresponding button on the External Data toolbar to update the data in Excel.

Reply | Quote

I have a few questions:

1. How do l select the query from the access database?

2. how can select the data from a specified row within the query?

Reply | Quote

Hi Justin

The method that Hans describes takes you through a wizard. When you select the database you are querying it will take you to a step where it asks you which object you want to get the data from (table or query) once selected you can select the field and then enter the parameter you require. It is similar in look and feel to Access.

Reply | Quote

I tied this but cannot work out how you select the database then select the queries from the database , please can you tell me how l do this, thanks.

Reply | Quote

Select Data | Get External Data | New Database Query, then wait until Microsoft Query appears.
Select MS Access Database and click OK.
Browse to the correct drive, folder and database, then click OK.
From there on, it should be pretty obvious.

Reply | Quote

On selecting the database l get an error, see attachment.

Reply | Quote

Try removing the autofilters from your excel workbook

Reply | Quote

They need to be there for sorting purposes.

Reply | Quote

What does AutoFilter have to do with sorting?

Reply | Quote

I don’t know l was told it has to be removed in order for the “Get External Data” command to work.

Reply | Quote

I thought that is why you had a database, to do the sorting

Can’t you create your queries in Access and then export to Excel

Reply | Quote

What l require is data to be pasted from access into Excel automatically.
Rather than keep having to copy and paste all the time.
The data is sorted in Access.

Reply | Quote

OK

I think I see.

1) Start off with a completely new excel workbook
2) Data | Import External Data | New database Query…

3) Run through to get the database location etc
4) Select the query. and finish wizard and ensure the data starts at A$1$

This will parse all the data from that query into Excel

5) Save workbook.

Each time you open this workbook it will update with the data that is “sitting” on the database. In the past when I have used this method I have used the set of data as my raw dat and I have done my calculations etc on another worksheet…or simply copied and pasted the data from that sheet to another so that i can manipulate it.

Reply | Quote

I cannot work out how you select the query from the database, see screenshot.

Reply | Quote

Go back one step (click Cancel). What do you see then?

Reply | Quote

This is what l see, see screenshot.

Reply | Quote

Try clicking the Databases tab. Then select MS Access Database etc.

Reply | Quote

I done this but cannot see how l can access the queiries, could you possibly show me a screenshot.

Reply | Quote

Right on the Choose Data Source tab, select MS Access database*, then click OKand look for the location of your database on your PC from the next window that opens. Once selected press OK and the Query Wizard starts

Reply | Quote

When you have selected MS Access Database and clicked OK, the next dialog will let you select a database.
After that, a list of available tables and queries will be displayed.

Reply | Quote

Tried this but got an error, see screenshot.

Reply | Quote

Can you open the same database directly?

Added: MS Query may fail if the database is open in Access.

Reply | Quote

Click the Database tab, and locate the database you are connecting to. Remember the queries are objects within the database, not external objects to the database which is what the query tab is looking for.

Reply | Quote

I understand that, but what l cannot underdtand is how l can select them.

Reply | Quote