Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel VBA
Resolved · Medium Priority · Version 2003
Reply Mon 13 Oct 2008, 09:50Delegate Zehra said...
Zehra has attended:
Excel VBA Intro Intermediate course
Excel VBA
Can I use VBA to extract data from Access and populate in to a report
For upcoming training course dates see: Pricing & availability
RE: 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
RE: Getting data from Access
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
|