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 from 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 to 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 Tool Box.
  6. Draw a rectangle large enough to accommodate the size of the Worksheet you need leaving about quarter of an Inch space on all four sides on 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 OK Command Button.

    An Excel Worksheet will be inserted into the Unbound Object Frame area on the Form. The Excel Worksheet will be in active state.

    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 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 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 Disabled and Locked state in normal view of the Form. These properties we must modify to work with the Worksheet in 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 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 and 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 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.


    Few Issues with the Worksheet needs correction.

    I know you don't feel comfortable to work with this Worksheet because you have only 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 of 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 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 regular Worksheet, like add another Worksheet, write 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 draw backs 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 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 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 Option 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.

Isn't it something different to work with Excel from within MS-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 are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations Class Module msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Event Menus and Toolbars Collection Object Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Form Command Button Data Dictionary Object Emails and Alerts Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export 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 Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...

Labels

Blog Archive

Recent Posts