Getting data from Access in Excel

[Peter Vail]

I am asking this question on behalf of my brother who is in PNG. He asks
(working in Excel/Access 2000):
Have managed to persuade Excel to play ball and produce the sort of output I
want, now all I need to know is how to get Access to talk to Excel or vice
versa so I can wrap the whole thing up in one user-friendly interface.
Ideally Excel would tell Access to open the database, export the query to a
spreadsheet, and then I can take it from there. As usual, the help is
helpless (though there is a topic there).

Any help appreciated.
Thank you
Peter V

 

[Frank Kabel]

Hi Peter

go to 'Data - External data - Import data' (i hope this is the right
translation of my german version..) and select your Access database and
the sepcific table/query.

Frank

 

[onedaywhen]

If you just want the data (i.e. no formatting), it may be easier to do
it from within MS Access using a SELECT..INTO query, something like:

SELECT RefID, Surname, Forename1
INTO [Excel 8.0;Database=c:\MyWorkbook.xls].[MyWorksheet]
FROM PersonalDetails

If the workbook and/or worksheet specified does not already exist, Jet
will create it on the fly.

 

[steam3801]

I am asking this question on behalf of my brother who is in PNG. He asks
(working in Excel/Access 2000):
Have managed to persuade Excel to play ball and produce the sort of output I
want, now all I need to know is how to get Access to talk to Excel or vice
versa so I can wrap the whole thing up in one user-friendly interface.
Ideally Excel would tell Access to open the database, export the query to a
spreadsheet, and then I can take it from there. As usual, the help is
helpless (though there is a topic there).

Any help appreciated.
Thank you
Peter V

Rather than Excel send the data to Access, you need Access to import
the data from Excel

Create an Access macro using the Action command TransferSpreadsheet,
and completing the details in the parameters box which is displayed
when this option is sleected
I.e.
Transfer type : Import
Spreadsheet Type : Microsoft Excel (version)
Table name: (new table to be created)
File name: (full path and filename )
and so on

When you open Access, include this macro in the autoexec macro and the
datanbase will er-import the spreadsheet each time

Alternatively, link the spreadsheet so if any upadtes are done when
the db si open, they will be reflected

You know it makes sense.
steam3801

 

[onedaywhen]

I seemed be having trouble following things today. Surely the OP wants
to end up with the data in a spreadsheet. So why tell them, "you need
Access to import the data from Excel"? It certainly doesn't make sense
to me.

--

 

[steam3801]

I seemed be having trouble following things today. Surely the OP wants
to end up with the data in a spreadsheet. So why tell them, "you need
Access to import the data from Excel"? It certainly doesn't make sense
to me.

Now you've made me read it again, I'm confused now as well.

Early para : "Have managed to persuade Excel to play ball and produce
the sort of output I want, now all I need to know is how to get Access
to talk to Excel ... <snip>

Excel output .... Access to talk to Excel = suggests import

Later para : Ideally Excel would tell Access to open the database,
export the query to a spreadsheet

Now this does suggest the reverse

So, same basis would apply - TransferSpreadsheet macro action,
paramater would be Export rather than Import, bearing in mind that
Access would create a whole new Excel file, and replace any existing
file in its entirety.

You know it makes sense.
steam3801

 

[Peter Vail]

Thanks all for your help. I have the passed your responses on.
Sorry if I have confused people. The requirement is to produce a fairly
complex report and my brother has found that he needs Excel for this
particular report. The database is in Access.

Peter V