Display Excel Value Directly on Form

Introduction - MS Access 2003

In the earlier post, Opening dBase Files Directly, we learned how to open and work with dBase files in MS Access using VBA. However, there are several alternative methods—other than linking—to handle dBase files within Access. We’ll explore those options in detail later.

This post, however, was originally meant to demonstrate how to open an Excel-based data table directly and work with it in VBA. We’ll cover that in the next section. But before diving into that, let’s take a short detour to try out some simple yet fascinating Excel tricks.

I say “simple” because you won’t need to wrestle with any complex VBA code this time. And I know you’ll appreciate that little break from my so-called spaghetti code!

As the title suggests, we’re going to explore how to display values from an Excel worksheet directly on Microsoft Access forms—no heavy coding required.

The Demo Run Preparation

1. Open Microsoft Excel.

2. Enter your name in cell A1 on Sheet1, then minimize Excel—but don’t close it. You may save the workbook with a suitable name. Keep in mind that if the AutoSave feature is turned on, Excel may prompt you to save your changes.

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 Toolbox 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:

   =DDE("Excel","Sheet1","R1C1")

8. Save the Form with the name of your choice.

9. Open the Form in Normal View. You will see your Name written on an Excel Cell appearing in the Text Box on the Form.

   The DDE() Function stands for Dynamic Data Exchange. The first two Parameters are the Excel Application and the 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). Cell reference must be used in this way rather than the A1 style.

   Do the following, if not successful.

   But, you may end up with nothing showing in the TextBox. In that case, you have to see if a particular Option is set correctly on the General Tab of Options in the 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 Ignore Other Applications option in the Settings Options.

12. Minimize Excel, close the MS Access Form, and open it again. Now you must see your name in the Excel Cell on the Form. Don't close the Form.

13. Maximize Excel, make some changes to the Name, and minimize again. The change may not reflect immediately on the Form. You can update the Form value manually without the closing and opening steps.

14. While the Access Form is in Normal View, select OLE/DDE Links from the 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 the Form Value.

Microsoft Access also includes option settings similar to those in Excel. Generally, instead of manually refreshing changes—as we did earlier—the updates should occur automatically at fixed intervals according to the settings under Tools → Options in Microsoft Access. However, in my experience, this feature hasn’t worked reliably on my system so far.

Select Tools → Options → Advanced tab. Ensure that the Enable DDE Refresh option is checked if it is not already enabled. In this section, you can also view the default settings for automatic refreshing of Dynamic Data Exchange (DDE) and ODBC links — including the number of retry attempts and the wait time before Microsoft Access tries again in case of a failure.

Display Value in Combo Box and Option Group

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

The Combo Box Method

1. Minimize Access and Maximize Excel.

2. Enter a few Names of people, books, or anything else in Cells A9 to A17.

3. Enter Value 2 in Cell C1 and minimize the Excel Application Window.

4. Maximize MS-Access and open the Form in Design View.

5. Disable the Control Wizards Button on the Toolbox. Select the Combo Box Tool and draw a Combo Box Control on the form in a way that it looks like a List Box. See the sample image given above.

6. Display the Property Sheet and write the expression

   =DDE("Excel","Sheet1","R9C1:R17C1")

   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 them 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 Options Group Control.

1. Turn On the Control Wizard in the Toolbox. Select the Option Group Tool from the Toolbox and Draw an Option Group Control on the Form. Refer to 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 Options Group and display the Property Sheet (View -> Properties).

4. Write the following expression in the Control Source Property:

=DDE("Excel","Sheet1","R1C3"

The selection of items on the Option Group will be based on the value given in Excel Cell C1. Now the value in Cell C1 is 2. The second item in the Options 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 in Excel?"

If you respond with Yes, then the Excel Application will be started with Blank Sheets. You must open the Excel Workbook that provides information for the DDE() function manually, and display the values in the Access Form.

Next: Opening an 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 Access 2007
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 an Excel Database Directly
12. Create an Excel Word File from Access