Opening Excel Database directly ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

In the world of Personal Computers, many applications emerged and disappeared. One of the very popular applications was WordStar (Word Processor), used under Operating Systems CP/M-80 (Control Program for Microcomputers) and MS-DOS (Disk Operating System) till 1992. There were dedicated Word Processing Machines in those days, using WordStar and competing Software WordPerfect.

The first spreadsheet program, VisiCalc, was released in 1979 and made a groundbreaking impact. It soon inspired several successors—SuperCalc, Multiplan, Lotus 1-2-3 (1983), and eventually Microsoft Excel (1985)—each adding more power and functionality than the original VisiCalc. (Source: Wikipedia)

Like Microsoft Access, Microsoft Excel also includes powerful automation capabilities that make spreadsheet-based computing both flexible and engaging.

Spreadsheet programs are designed for analytical tasks that involve a chain of calculations, with all relevant data visible on the sheet. A small change made at the beginning of a calculation can trigger a cascading effect through dependent cells, instantly updating the final results. This makes Excel an invaluable tool for “what-if” analysis. And when it comes to creating graphical charts, Microsoft Excel remains the first name that comes to mind.

Excel Table

Although Excel includes limited database-like features, such as built-in functions, filters, and sorting capabilities, it can still be used effectively for structured data management. This brings us to our next example—creating a table in Excel that can be read from or updated by Microsoft Access.

While Excel provides the tools needed to follow general database principles when creating and maintaining a table, these rules are often overlooked. Users are free to design their worksheets in any manner, even mixing regular data and database-like tables side by side on the same sheet.

In contrast, Access enforces strict data integrity rules—for example, text cannot be entered into numeric fields, field names must be unique, and data cannot exceed defined field sizes. The same principles technically apply in Excel, but violations often go unnoticed because Excel doesn’t always flag them. As a result, the worksheet may not function as intended when used as a data source.

Moreover, not all Access data types exist in Excel—for instance, the True/False field type in Access has no direct equivalent, although Boolean values like TRUE and FALSE are still valid entries in Excel cells.

Before we create a database-style table in Excel and open it directly in Access (as we did with a dBase file), let’s explore how to set data validation rules in Excel to restrict cell entries. You may be surprised by how powerful these validation features can be.

Validation Settings.

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 the 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, and 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 the 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 a validation rule in a Cell to accept only Text Length is Less Than 15 characters. Try entering 16 characters or more into that Cell.

When planning to create a database in Excel, begin by defining short, meaningful headings in the top row—these will serve as your field names. Next, apply data entry rules (as explained earlier) to each column so that only valid values can be entered. Excel also provides a built-in Data Entry and Search Form, similar to the one available in Microsoft Access.

If you already have a data table in Excel, simply click anywhere within the table and select Form from the Data menu. This will open a Data Entry/Search Form. By clicking the Criteria button, you can switch it into search mode, where you can enter specific criteria in any field to locate a record.

You can experiment with this feature after we create an Excel table, which we’ll later open directly in MS Access through a VBA program.

Preparing for a Trial Run.

Open Microsoft Excel (if it is already closed).
Open the NorthWind.mdb sample database. Check the link Saving Data on Forms not in Table for location references, if you don't know 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 the Edit Menu.
While the highlighting is still on the pasted Table, select Insert -> Name -> Define and type Categories in the Names in Workbook control, click the OK button to close it.
Save the Workbook with the name: C:\My Documents\myData.Xls, and close Microsoft Excel and close the 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 the 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 rst

msg = ""
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 the 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 Confectionaries, and updated the Value Chocolates back into the Excel Cell, overwriting the word Confectionaries.

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

Next: Database Connection String Properties

Earlier Post Link References:

import spreadsheetSeptember 22, 2011 at 2:07 AM
[...] tables directly in the Query and add the data into the target table. You can learn this method from here. __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn [...]