Connecting Access to Excel - MS Access

In this section, you will be looking at working with some of the key objects in Excel. Figure shows a simplified object model of Excel.

A simplified Excel object model.

A simplified Excel object model.

Excel supports a large number of different object types, some of the key object types are as follows:

  • Application Used to manipulate the Excel environment
  • Workbook A file that contains multiple Worksheets
  • Worksheet A spreadsheet
  • Range A region within Excel
  • Cell A cell
  • PivotTable A pivot table with a possible connection to external data such as Access tables/queries

As described earlier in this chapter, you need to add a reference to the Excel Object Library before developing the code described in this section. The sample database file to use in this section is ExcelAnalysis.accdb.

Writing Data to a Spreadsheet

In this section, you will look at situations in which you need to either directly read or write information into a spreadsheet. This is particularly useful when receiving or providing information to other people in or outside your organization who will not have any access to your database. The demonstration form frmExcelOperations shown in Figure is used to read and write data to Excel.

Opening the frmExcelOperations form for reading and writing data to Excel.

Opening the frmExcelOperations form for reading and writing data to Excel.

The first example demonstrates how to transfer information from database tables to a placeholder spreadsheet. To facilitate re-using the methods developed here and in other parts of an application, we have created a table for holding a list of placeholder template documents, as shown in Figure.

A table designed to support reading and writing of data from different spreadsheets.

A table designed to support reading and writing of data from different spreadsheets.

Each placeholder spreadsheet is referred to a template in the code. Notice that each placeholder template has entries in a related table , which is used to tie up the reference in a cell to a field in the database (this technique can be easily extended to handle multiple spreadsheets in a workbook).

Each spreadsheet uses a table of instructions for reading and writing data. This links the database field names to the cell positions in Excel.

Each spreadsheet uses a table of instructions for reading and writing data

Referring back to Figure , use the Write Product Specifications To Excel button to display a form, from which you select the specification template and database products, as shown in Figure.

Use the sample form frmExcelProductSpecWrite to select a product and specification document template, and then generate a document in Excel.

Use the sample form frmExcelProductSpecWrite to select a product and specification document template, and then generate a document in Excel.

The code behind the Generate A Product Specification button calls a routine in a library to generate the specification, passing the identifiers for the product and the specification, as shown in the following:

modTesting_WriteProductSpec Me.cboTemplateId, Me.cboProductId

As demonstrated in the following code sample, the first part of the processing routine looks up some key information based on the document and product identifiers, and then prepares to open the spreadsheet in Excel:

Opening Excel

All the Excel operations are contained within a single module called modExcel, at the top of the module, variables are defined to refer to the Excel objects, as shown in the code that follows:

The code to open Excel has a parameter that allows it to either create a new Excel application or use an existing instance of Excel. If the parameter is set to use an existing instance of Excel and one does not exist, it will create a new instance, as shown here:

The second routine loads the Excel Workbook:

The third routine gets a reference to the active worksheet. You can modify this routine to get a reference to a specifically-named worksheet (an example of which is shown in modExcel_SyntaxForWorkSheets):

Writing the Data

Returning now to your main outer code loop, you can examine the additional Do While Not crst.EOF loop that writes all the data entries into the worksheet, as shown here:

This code uses the Recordset crst to find the name of the database field, and then uses the expression CStr(rst(crst!databaseFieldName)) to extract the value from the Recordset. You can set a breakpoint in the code to display the key values in the variables and expressions in the Watches window, as shown in Figure.

Using the Watches window to examine the key variables used to write data to the spreadsheet.

Using the Watches window to examine the key variables used to write data to the spreadsheet.

Figure shows that the cell reference is C5 and the database field name of Product Code extracts the value NWTB-1. The WriteCell procedure is as follows:

Because you are referencing the cell by using a column number and not a column letter, we have added a function to map the column letter to a numeric value, as shown in the following:

Add a breakpoint to the execution of the code so that you can switch to display the Excel spreadsheet cells being written, as shown in Figure.

Monitoring activity in Excel as data is written to the spreadsheet.

Monitoring activity in Excel as data is written to the spreadsheet.

The last portion of your code calls a routine to save the spreadsheet:

Using Excel to drive an Access application

In the sample spreadsheet ExcelOpeningAccess.xlsm, we have written sample code that allows Excel to directly manipulate objects in an Access application by using the following VBA code:

References have been added to the Microsoft Access 14.0 Object Library and Microsoft Office 14.0 Access database engine Object Library. This then allows all the types of code that you have been looking at in this book to be executed from inside Excel.

Reading Data from a Spreadsheet

