Pull data from Access Database into Excel
(Without Opening Your Access Database)
You are going to love this Excel Addin — if you regularly Pull Data from Access into Excel,
Why? Because you could do following in EXCEL without opening your ACCESS database.
5 Powerful Features of AddinX
- Choose and Pull Data from the Specific Fields You Need
- Aggregate and Group Data like a pivot table in many interesting ways
- Design and run Queries
- Create relationships between tables.
- Maintain history of your recent Access Databases for quick access later
You can download the copy of this Addin from the link below
[download id=”229″]
STEP BY STEP INSTRUCTIONS TO USE “PULL FROM ACCESS” ADDIN
How to Install this Addin
Save the AddinX.xlam file in your PC.
Note: Please don’t change the name of file, otherwise some features would not work
Open any excel workbook. Press ALT + T + I to open the Addin Manager. Browse for the file and enable the Addin
Once installed you will see a new tab “ACCESS” in your excel workbook. Activate the “Access” Tab and press the “Smiley Face Button”
A dialog form would appear as shown below
Retrieving Data from Specific Access Fields
Press the “Select Access Database” to browse and select your access database.
The database won’t open, however you would see the list of all the tables in the “Select Table” frame. The file details would be stored in the quick links tab for quick access later
Select any table and you will see the next frame “Select Fields” gets populated with all the fields in that table
Select and Add your desired fields to the “Fields I want” frame. You can remove rearrange, remove or add fields using the command buttons beside the frame
Click anywhere in the excel workbook where you want data to be placed and PRESS “DONE” button in the DIALOG FORM.
The Dialog Form works in MODELESS fashion i.e. you can work in EXCEL while the Dialog Box is open
The database file details is added to the QUICK LINKS TAB so that you can quickly access it later
Aggregate and Group items
This powerful feature lets you summarize your data in ACCESS tables which might span thousands or even millions of rows.
Since ACCESS tables are often collection of individual transactions, users/ analysts would be interested in summarizing data. For example in a daily sales table having ten thousand rows of individual sale transactions, you might like to see the total sales for a particular period for each customer and for each sales person.
Check the “Aggregate and Group” check box as shown below; you would be directed to a new tab “Aggregate & Group”.
Select any table. You would see the list of fields in both “Choose fields to Aggregate” and “Choose Field to Group by” frames.
Only choose those fields to aggregate which contain numeric values, select the aggregation function (sum, count, average etc.) you wish to apply.
Only choose those fields to Group by which contain non-numeric values
Select any cell in your workbook and Press Done ; you will see the summarized data reflecting your selections above
You can make queries while using the Aggregation and Grouping tool. For example in the case above you might want to see the results only for a particular salesperson or customer. Queries are covered in detail below.
Make Queries
Queries work like Excel data filters. They allow you to get the particular records you need
To make queries check the “Make Queries” checkbox in choose fields box
Create Relationships and get data between related tables
To select fields from multiple related tables, click the “Select Fields from multiple tables” checkbox as shown below.
A new tab “Create Relationships” would become active now. Select related tables and fields and press the “Create Relationship” button
Return back to “Choose Fields” tab and you will notice that now you can select fields from multiple tables.
Select the cell where you want data to be placed. Press done and check the results
HISTORY OF MICROSOFT ACCESS DATABASES OPENED
The Addin maintains history of 10 most recent Access Databases which you have accessed using this Addin.
This helps you quickly access your most used Databases without having to browse and locate them again.
Go to the Quick Links tab. Just Double click on the Database you want to retrieve data from
[huge_it_share]