Integrating Excel and Access

Graphing Variables in a Model

The next method is very useful if you build a model and want to see the results against various criteria. Let's assume you have multiple values in a table, and you want to input them into a model and store the result. This would normally be a very time-consuming process. However, by leveraging VBA, you can do it rather easily.

For this example, we use a table with three fields: sales, full-time equivalent (FTE), and contribution. The table uses an Excel workbook that uses the sales and FTE inputs to return a contribution profit number. This method is obviously more useful in a more complex model, but this simple example will demonstrate how it works. Note that in this example, the code opens a workbook that already exists; your path and filename will be different. Example 6-7 also uses a worksheet called "Model" in the workbook.

Example 6-7. Profit Model example

Public Sub ProfitModel() Dim xlapp As Excel.Application Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim adors As ADODB.Recordset Set xlapp = New Excel.Application xlapp.Visible = True Set xlwb = xlapp.Workbooks.Open("C:\BookInformation\Chapter6\ProfitModel.xls") Set xlws = xlwb.Sheets("Model") Set adors = New ADODB.Recordset adors.Open "tbl_ProfitModel", CurrentProject.Connection, _ adOpenDynamic, adLockOptimistic adors.MoveFirst While Not adors.EOF xlws.Range("B10").Value = adors.Fields("Sales").Value xlws.Range("B6").Value = adors.Fields("FTE").Value adors.Fields("Contribution").Value ...