Opening Excel Database directly

In the World of Personal Computers many Applications came and disappeared. One of the very popular Applications was WordStar (Word Processor) used under Operating Systems CP/M (Control Program for Microcomputers) and DOS (Disk Operating System) till 1992. There were dedicated Word Processing Machines in those days using WordStar and competing Software WordPerfect. Microsoft Excel is also one of the very early days Applications, since 1985, and we all know how popular it is.

The first Worksheet Program Visicalc came out in 1979 with a bang and branched out into several names Supercalc, Multiplan, Lotus 1-2-3, Microsoft Excel and others putting more power into these clones than the original Worksheet Program Visicalc. (Source: Wikipedia)

Like Microsoft Access, Microsoft Excel also has very powerful Automation features that can make working in Excel more interesting. I have published two Articles in articlebase.com featuring two simple macros for Automation. The links are given at the bottom of this Post. If you would like to have a look at it, please do.

Spread Sheet Programs are designed for analysis that involves a chain of calculations with all the information spread across the Sheet visible to the user. A small change at the beginning of the calculation triggers a chain reaction across the cells, which have formula that depends on other cells, and the end result is instantaneous. It is an invaluable tool for What. . If. . . Analysis. When we think about Graph Charts the first name comes into our mind is Microsoft Excel.

With limited features of Database Functions also built into it with the power of Filter, Sort etc. This is where we step in to introduce a Table in Excel for our new example for Reading/Updating the Excel data from Access.

Even though there are facilities in Excel to implement general database rules for creating and maintaining a table it is often not followed. It is left to the user to decide how to create a worksheet and how to create a database when both can go into a single Worksheet side by side.

In Access, there are strict rules that we should follow, like should not enter text into Numeric Field, or cannot enter Text larger than the field size, Field Names must be unique and so on. All these rules are applicable in Excel also. But unlike in Access if we don't comply with any of these rules it may not give you an indication to correct it but it will not work as you have originally planned. All the Data Field Types available in Access are not present in Excel, like True/False in Access, but these constants are valid values in Excel Cells.

Before we quickly introduce a database in Excel and open it directly in Access as we did for dBase File let us look into an example of setting field Validations in Excel to restrict Data Entry in a Cell. You will be surprised to see how powerful it is.

Example: Accept only values between 25 and 100 in a Cell or Cells.

Open Excel and select a Cell in Sheet1.

Select Data- -> Validation . . . – -> Settings.

Select Whole Number in the Allow: Control.

Enter 25 in the Minimum Control and 100 in the Maximum Control.

Select the Input Message Tab. Enter Age in the Title Control and type Enter Value between 25 and 100 in the Input Message.

Select Error Alert Tab and type Value Error in the Title Control.

Type Valid Value between 25 and 100 in the Error Message Control and Click OK to Close it.

Try entering a value less than 25 or greater than 100 in this Cell, it will display the Error message that you have set up in the Validation Control.

You can apply the same rules quickly to other cells. Copy the Cell, highlight the Range of Target Cells and select Edit – -> Paste Special – -> Validation. If you paste it over existing data it will not validate the field contents if wrong value is already present in the Cell. The validation check is performed only when you manually key-in values.

Following the same procedure try setting validation rule in a Cell to accept only Text Length, Less Than, 15 characters. Try entering 16 characters or more into that Cell.

When we plan for creating a database in Excel we can define short and meaningful Headings on the top row to stand for Field Names and set up Data Entry rules, following the procedure explained above, to each Field cells so that they will accept only valid values into them. You already have a Data Entry/Search Form in Excel like in Microsoft Access.

If you have a Data Table in Excel then click anywhere within this Table, select Form . . . from Data Menu. You will get a Data Entry/Search Form. Clicking the Criteria Command Button will turn it into a Search Form, in that you can enter Search Criteria into the Field, which you want to use to find your record. You can try this after we create a Table in Excel quickly for our VBA Program to open the Excel Table directly in MS-Access.

Open Microsoft Excel (if you have closed it).

Open the NorthWind.mdb sample database. Check the link Saving Data on Forms not in Table for location references, if you are not sure where this file can be found.

Open the Categories Table in Datasheet View.

Right-Click on the top left corner of the Datasheet View and select Copy from the shortcut Menu.

Click on the Excel Icon on the Taskbar to open it and select Cell A1.

Select Paste from Edit Menu.

While the highlighting is still on the pasted Table, select Insert – ->Name – -> Define and type Categories in the Names in Workbook Control and Click OK to close it.

Save the Workbook with the name: C:\My Documents\myData.xls and close Microsoft Excel and close Northwind.mdb sample Database.

Open any one of your Databases or create a new one.

Copy and paste the following Code into the Global VBA Code Module of your Database and keep the Module open. You may save the Module by selecting the Save Toolbar Button or with File – -> Save option.

Public Sub OpenDirectExcel()
'-----------------------------------------------------
'Open Excel Table directly
'Author : a.p.r. pillai
'URL    : www.msaccesstips.com
'All Rights(c) Reserved by msaccesstips.com
'-----------------------------------------------------
Dim db As Database, rst As Recordset
Dim strSql As String, i As Integer
Dim msg As String

strSql = "SELECT Categories.* FROM Categories IN 'C:\My Documents\myData.xls'[Excel 5.0;];"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

i = 0
With rstmsg = ""
Do While Not .EOF And i < 5
   msg = msg & ![Category Name] & vbCr
   If ![Category Name] = "Confections" Then
      .Edit
      ![Category Name] = "Chocolates"
      .Update
   End If
   i = i + 1
   .MoveNext
Loop.Close
End With

MsgBox msg, , "Product Categories"

Set rst = Nothing
Set db = Nothing

End Sub

Click in the middle of the Code and Press F5 to Run it. Displays the Product Category Names of first five Records from the Categories Table in C:\My Documents\myData.xls in a Message Box.

Note: In the VBA Code we have Tested the Category Names Field for the value Confections and updated the Value Chocolates back into Excel Cell overwriting the word Confections.

Check the SQL Syntax in the Code that pulls the data directly from the Named Range Categories in C:\My Documents\myData.xls file.