Peter Vogel
By storing Excel data and formulas in an Access table and integrating Access queries into Excel formulas, you can give your users the best of two powerful tools.
I look for techniques that allow users to take control of their applications. However, my company's new order entry system presented a major challenge to this philosophy. The system was going to have to support four divisions (two in another country), each with their own special needs. Trying to keep the system in synch with users' needs as the company (actually, each division is like a different company) changed was going to be impossible. And the new system actually had to make users' lives easier: the system had to allow users to create an order faster than before, without worrying about whether the prices in the system were up to date. Special charges or credits were a particular problem: they varied dramatically from one order to the next because of special discounts, special warranties, local taxes, overseas shipping and insurance, special engineering charges, and on and on. Fortunately, somebody suggested that all marketing needed was an Excel spreadsheet where they could type in whatever special terms they wanted, and a solution started to appear.
Excel made sense because users could create their own charges as they needed them. They could even enter formulas to calculate charges that were dependent on other charges. Heck, if they needed to, they could add a line in the middle of the charge sheet (try that in Access)—not to mention automatically recalculating the total price after every change. However, if management was going to be able to do any sort of sales analysis, the data was going to have to be kept in a database such as Access.
Of course, I had very little experience with Excel, had never used Excel with Access, and hadn't done much work with OLE, so I had much to learn. In this article I'll share what I learned so you can apply these solutions to your own applications.
After assessing all the issues, I plunged in. To make the two packages look like a seamless whole, I decided to embed an Excel spreadsheet in an Access form. This would allow users to edit the spreadsheet in place or bring Excel up in full screen mode when desired. Embedding also gave each user his or her own copy of the spreadsheet and kept the number of files to a minimum. You can embed an Excel spreadsheet into an Access form using these steps:
Voilˆ! You have an Excel spreadsheet in an Access form. But you also need to get the data on that form into the database.
Figure 1. Embedding the Excel spreadsheet into the Access form.
I needed to define a table to store the charges from Excel. Every set of charges was unique to an order, so the key for the table was the order number. For any particular charge the user has to enter a description and either a formula or a number, so I provided fields for both. Here's the schema of the table, called tblOrderCharges:
| Column name | Type | Precision (size) |
| txtOrderNumber | Text | 7 |
| txtChargeDescription | Text | 25 |
| curChargeAmount | Currency | |
| txtChargeFormula | Text | 50 |
| intChargeRow | Integer |
I had to write some code to move data from the spreadsheet to this Access table, though (as it turned out) I didn't have to write much code. To access the Excel spreadsheet embedded in the form, I needed to reference it as an object variable using the object property of the OLE control containing it. While simple to do, this turned out to be a mistake, as I'll discuss later. I then activated the object, which would load Excel, by setting the Action property of the object to the constant OLE_ACTIVATE (I copied the definition of this constant from the CONSTANT.TXT file that ships with Access). Once that was done, I could loop through all the rows on the spreadsheet, move each cell to the appropriate field, and save the data. When I hit a blank row, I stopped (see the LoadTable subroutine in Listing 1).
The code to put the data back on the spreadsheet was very similar. But when I redisplayed the charges they came back in alphabetical order by description. Thus, I added a charge number field to the table and stored the Excel row number there, making sure the query that retrieves the charges is sorted by this field.
I also discovered that every cell in Excel has a formula. If you put a value in a cell, the cell acquires a formula that consists of that number stored as a string. Since I needed to store formulas and amounts separately, I had to be able to spot cells with "real" formulas. I ended up using the Val() function to compare the value of the cell with the value of its formula. Where they were different, I stored the formula for the cell in the txtChargeFormula field of the database. I stored the value of the cell in the curChargeAmount field so this data could be analyzed using Access queries but, when loading the charges back to the spreadsheet, if the charge contained a formula then I loaded only the formula to the cell.
Even with Excel's help, it turned out that creating a whole order's worth of charges in this spreadsheet was a lot slower than using paper, pencil, and a calculator. And there was no way of analyzing charges across orders—how could anyone tell that "Dealer Discount" on order 17890 was the same kind of charge as "Discount" on order 17891? Normally I would create a table of valid charges and slap a list box on the form but, as I'll note later, putting smarts in the spreadsheet turned out to be a bad idea.
The solution was to create standard charges that users can load when they begin a new order. Besides giving users a leg up in creating the charges, this gives you some control over the kind of entries that are available to users (though users are able to add, delete, and modify charges once they are displayed). The table design for the standard charges is identical to the format of the order charges table¾ except that the product line and marketing area are used as the key instead of the order number. When the user brings up the charges form, the form first checks for charges in the order charges table using the order number. If none are found, the form pulls the records for the product line and market area from the table.
Some entries in the standard charges table are very straightforward. For orders in the domestic market, there's a sales tax of 7 percent, so that charge has a curChargeAmount of .07. For charges entered by the user when the order is created, both the Charge Formula and the Charge Amount fields are set to Null.
Creating standard charges that use a formula was more awkward. For instance, the sales tax amount equals the price times the sales tax rate. What would the user put in the formula field for this charge record? Entering actual cell names (C1 * C2) made a lot of assumptions about how the charges would be loaded to the embedded spreadsheet. Also, Excel formulas are adjusted automatically when rows are removed or added but this isn't the case in Access.
I solved this problem by adding a Charge Id field to both tables. These Charge Ids can be used in formulas just like Excel cell names. For example, Table 1 shows some sample charge records.
Table 1. Using Charge IDs in charge records.
| txtChargeId | txtChargeDescription | curChargeAmount | txtChargeFormula |
| Price | Price | Null | Null |
| TaxRate | Sales tax rate | 7 | Null |
| Stax | Sales tax amount | Null | Price*TaxRate |
Now I just needed to name the cells in the spreadsheet with their Charge Ids to make the formulas work in Excel:
mobj.range("C" & Cstr(intRow)).name = _
recCharges("txtChargeId")
Except it didn't work—I got an error message every time I tried to assign a name.
I eventually realized that the problem related to the way Excel assigns names. The first time a name is assigned in Excel, it's a workbook level name. Unfortunately, what I had embedded was an Excel worksheet—one level farther down in the hierarchy. To fix this, I had to alter the code that set the object variable used in the program. Instead of simply setting the object property of the OLE container, I had to set the variable to Excel itself and then to a specific sheet in Excel:
Set mobj = GetObject(,"Excel.SpreadSheet.5") Set mobj = mobj.Workbooks(1).Worksheets(1)
Now, unfortunately, when I opened the form Excel would start up in a new window and take over the screen. This isn't necessarily a bad thing, but one of my goals was to make the two packages appear seamless. After much experimentation I discovered that if I opened the form in design mode and then switched to display mode, everything worked the way I wanted it to. This resulted in the only two lines of code in the application that I am ashamed of:
DoCmd OpenForm "frmOrderCharges",A_DESIGN DoCmd OpenForm "frmOrderCharges",A_NORMAL
I have to believe that this workaround is simply due to my ignorance. I suspect a better understanding of the OLE control's Verb property may solve the problem.
Next, I added a column to the spreadsheet so that users can assign Charge Ids to charges they create while working on an order, and I altered the save routine to save these Charge Ids. This was all great, but the spreadsheet had no data about the order the user was working on. The users were going to have to calculate the initial dollar amounts on which the formulas would be based. This was too bad, because now that the complicated stuff was taken care of, each individual amount could be calculated with a single query.
I decided to extend the application by giving it the ability to run Access queries. The process was simple: if the txtChargeFormula field contained a query name, run the query and put the result in the cell. I established two conventions:
Because Access is the company's user report tool, users can develop most of the queries and add them to the standard charge tables themselves.
But most of the time the queries will require parameters: the order number, the model number, and so on. While I couldn't handle every situation, for most cases the query needs information from the order header form. Since a form can be treated as a collection (with its various controls as members of that collection), I established a third convention for the queries:
For other situations, I would eventually provide the code to set the parameter but until then the users would have to enter the information when the query was run.
The code to do this is relatively straightforward, though it looks complicated because of all the nested If statements (see Listing 2). When a query is found in the txtChargeFormula field, its Parameters collection is checked. If there are parameters, the code sets the parameter equal to the value of the control with the same name on the OrderHeader form. If that fails, the code uses a Select Case to handle known parameters not on the order header form. If no match exists, the code jumps to the Else clause that displays an InputBox that requests the unknown parameter.
The first time I used this facility and then saved the charges, I lost all the references to the queries. When the program loaded the charges, it had (as intended) replaced the query name with the value of the query. When the charge was saved, there was no longer any reference to the query in the spreadsheet. To get around this, I stored the query name in another spreadsheet column as I loaded the charges and then hid that column. I altered the save routine to check for a value in that column and store it in the txtChargeFormula field when it contained anything.
As soon as I brought up the screen I realized that one important item was missing—totals. So I extended the application again by adding two new keywords that can be put in the txtChargeFormula field: SubTotal and TotalSubs. As charges are loaded into the spreadsheet, the program checks for these keywords. If they're found, the code adds a total line to the spreadsheet and generates a total formula.
The SubTotal keyword was the most complicated. It triggers a routine in the spreadsheet to create a named range that runs from the last subtotal to the current line. Then the routine assigns the cell a formula of Sum(rangename) and adds rangename to an array of subtotal names. The TotalSubs keyword generates a formula that sums the range names in this array (this routine appears in Listing 3). Like the query names, these keywords are stored in the hidden column on the spreadsheet.
I also modified the form to allow users to use these extensions when working with an order. When the users were creating charges, they would want to type the query names SubTotal or TotalSubs into the spreadsheet just as they did when they created charges in the standard charges table. Excel, of course, would convert these to labels and the user wouldn't get the results they expected. I added a Fix Charges button to the form to run a routine that sweeps through the spreadsheet, converts these extensions to valid entries, runs the queries, and executes the Sum functions.
As a finishing touch to the form, I added a button that allows users to open Excel and work with the spreadsheet inside it's parent application. The code for this form consists of setting the control's Verb property to open the application in a new window, and then re-activate the control:
Me!objExcelCharge.Verb = VERB_OPEN Me!objExcelCharge.Action = OLE_ACTIVATE
I had been worrying about the users' ability to create standard charges. Entering this data into an Access table and trying to visualize the results in an Excel spreadsheet seemed to be asking a lot of people who were already busy enough. After adding the Fix Charges button I realized I had created 95 percent of a form for creating standard charges. All I had to do was alter the save routine to save back to the standard charges table (instead of the order charges table) and add a form that would allow users to specify which standard charges to display. This form, which is shown in Figure 2, is included on this month's Developer Disk .
Figure 2. The form for maintaining standard charges.
I learned a lot on this project and I'd like to close by sharing with you some additional tips:
Writing this application was a rewarding experience. I delivered a useful application to the company and learned a lot. In the end, the whole application probably doesn't have more than 250 lines of code and leverages the capabilities of some very powerful tools.
Listing 1. This routine moves data from the Excel spreadsheet to an Access table.
Sub Loadtable()
Dim db As Database
Dim rec As Recordset
Dim ysnCellNotEmpty As Integer
Dim intRow As Integer
Dim strValue as string
Set rec = db.OpenRecordset("tblOrderCharges")
ysnCellNotEmpty = True
'activate the Excel spreadsheet
Set mobj = Me!objExcelCharge.object
mobj.Action = OLE_ACTIVATE
intRow = 1
'set up to trap error on empty row
On Error GoTo ErrorCellEmpty
strValue = _
mobj.range("C" & CStr(intRow)).Value
While ysnCellNotEmpty
'move data from cells to fields
rec.AddNew
rec("txtOrderkey") = Me.Openargs
rec("txtChargeDescription") = _
mobj.range("B" & CStr(intRow)).Value
rec("curChargeAmount") = strValue
'check if formula matches value
If Val(mobj.range("C" & _
CStr(intRow)).Formula) <> _
Val(mobj.range("C" & _
CStr(intRow)).Value) _
Then
rec("txtChargeFormula") = _
mobj.range("C" & _
CStr(intRow)).Formula
End If
rec.Update
intRow = intRow + 1
'get value to trigger error if blank row
strValue = _
mobj.range("C" & CStr(intRow)).Value
Wend
Set mobj = Nothing
rec.close
db.close
Exit Sub
ErrorCellEmpty:
ysnCellNotEmpty = False
Resume Next
End Sub
Listing 2. This routine runs queries specified as Excel formulas.
Sub LoadQuery (pintRow As Integer)
Dim dbs As Database
Dim rec As Recordset
Dim recPrice As Recordset
Dim que As QueryDef
Dim intPrm As Integer
Dim ysnNoOrdHeaderControl As Integer
'check if formula is really a query name
If Left$(rec("txtChargeFormula"),3) = "qry" _
Then
Set que = _
dbs.querydefs(rec("txtChargeFormula"))
If que.parameters.count > 0 Then
For intPrm = 0 to _
que.parameters.count - 1
'trap error if parm not form field
On Error Goto NoOrdheaderControl
ysnNoOrdHeaderControl = False
que.parameters(intPrm).Value = _
Forms!OrderHeader _
(que.parameters(intPrm).Name)
'if parameter not a form field,
'set it or ask the user
If ysnNoOrdHeaderControl Then
Select Case _
que.parameters(intPrm).Name
Case "Dealer Name"
que.parameters(intPrm) _
= GetDealerNo()
Case Else
que.parameters(intPrm)_
= InputBox _
("que.parameters(intPrm) _
.Name")
End Select
End If
Next intPrm
End If
End If
'open the recordset and get the value
Set recPrice = que.OpenRecordset()
mobj.range("C" & Cstr(pintRow)).value = _
recPrice(0)
recPrice.close
Exit Sub
NoOrdheaderControl:
ysnNoOrdHeaderControl = True
Resume Next
End Sub
Listing 3. This function inserts total lines into the spreadsheet.
Function intAddTotal (pstrFormula As String, _
pstrChargeId As String, _
pintChargeLine As Integer)
Dim strSTFormula As String
Dim strCName As String
Dim iaintSTs As Integer
Dim intHoldChargeLine As Integer
Static sastrSubTotals() As String
Static sintLastSub As Integer
Dim sintSubTotalCount As Integer
intHoldChargeLine = pintChargeLine
strCName = "C" & CStr(pintChargeLine)
If sintLastSub = 0 Then
sintLastSub = 1
End If
'create a cell filled with equal's signs _
'to go above the total
mobj.range(strCName).HorizontalAlignment = XLFILL
mobj.range(strCName).VerticalAlignment = XLBOTTOM
mobj.range(strCName).WrapText = False
mobj.range(strCName).Orientation = XLHORIZONTAL
mobj.range(strCName).FormulaR1C1 = "="
'clear out the other cells on the line
mobj.range("A" & _
CStr(intHoldChargeLine)).value = ""
mobj.range("B" & _
CStr(intHoldChargeLine)).value = ""
'go to the line the total goes on
'and format it
intHoldChargeLine = intHoldChargeLine + 1
strCName = "C" & CStr(intHoldChargeLine)
mobj.range(strCName).HorizontalAlignment = 1
Select Case pstrFormula
Case "SUBTOTAL"
'create a range from the last subtotal
mobj.range("C" & _
CStr(sintLastSub) & ":C" & _
CStr(intHoldChargeLine - 1)).name _
= "SUBTOTAL" & _
CStr(sintSubTotalCount)
mobj.range(strCName).value = _
"=Sum(SUBTOTAL"& _
CStr(sintSubTotalCount) & ")"
'add the name of the range to the list
ReDim Preserve _
sastrSubTotals(sintSubTotalCount)
sastrSubTotals(sintSubTotalCount) = _
pstrChargeId
sintSubTotalCount = _
sintSubTotalCount + 1
sintLastSub = intHoldChargeLine + 1
Case "TOTALSUBS"
'make a formula of all the names
strSTFormula = "="
For iaintSTs = 0 To _
sintSubTotalCount - 1
strSTFormula = strSTFormula & _
"+" & sastrSubTotals(iaintSTs)
Next iaintSTs
mobj.range("C" & _
CStr(intHoldChargeLine)).value _
= strSTFormula
sintLastSub = intHoldChargeLine + 1
End Select
mobj.range("D" & _
CStr(intHoldChargeLine)).value _
= pstrFormula
intAddTotal = intHoldChargeLine
End Function
Peter Vogel is the applications systems supervisor at Champion Road Machinery, a Microsoft Certified Solutions Developer, and contributes to Microsoft's publc Access news groups. peter.vogel@odyssey.on.ca.
To find out more about Smart Access and Pinnacle Publishing, visit their website at:
Note: This is not a
Microsoft Corporation website.
Microsoft is not responsible for its content.
This article is reproduced from the August 1996 issue of Smart Access. Copyright 1996, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call (800)788-1900 or (206)251-1900.