The process of reading data from an Excel spreadsheet is very similar to the previously described operations for writing data. In the sample code that follows, we have shown part of the code from the module mod Testing _Read ProductSpec, which performs the read operation.

Rather than read the data directly into the table, we have created a temporary table into which you will read the data. You can then use this to update the main tables in the database:

The following code illustrates the ReadCell processing routine is the reverse of the WriteCell operation:

Reporting with Excel Linked to Access

In this example, you will look at a very simple and effective way to use Excel for management reporting. There are two compelling reasons for using Excel in this manner (in particular when working with financials). First, the people consuming the data might prefer to use Excel for reporting because it also enables them to tie in their own data from other Excel applications. Second, Excel has many wonderful reporting features that can be difficult to reproduce in Access.

When you link Excel to queries in Access, there are restrictions regarding the use of Accessspecific functions, such as the Nz function or custom VBA functions in a query; you need to avoid these. You also cannot use any queries that contain references to controls on Access forms.

To get around this restriction of having an Access query that directly references controls on a form, one simple approach to reporting is to create a local table that contains a user’s reporting selections and include this table to filter data in any queries. This can then be linked directly to Excel.

To use this approach, the simplest solution is to give each user a copy of an Access front-end application with a local table containing these choices for reporting (or if the application were shared, only one user at a time could use this reporting feature). If you decide that rather than creating this for yourself that you prefer to use our example Excel spreadsheet, ExcelReporting.xlsx, you will need to edit the existing connections in the spreadsheet if your database is on a different path to the one we have used.

On the ribbon, on the Data tab, click the Connections group, and then click the Connections icon. You will see four items; for the two items, ExcelAnalysis(1), go to Properties | Connection String, and then edit the path to ExcelAnalysis.accdb. For the two items, Query from ExcelAnalysisDatabase( 1), make the same change, but notice that you make the change twice in each connection string.

You start by creating a table that will have only one row that is set to the user’s reporting choices, as shown in Figure. Note that this is a single-user solution.

The table holding parameter choices for a user. The first field is the primary key, although this table only ever holds one record.

The table holding parameter choices for a user. The first field is the primary key, although this table only ever holds one record.

Use the form called frmExcelReporting, which contains a combo box that you use to select a reporting period. The combo box is bound to the ReportingMonth column in tblReportingParameters.

The sample form frmExcelReporting for opening Excel and refreshing links to Access.

The sample form frmExcelReporting for opening Excel and refreshing links to Access.

You start by constructing a query in Access that will use the user’s reporting choices to restrict the data. This uses a cross-join or Cartesian product to restrict the records to the user’s choice of parameters, the query qryProduct Sales is shown in Figure.

A query displaying product sales for a selected month.

A query displaying product sales for a selected month.

The actual products sold each month can change and you want to produce a query that includes all the products. To do this, you create another query that brings the main product and sales information together. This query is called qryProductSalesAllProducts , and it uses an outer join to bring together all the product records.

Combining the sales and product information together.

Combining the sales and product information together.

There is one more piece of information that you want to include: the reporting month. To do this, you create a top-level query that uses a Cartesian product to project the month information. This uses the qryProduct SalesForExcel query, as shown in Figure.

A Top-level query displaying month information.

A Top-level query displaying month information

With all the queries saved and closed, you can now open Excel and create a link to the query with the resulting data. In a new spreadsheet, on the ribbon, on the Data tab, click the Access icon to create the link, as shown in Figure.

Getting external data from Access.

Getting external data from Access.

Then, browse until you locate the ExcelAnalysis.accdb database, as shown in Figure.

Use the file dialog to browse and locate your Access database file.

Use the file dialog to browse and locate your Access database file.

Setting Data Link advanced properties

If you still have Access open and click the Test Connection on the Connection tab, a message displays indicating that database is already open. To resolve this, in the Data Link Properties window, click the Advanced tab. You will see that the Access Permissions are set to Share Deny Write.

Clear this option, and then select the Read check box. Return to the Connection tab and test the connection; the test will now succeed. Figure shows the Data Link connection and the Access Permissions for the connection.

Click the Advanced tab on the Data Link Properties dialog box to set the Access Permissions properties.

Click the Advanced tab on the Data Link Properties dialog box to set the Access Permissions properties.

After you successfully test the connection (on the Connection tab) and click the OK button, Excel displays a list of tables and queries in Access . Notice that this list does not show queries that Excel cannot understand; for example, any query using a custom VBA function. The query qry Product Sales AllProducts Using IIF is displayed, but the query qry Product Sales AllProducts Using NZ is not displayed because it contains the Nz function in an expression.

Selecting from available tables and queries. Not all queries will be shown, depending on their content.

