Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Microsoft Excel Power in MS-Access

Introduction.

There are times that Microsoft Access Application Users ask for Report Data exported into Excel so that they can work on it and do their own customized analysis. In this case, we can create a Macro with the Macro Command OutputTo or Visual Basic Docmd to export the Data from a Table or Query and open the Data in Excel automatically.

The sample Macro Command and Parameter setting will look like the image given below:

The same action in Visual Basic Code is given below:

Function xPort2XL()
    DoCmd.OutputTo acTable, "Products", "MicrosoftExcelBiff8(*.xls)", "C:\My Documents\Products.xls", True, "", 0
End Function

Both can be run from a Command Button Click on the Main Switch Board.

But, if the User is able to do whatever he/she does in Microsoft Excel, like writing expressions for Calculations, Formatting, Chart Preparations, Printing, and so on from within Microsoft Access itself that will be a different experience altogether, right? They can even save a copy of whatever they do from within Access as an independent Excel Workbook outside MS-Access if needed.

The Design Task.

Only a few things are involved to implement it in your Projects.

  1. Open a new Form in Design View in your Database.
  2. Expand the Detail Section of the Form large enough for the size of the Worksheet you would like to display on the Form.
  3. Display the ToolBox (View - - > Toolbars - - > Control ToolBox), if it is not visible.
  4. Select the Control Wizards Tool Button (the button with a magic wand icon) to activate it.
  5. Select the Unbound Object Frame Tool from the ToolBox.
  6. Draw a rectangle large enough to accommodate the size of the Worksheet you need to leave about a quarter of an inch space on all four sides of the Form. A sample image is given below for reference:

    This action will display a list of Objects that can be inserted into the Unbound Object Frame.

  7. See that the Create New Radio Button is selected and Select Microsoft Excel Worksheet from the displayed Object Type List and click the OK Command Button.

    An Excel Worksheet will be inserted into the Unbound Object Frame area on the Form. 

    If you look at the Menus and Toolbars above, you can see that all of them are changed into Microsoft Excel Menus and Toolbars now. You can see the Formula Bar and the active Cell Address to the left of the Formula Bar. You can type some expression in the Formula Bar to save it in the active cell if you want to try now.

    But, we are now in the Design View of the Form. First, we must save the Form with an appropriate name and open the Form in Normal View for use.

    Unlocking Worksheet for Normal Use

    Before saving the Form we must change a few Property Values of the Unbound Object Frame (the housing frame of the Worksheet Object) that we have drawn initially. The Unbound Object Frame will be in a Disabled and Locked state in a normal view of the Form. These properties we must modify to work with the Worksheet in the normal view of the Form.

  8. Click outside the Worksheet on the Detail Section of the Form to deactivate the Worksheet.

    The Unbound Object Frame with the Worksheet will be in a selected state (if it is not, then click on it again to select it) showing the sizing controls on all four sides and corners. Now the Menus and Toolbars changed back to MS-Access.

  9. Select View - - >Properties to display the Property Sheet of the Unbound Object Frame and change the following Property Values as shown below:
    • Enabled = Yes
    • Locked = No
  10. Close the Property Sheet.

    NB: You may modify the look of your Form with a Title on the Header Section of the Form and change the Property settings of the Form to remove the Record Selector, Navigation Buttons,  Dividing Lines, etc. Add a Command Button at the Form Footer and attach a Macro to Close the Form.

  11. Select Close from File Menu and save the Form with the name XlWorkBook.

    Preparing Worksheet for Normal Operations.

  12. Open the XlWorkBook Form in Normal View.

    When you open the Form the Unbound Object Frame will be in the selected state showing the Excel Worksheet Grid and the sizing controls, as we have seen them earlier in Design View.

  13. Right-Click somewhere on the Control to display the Shortcut Menu.
  14. Select Edit from the Worksheet Object Option displayed. You can Double-Click on the Unbound Object Frame to get the same result.

    The Worksheet becomes active now, Menus and Toolbars now changed to Excel and the Formula-Bar is showing up above the Form.

    A Few Issues with the Worksheet which need correction.

    I know you don't feel comfortable working with this Worksheet because you have only a limited area of the worksheet available, or the Scrollbars are not showing up properly to move to a wider area of the Worksheet or the Worksheet Tab is not visible, etc. But, these are all temporary issues and can be solved in no time.

    Before going on to that how do we bring the data from Access Table or Query into this Worksheet. You cannot link a Table of this Database to the Worksheet and you cannot import the contents of a Table or Query into this worksheet either. There is an option Import Data in Data Menu for Excel to get external data through ODBC Connection, but this will not work for bringing data from the same database or cannot be recommended for Users.

    There is only one easy way that I could find, that is open the Table or Query in Datasheet View, Copy the required portion (Rows or Columns) or whole contents to the Clipboard, and Paste it into the Worksheet.

  15. Minimize the xlWorkBook Form and the Access Menu is back.
  16. Open a Table or Query in Datasheet View (this can be done through a macro for Users).
  17. Click on the left top corner of Rows and Columns to highlight the entire Table or Query contents (or select a few rows or Columns).
  18. Select Copy from Edit Menu.
  19. Minimize the Datasheet View and Maximize the xlWorkBook Form.
  20. Click on the left top corner of the Worksheet area where you would like to paste the data into.
  21. Select Paste from Edit Menu. The copied contents will be pasted on to the Worksheet.

    You can close and open the Form again and the data on the Form will have all the changes that you have made before closing it.

    NB: The normal Excel Paste Special. . . Menu Options are not available here, but you can get it when the Excel Workbook is open in a different mode.

    You can work with the data as you will do in a worksheet, to add another Worksheet, write a formula, do calculations, create Charts, and so on. There are certain things that you cannot do here (in this state of the Form) like mark an area of the worksheet as Print Area or do Page Setup changes or Print Preview of the Worksheet. That doesn't mean that we cannot do these things at all.

    Worksheet EDIT or OPEN Mode

    These drawbacks can be solved by opening the Worksheet in a different Mode.

  22. Click on the Form's detail Section outside the Unbound Object Frame to deactivate the Worksheet.
  23. Right-Click on the Unbound Object Frame and select Open (earlier we have selected Edit) from the Worksheet Object Menu.

The Worksheet will be opened in a normal Microsoft Excel Application Window and you can do whatever analysis you want to do here. You may add more Worksheets in the workbook, write the formula, create Charts, and so on.

Here, you can mark a selected area of the Worksheet as Print Area, change Page Setup, Print Preview, or Print the Worksheet. The normal Paste Special menu options are available here.

You can update these changes on the MS-Access Form by selecting the Update option on the File Menu. You don't have to search for the Excel File on disk later. You can even save a copy of this Workbook as a regular Workbook by selecting the Options Save Copy As from File Menu if needed.

When your work is complete in Excel, select the option Close & Return to xlWorkBook: Form to come back to MS-Access Form, saving whatever work you have done with it.

Isn't it something different to work with Excel from within MS-Access?

  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 Files from Access
Share:

3 comments:

  1. Great site. A lot of useful information here. I’m sending it to some friends!

    ReplyDelete
  2. Heh I am actually the first comment to this incredible writing?!

    ReplyDelete
  3. If I had a penny for every time I came to www.msaccesstips.com! Superb post.

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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