ACCESS TO EXCEL – HELPPLEASE (Excel / Access)

Topic

New Reply

I would appreciate your help with the following:

Using ‘MS-DOS Batch File’ I convert several text files to excel files, using:

COPY “C:Documents and SettingsExample Text File.txt” “C:Documents and SettingsExample Excel File.xls”

Can this be done in the same way, converting microsoft access database (mdb) to excel format.

The only way I know how currently, is to open the database file,copy the data and paste into excel. But there must be an easier way??

Thanks

Reply | Quote

Replies

You can’t just convert an Access database to an Excel workbook – an Access database can hold tables, queries, forms, reports, macros and VBA code.

You can export an entire table (or query) from Access to an Excel workbook, using File | Export…, or in VBA using DoCmd.TransferSpreadsheet.

You can import an entire table (or query) into Excel from Access, using Data | Import External Data | New Database Query…, or in VBA using QueryTables.Add or CopyFromRecordset.

You cannot do this directly in a batch file, but you could write VBscript that automates Acces or Excel.

Reply | Quote

Hi Hans, Thanks for your response….

I use excel for most of my working day (such fun :-))…. However,I know nothing about Access, other than it being a source of data to me that I copy accross to xls. Can I check that I am understanding you correctly?

I can, using a macro in excel – Data / Import External Data / Import Data from a mdb file ?? at the click of a button??

Does that mean I can sack the old ‘ctrl c’ ‘alt esv’.Is the end of the world nigh???

Many Thanks

Reply | Quote

The menu command in Excel is Data | Get External Data | New Database Query…
The VBA equivalent of this menu command is

ActiveSheet.QueryTables.Add connection, targetcell

Look up the Add method of the QueryTables collection in the VBA help.

You don’t need to open Access for this – you can do it all from Excel.

Reply | Quote