Microsoft Excel Power in MS-Access ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Sometimes, Microsoft Access users need report data exported to Excel for further analysis and custom work. In such cases, you can create a macro using the OutputTo command or the VBA DoCmd.OutputTo method to export data from a Table or Query and automatically open it in Excel.

The sample Macro command and parameter settings are illustrated in the image below:

Right-click to open Large Image in New Tab/Window.

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.

Imagine if users could perform all the tasks they normally do in Microsoft Excel—such as creating formulas, formatting cells, preparing charts, printing reports, and more—directly from within Microsoft Access. That would provide a completely different and seamless experience. They could even save a copy of their work as an independent Excel workbook outside Access whenever needed.

The Design Task.

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

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

Right-click to open Large Image in New Tab/Window.
This action will display a list of Objects that can be inserted into the Unbound Object Frame.
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 have been 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. 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 then 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 were drawn initially. The Unbound Object Frame will be in a Disabled and Locked state in the normal view of the Form. We must modify these properties to work with the Worksheet in the normal view of the Form.
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, click on it again to select it), showing the sizing controls on all four sides and corners. Now the Menus and Toolbars have changed back to MS-Access.
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
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.
Select Close from the File Menu and save the Form with the name XlWorkBook.

Preparing Worksheet for Normal Operations.
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 saw earlier in Design View.
Right-click somewhere on the Control to display the Shortcut Menu.
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 are now changed to Excel, and the Formula-Bar is showing up above the Form.
A Few Issues with the Worksheet need correction.

I understand that working with this worksheet may feel uncomfortable because only a limited area is visible, the scrollbars may not appear correctly, or the worksheet tabs are hidden. Don’t worry—these are all temporary issues that can be fixed quickly.
Before addressing that, you might wonder how to bring data from an Access table or query into this worksheet. You cannot link a table from the same database directly, nor can you use Excel’s Import Data option via ODBC for this purpose—it’s either ineffective or not recommended for users.

The simplest solution is to open the table or query in Datasheet View, select the rows, columns, or the entire dataset you need, copy it to the clipboard, and paste it directly into the worksheet.
Minimize the xlWorkBook Form, and the Access Menu is back.
Open a Table or Query in Datasheet View (this can be done through a macro for Users).
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).
Select Copy from the Edit Menu.
Minimize the Datasheet View and Maximize the xlWorkBook Form.
Click on the top left corner of the Worksheet area where you would like to paste the data.
Select Paste from the Edit Menu. The copied contents will be pasted onto the Worksheet.

You can close and reopen the Form, and all the changes you made earlier will be preserved and displayed.
NB: The normal Excel Paste Special... Menu Options are not available here, but you can get them 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.
Click on the Form's detail Section outside the Unbound Object Frame to deactivate the Worksheet.
Right-click on the Unbound Object Frame and select Open (earlier we had selected Edit) from the Worksheet Object Menu.

The Worksheet will open in a standard Microsoft Excel window, allowing you to perform any special analysis. You can add additional worksheets, write formulas, create charts, and more.

You can select specific areas of the worksheet as a Print Area, adjust Page Setup, use Print Preview, or print the worksheet. All standard Paste Special options are available for use.

Any changes you make can be updated directly on the MS-Access Form by selecting the Update option from the File menu. There’s no need to locate the Excel file on your disk. If desired, you can also save a copy of the workbook as a regular Excel file using Save Copy As from the File menu.

Once your work is complete, select Close & Return to xlWorkBook: Form to return to the MS-Access Form, ensuring that all your changes are saved.

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