Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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 of 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 Excel computing very interesting.

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 a 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 that comes into our mind is Microsoft Excel.

Excel Table

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 in 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.

Validation Settings.

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

  1. Open Excel and select a Cell in Sheet1.

  2. Select Data- -> Validation . . . - -> Settings.

  3. Select Whole Number in the Allow: Control.

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

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

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

  7. 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 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, 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, for each Field cell 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, and 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, 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.

Preparing for a Trial Run.

  1. Open Microsoft Excel (if you have closed it).

  2. 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.

  3. Open the Categories Table in Datasheet View.

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

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

  6. Select Paste from Edit Menu.

  7. 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.

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

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

  10. 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
          ![Category Name] = "Chocolates"
       End If
       i = i + 1
    End With
    MsgBox msg, , "Product Categories"
    Set rst = Nothing
    Set db = Nothing
    End Sub
  11. 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 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.

  1. Microsoft Excel and Automation
  2. Microsoft Excel and Automation-2

Next: Database Connection String Properties

Earlier Post Link References:

  1. Roundup Function
    of Excel in MS-Access
  2. Proper Function of Excel in Microsoft Access
  3. Appending Data from Excel to Access
  4. Writing Excel Data Directly into Access
  5. Printing MS-Access Report from Excel
  6. Copy Paste Data From Excel to Access2007
  7. Microsoft Excel Power in MS-Access
  8. Rounding Function MROUND of Excel
  9. MS-Access Live Data in Excel
  10. Access Live Data in Excel- 2
  11. Opening Excel Database Directly
  12. Create Excel Word File from Access

1 comment:

  1. [...] 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 [...]


Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code