How to Use Excel as a Database Engine.

It is possible to update changes to the excel database
By using Excel macros or with the help of any front-end applications.

Before we go further, let us first take a look at excel as database. The basic format for storing data in an Excel database is a table. In a table, data is entered in rows. Each row is known as a record. After the creation of table, Excel's data tools can be used to search, sort and filter records in the database to get the required information. Creating a list from the data in a table is the easiest method of using the data tools.

For creating an Excel DB, the first step you need to follow is to enter the data with no blank rows. A record can contain data about only one specific item. A record must also contain all the data in the database about that item, not more than one row. Column headings are called field names and are used to ensure that the data for each record is entered in the same sequence and is consistent. After the data has been entered into the table, it can be converted to a list. To do that, please select any one cell in the table. Then Choose Data> List > Create List from the menu to open the Create List dialog box. The dialog box displays the range of cells needed to be included in the list.

If we create the table correctly, Excel selects the correct range of fields. Click ok if the range is correct. If the list is incorrect, please click the return button of list box for getting back to worksheet; it will appear as a small box and the current range of cells will be surrounded by marching ants. You will need to select the correct range of cells by dragging the mouse and clicking the button on list box for return back to the normal size and click oK for finishing.

After completion, the list will have a dark border around it and each field name will have down arrows, which will open drop-down menus when clicked. Options such as sorting the DB with field names and searching for records from DB are possible with dropdown menus. Additional data can also be added using blank row containing an asterisk at the bottom of the list.

Merging or combining two excel tables into one database excel file can be based on a common column of data from each file. The two tables can be located in the same Excel file or two different Excel files.

The concept behind the issue is loop into the worksheets, based on a common field for insert and update the data.

For example, name a variable `sh' and loop the worksheets. For each `sh' in this Worksheet. If sh(name) is not equal to destination.name, then insert into last row (destination name). Likewise, for updating any changes in the worksheets to reflect it on destination (Database), you will need to loop through the worksheets with variable, find out any changes, and update it. This can be done with the help of an excel macro based on this concept.

By Using Visual Basic.Net 2005 - VSTO (Visual Studio Tools for Office), this can be achieved with WorkBook.MergeFile method.

You will need to import Microsoft.Office.Tools.Excel for this.

Here is a sample coding: Dim objFirst As Worksheet = New

Worksheet

Dim obj,objFirst,objSecond,ObjThird As

New Workbook

objFirst = New WorkBook.Open(firtFile)

objSecond = New WorkBook.Open (secondFile) objThird = New WorkBook.Open(thirdFile)

objThird.Merge(objFirst, objSecond)

Close and dispose all the variables.

There are various tools and add-ins for merging data sheets into a single Excel workbook.

For more useful tips about excel, please look up

http://www.rondebruin.nl/tips.htm