Selecting from available tables and queries. Not all queries will be shown, depending on their content.

The Import Data dialog box opens and prompts you to select whether you want the data displayed in a Table, PivotTable Report, or a PivotChart And PivotTable Report, as shown in Figure.

Although this is the Import Data dialog box, you are constructing a dynamic link to the query in the Access Database.

Although this is the Import Data dialog box, you are constructing a dynamic link to the query in the Access Database.

After pressing OK to create the linked table of data, place your cursor inside the area of the table, right-click to display the shortcut menu, and then click the Refresh option, which refreshes the data in the table if any changes were made to the data in Access. Figure shows the shortcut menu option to manage the external data for the table.

The External Data Properties can be used to manage settings for the table.

The External Data Properties can be used to manage settings for the table.

Figure presents the External Data Properties dialog box. Click the button adjacent to the Connection Name text box to manage the connection details and make changes to the name of the query, table, or SQL used for the external data.

Click the button next to the Connection Name box to open a window, in which you can change connection properties.

Click the button next to the Connection Name box to open a window, in which you can change connection properties.

Planning for linking to external data

A useful approach to managing external data links is to allocate one spreadsheet page (for example, called RawData) and place all of your linked tables on this single page. Other spreadsheet pages can then link to this data page. Depending on how you construct your queries, it can be useful to have a database field to control the ordering of the data being linked from Excel so that when new data rows are added, they do not disrupt any dependencies on row ordering that you build into the spreadsheet formulae.

It can also be useful to change the options shown in Figure and select the option Overwrite Existing Cells With New Data, Clear Unused Cells so that additional data does not move other linked tables of data. Planning your layouts to allow for growth in tables of data is important, and placing multiple tables to the right of each other and not below each other can help with allowing for the growth of data.

After selecting the query, you are offered a number of options for adding the data as a linked table or pivot table. For this example, choose to display the data as a table. On the Data tab, click the Connections group, and then click the Connections button to display the Workbook Connections dialog box. Here, you can manage all the connections for the Workbook, as shown in Figure.

Use the Workbook Connections dialog box to manage all the connections in the Workbook.

Use the Workbook Connections dialog box to manage all the connections in the Workbook.

Save and close the spreadsheet, return to the Access application where you will construct some VBA code to allow the user to make selections, and then open and refresh the Excel spreadsheet. The code behind the button that refreshes the Excel spreadsheet is as follows:

The details behind the open procedures have been discussed in earlier sections. The procedure that refreshes the Excel workbook can execute a RefreshAll to refresh all items, or it can selectively refresh the connections (and associated data tables) and any pivots:

Using MS Query and Data Sources

In the previous section, you linked Excel to Access by using connections; this is not the only method that can be used when connecting Excel to Access. If you are working with older linked spreadsheets, they are likely to use the second technique, which is described in this section. On the Data tab, click the Get External Data group, and then click From Other Sources | From Microsoft Query. This displays the Choose Data Source dialog box shown in Figure.

Select the Use The Query Wizard To Create/Edit Queries check box if you want the wizard to provide assistance while setting up or editing queries.

Select the Use The Query Wizard To Create/Edit Queries check box if you want the wizard to provide assistance while setting up or editing queries.

If you do not already have a data source, select <New Data Source>, and then click OK. The Create New Data Source dialog box opens, as shown in Figure.

Enter a name for your data source, and then select the Microsoft Access Driver.

Enter a name for your data source, and then select the Microsoft Access Driver.

Use the Connect button to browse and select your Access Database. (You might find that you need to close the Access Database to avoid messages indicating that the database is already in use.) You can then select your new database connection, which will be displayed in the Choose Data Source dialog.

You might notice that unlike the connection wizard described in the previous section, the Query Wizard will display queries that you are not allowed to use, and if you try to connect to an illegal query, such as qryProductSales AllProductsusingNZ, you will see the error message displayed in Figure.

The error message that pops up if you attempt to use a query that contains an unsupported function.

The error message that pops up if you attempt to use a query that contains an unsupported function.

In the sample spreadsheet called ExcelReporting.xlsx, we have added both a Table and Pivot Table to the spreadsheet called MSQueryData.

QueryTables and ListObjects

The Excel worksheet contains two collections called PivotTables and QueryTables. With older versions of Office, when you linked a table of data, this created an entry in the QueryTables collection, but with Office 2010, it does not create an entry in the QueryTables collection for the spreadsheet object. Instead, connections are created as described in the previous section and the QueryTable can be accessed through the ListObjects collection in the SpreadSheet object In the spreadsheet ExcelReporting.xlsm, we have included some example module code for listing Connection and QueryTable information, as shown below:


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics