Excel VBA

Hi Zehera

Thank you for your question

The simple answer is yes you can.

You need to use Active X Data Objects to do this. These are a series of standard tools that work alongside VBA to extract data from a relational database. A detailed description of their use is beyond the scope of this forum.

We cover ADOs on both our Access VBA course and our advanced Excel VBA course

Regards

Stephen

Zehra,
The first thing to check out is:
Data tab> Get External Data >From Access

This allows you to get data from any table or query in a specified Access database. This will bring the data in just as it would appear in a Datasheet view in Access. In 2007, the default form is as a Table, but you can Convert to Range.

It might be that the data is not quite in the form that you want it in Excel (for example, you might need to insert blank rows, to match layout with the in-Excel data). In which case, you'll need to create a transformed version of the data on another worksheet (this might be something to do using VBA).

If you decide that you really do need to have complete control over the import process, then you need to do it via VBA, using a Recordset object (the class is in the ADODB library). A Recordset object is a data structure that can be linked to a database and either pointed at a specific table, or given an SQL query string. Load the ADODB library (>Tools >References) and read the Help pages for Recordset and related classes. Having constructed a Recordset (on the Excel side) you can then do Range("...").CopyFromRecordset, which imports the data (again, check out the Help pages). As you will see, this is getting quite technical...

Hope this helps
/Roy MacLean