Import Data Into Excel
There are many instances we need to import data into Excel. The most commonly known method is to use export the data from the database in CSV or text form and then open the exported file in Excel. This method will put the data into an Excel worksheet. The data will remain unchanged.
You can skip a step from the above method by using the data import function provided by Excel. To activate this method, go to the menu and select Data -> Import External Data -> Import Data. This approach can be used for a number of databases, the most common is Access databases, DBase, Excel files, CSV files, Text Files, etc.
The above 2 methods are definitely useful methods for presenting data in Excel. The disadvantage of these 2 methods is that the data imported does not change. The data records remain the same when you opened it the next time, in one day, one week or even one year later. Therefore, to get the updated records from the database, you have to import the data again.
If you need to get the updated records very frequently, such as every day, or every week, then the above 2 methods to import may not be efficient for you. You would have to use the more advanced approach of importing the data, that is, using the MSQuery function provided in MS Office. The function will allow you to import data recently updated from the database when you click on the refresh function given in MSQuery. ALTernatively, by set the option to have the data refreshed at the regulary interval. Do note that this option works when the file is opened. To get the updated data all the time, you can turn on the setting to have the query refreshed when you open the Excel workbook. This will make sure that the latest set of data is presented when you opened the workbook. And MS Query allows you to combine more than one table so that data from different data table can be presented in one single worksheet. This approach requires you to install MSQuery that does not come installed in the standard installation (up to MS Office XP) even though it is provided free with the MS Office Package.
The final method to import data that I would like to share with you here is to use VBA or Macro. It is not using macro recording. It is using ADO Connection which offers one of the fastest ways to connect and present data from databases. To use ADO connection, you need to provide a connection to the database (in this case, we are using an Excel workbook as a database. This is done by using the following procedure or set of codes:
[supsystic-tables id=14]
Notice the content in the bracket [Public Sub ConnOpen(ByVal sPath As String)]. It is to allow us to pass values into the sub procedure. In this case, we are to pass the path of the worksheet into the connection.
Next, we need to get the records out from the database. By using the asterisk (*), we can pull out all the records in the data table:
[supsystic-tables id=15]
After putting the above 2 sets of codes into a module, you are ready to import data into an Excel worksheet. Let’s use a procedure name called “Test” to present the data in worksheet “Sheet1”.
[supsystic-tables id=16]
Now, all you have to do is to run the macro called Test and you will be able to import data into the Excel worksheet.
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
