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:

Click to Enlarge

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.

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:

    

   
   

   Click to Enlarge

    

   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.

    

   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.

 

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.

     

    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.

     

    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.