Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Display Excel Value Directly on Form


We have already seen how to open dBase Files directly and work with the data in VBA in the earlier Post Opening dBase File Directly. But, there are other alternatives (besides linking the files) to work with dBase Files in Access. We will explore this aspect in detail later. This Post was originally intended for a demo of opening Excel based Data Table directly and working with it in VBA Code. We will do that in the next Post. Before that we will look into some interesting and very easy trick with Excel.

Why I said very easy, because you don't have to struggle with VBA Code to do this. I know you will be happier if you don't have to work with my spaghetti VBA Code. So, I thought we will take a break and do something different and interesting before continuing with databases.

You got the clue from the title itself. That's right, you can display Values from Excel Worksheet directly on Microsoft Access Forms. We will go straight into a Demo.

The Demo Run Preparation

  1. Open Microsoft Excel.
  2. Enter your name in Cell A1 on Sheet1 and minimize Excel, don't close it. You may save the Workbook with a name before minimizing it. The WorkBook may keep interrupting for saving it, if Auto-save feature is on.
  3. Open any one of your Microsoft Access Databases or create a new one.
  4. Open a New Form in Design View.
  5. Select the Text Box Tool from the Tool Box and draw a Text Box on the Detail Section of the Form.
  6. Click on the Text Box and display the Property Sheet (View - -> Properties).
  7. Write the following expression in the Control Source Property of the Text Box:


  8. Save the Form with a name of your choice.
  9. Open the Form in Normal View. You will see your Name written on Excel Cell is appearing in the Text Box on the Form.

    The DDE() Function stands for Dynamic Data Exchange. The first two Parameters are Excel Application and worksheet name Sheet1 and the third one is the Cell Reference where your name is written in R1C1 (Row-1 Column-1 or Cell A1). The Cell reference must be used in this way rather than A1 style.

    Do the following, if not successful.

    But, there are chances that you may end up with nothing showing on the Text Box. In that case you have to see a particular Option is set correctly in the General Tab of Options. . . in Excel Tools Menu.

  10. Click on the Excel icon on the Taskbar to open it. Click on Tools - -> Options. . . and select the General Tab.
  11. Remove the check mark (if it is set) from the option Ignore Other Applications under the Settings Options.
  12. Minimize Excel, Close MS-Access Form and open it again. Now you must see your name from the Excel Cell on the Form. Don't close the Form.
  13. Maximize Excel again and make some change in your Name and Minimize again. The change may not reflect immediately on the Form. You can update the Form value manually without closing and opening the Form again.
  14. While the Access Form is in Normal View, select OLE/DDE Links from Edit Menu.
  15. The Links Dialog Box will open up. Select DDE Links in the Links Group control. The Link's list will appear below. Select the Link and click on the Update Now Command Button. Now the change on the Worksheet will reflect on the Form Value.

There are Option settings in MS-Access also similar to the one we have made in Excel. The general rule is that instead of refreshing the change manually, as we did above, it should happen automatically at a fixed interval as per the Tools - - > Option. . . settings in Microsoft Access, but I didn't see it happen successfully on my machine so far.

Select Tools - -> Options. . . - -> Advanced Tab. Put a check mark in Enable DDE Refresh option, if it is not set. You can see the default Values set for automatic refreshing for Dynamic Data Exchange and ODBC links, how many retries and in case of failures how long Microsoft Access should wait before trying again etc.

Display Value in Combo Box and Option Group

You can display Values from Excel in two more Access Controls, in Combo Boxes and Option Group Control. See the sample image given below.

The Combo Box Method

  1. Minimize Access and Maximize Excel.
  2. Enter few Names of people, books or anything else in Cells A9 to A17.
  3. Enter Value 2 in Cell C1 and minimize Excel Application Window.
  4. Maximize MS-Access and open the Form in Design View.
  5. Disable the Control Wizards Button (top right button, if it is active) on the Tool Box. Select the Combo Box Tool and draw a Combo Box Control on the form in such a way that it looks like a List Box. See the sample image given above.
  6. Display the Property Sheet and write the expression

    in the Control Source Property of the Combo Box.

    You may give a Name to the Range: R9C1:R17C1 and use that Name in the expression replacing the Range Address. To Name the Excel Cell Range, highlight the Range, select Insert - -> Name - - > Define and type the name, say List, in the Names in Workbook control. Replace the third Parameter R9C1:R17C1 with the Range Name List in the DDE Function.

  7. Open the Form in Normal View. The Names from the Excel Range will appear in the Combo Box.

Note:You cannot select any of these values and insert into the Text Box portion of the Combo Box and use it in a data field.

The Option-Group Option

We will try one more example with the Option Group Control.

  1. Turn On the Control Wizard on the Tool Box. Select the Option Group Tool from the Tool Box and Draw an Option Group Control on Form. Refer the example given above.
  2. Type three Labels in the Wizard: Data View, Print Preview and Print, or anything else you prefer, on the control and Click Finish.
  3. Click on the outer frame of the Option Group and display the Property Sheet (View -> Properties).
  4. Write the following expression in the Control Source Property:

The selection of item on the Option Group will be based on the Value given in the Excel Cell C1. Now the value in Cell C1 is 2. The second item on the Option group will now show as selected. Change the Value in Cell C1 to 3 and refresh the Form as explained above, the option will change to 3.

Note: If the Excel Application is not active when you open the Form with the DDE() Function then Access will show the following Error Message:

"You tried to open a form or report that includes a DDE or DDESend function in a calculated control that specifies an OLE server application."

"Do you want to start the application Excel?"

If you respond with Yes then Excel Application will be started with Blank Sheets. You must open the Excel Workbook that provides information for DDE() Function manually to show up the values in Access Form.

Next: Opening Excel Database directly.

  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
  10. Access Live Data in Excel-
  11. Opening Excel Database
  12. Create Excel Word
    File from Access


  1. Pretty nice post. I just stumbled upon your blog and wanted to say that I have really enjoyed browsing your blog posts. In any case I’ll be subscribing to your feed and I hope you write again soon!

  2. Pretty nice post. I just stumbled upon your blog and wanted to say that I have really enjoyed browsing your blog posts. In any case I’ll be subscribing to your feed and I hope you write again soon!

  3. well written blog. Im glad that I could find more info on this. thanks


Comments subject to moderation before publishing.

MS-Access Tips on your Finger-Tip

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


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 Graph Charts Query VBA msaccessQuery Calculation Event List Boxes Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference TreeView Control ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ImageList Control 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 ListView Control Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility 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 Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo 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