Excel as a data Entry Form for Microsoft Access?
By Scott A McManus
You can take an Excel spreadsheet and turn it into a data entry form with data verification and error checking for users who feel more comfortable inputting data in a spreadsheet environment. Using automation, templates and the data validation option in Excel you can exert the same control over users’ input as you would in Access using lookup tables, relationships and rules.
“Why would you?” I hear a lot of Access developers say.
A well designed Microsoft Access User Interface provides data verification in the form of validation rules, look up tables and relationships that are very difficult to replicate in Excel.
I used to agree with them, but recently I was involved in four projects where the staff entering the data into databases were not dedicated data entry people, but professional people who needed to enter a small amount of data each day. Even though the projects were in different industries all of the staff were familiar with spreadsheet usage and in fact used them daily in many complex and varied ways to assist in scientific research or financial applications.
Unfortunately the lovingly made forms of the User Interface for each project were rejected by the staff. In all cases they wanted their familiar “copy”, “paste” and “fill” functionality of excel to input their data. They found great trouble entering data row by row. They preferred to enter data in part rows or part columns as the mood, or brain process struck them. As these users were also Senior Management or Technical Professionals we needed to ‘Deal with it!’ and give the user the Interface they wanted – but we also needed to ensure that data was valid and verified, without taking up to much valuable time from our ‘Power Users’.
This then leads us on too two separate areas that I will now consider;
- How shall we import the Spreadsheet Data?
- How shall we make sure the data is valid and verified?
The ideal solution should incorporate both areas into the one design.
Importing Data from Microsoft Excel to Microsoft Access
Which ever method you choose to employ to import the spreadsheet contained information into Microsoft Access; a carefully designed Template will make life easy for you and allow more automatic process. Import and code processes that you implement will be simpler if you use a template. With out one, you begin to allow a free flow ‘James Joycean’ data input that will require more complex testing to cover all eventualities, with the end result being manual import. For your users it provides a safe and friendly Data entry environment. So take the time to design the Spreadsheet User Interface and have it accepted by your Users before continuing on with the import and validation processes.
Import into Access Manually
A common but messy way that I have seen many developers use is to use the Intrinsic Access import tool to import the excel sheet as a table. Either into an existing table after deleting previous records, if the structure and header names do not change, or into a temporary table and then using an update/append query if the header names do change.
The process is;
1. Select File > Get External Data > Import
2. Using the File Open Dialogue select the spreadsheet file.
3. Select the “Named Range” or “Work Sheet” (see Figure 1.)
4. Select whether the first Row contains field names
5. Then either import into a new table (Which is used as a temporary Table) or go to Step 6.
6. Import into an existing table where the Field Names are EXACTLY the same.
7. If step 5 was chosen a query either using the ‘Query By Example interface” or an SQL “do Command “ In code under a button. (ADO or DAO could also be used). Things will get tricky if these are meant to be updates rather than just Appends!
Figure 1. The familiar Access import wizard in use with an excel spreadsheet. Note how you may select a named range or a total sheet.
I have several reservations about this method.
The first is that constant importing and deleting into temporary tables will cause the database to bloat and necessitate more frequent use of Database compaction.
The second problem is programmatically accessing the table name, if it is a temporary table name, one would have to ensure that the name of the new import table was passed to the routine that then merged or appended the data to the main table(s).
Usually, where I have seen this style of import, it is implemented as a two stage process.
The next problem is that the access importer assigns the data type (i.e. integer, number, text, date) based on the first non header row that contains data in the work sheet. This can cause problems with mixed data type fields where there can be numbers, alpha characters or a combination. If the numeric data is in that first row then the field type is made numeric and errors occur with import failures. This will also cause Data Type errors when importing directly to an existing table. Unfortunately the import wizard does not let you change the auto selected data type. To get around this you need to export your data, yes another step, to a text based format such as *.CSV or *.TXT. Figure 2 shows a situation where an Integer field in Excel has been assigned the Double Data Type.
Figure 2. During an excel import, the Import Wizard has decided to make an Integer Field in Excel a ‘Double’ data type in Access. You can see how the Data Type combo box has been disabled.
Finally unless you use ADO or DAO in the final step of moving the data form the Temporary Table you loose control of the Data Validation / verification process.
Linking to excel
In this method you also use the External Data Wizard, but this time we link to the spread sheet the same way we would with a software/application MDB and a Data Only MDB.
So File> Get External Data > Link Tables
1. Then using the File open Dialogue select the Excel Spread Sheet
2. select work sheet or named range
3. Let The Wizard know if the first row contains the Header.
4. Provide a name for the new linked table.
Figure 3. An example of a linked spreadsheet after using the “Import Wizard”.
The linked spreadsheet can now be treated like any other Access table – as long as you had all of your data in rows. You can view it, include it in Append / update queries. You can use ADO, DAO or SQL in Code to update from the linked table and import into the main table.
Downfalls with this method are few. The main one being that if you keep linking with a different Table name then you have to keep on creating Queries (or modifying them) and modifying your code to refer to the latest Linked Table name. As with the Manual import wizard the data type of the fields is automatically set by Access, so this may cause you some concern.
One last thing you should look out for using this method is if the same sheet is continually used for data input. When do you know to run the import routine? How can you be sure earlier imports have succeeded and have / have not been dealt with successfully?
If you use the code method then you can begin to use some verification / error trapping which I discuss in the second part of the article.
Automating Excel
This is by far my preferred method as it solves the problem of varying excel names and because everything is code driven, errors in the data can be trapped and dealt with inside routines at import time.
The method requires that you use a template and that information is stored exactly the same way each time. The previous methods, because they are not fully automatic, allow for human intervention in the situation where columns are not consistent.
Because the method relies on templates, rather than an exclusive row by row access, Look up tables, Messages to Users and even Forms and buttons can be used in the template to enhance verification and validation (Refer to second section) and may exist in the same row that data does – something that you can not do if you use the Import wizard to import or link an excel sheet.
Use the common dialogue control to get the location and name of the excel file that you want to import. You can use an API call, the Office FileOpenDialouge box or the Windows FileOpenDialoge box. If you are programming in Visual Basic you can use the additional component Common Dialogue Control (Which is already part of the tool box in VB.NET!)
Then you Instantiate an excel object and open the file. You than determine how big the range is, if there is not a predefined set of rows in each data entry sheet.
Other articles have dealt with using file open dialogues before, so I will not show that detail here, but I will go over instantiation of automation objects.
First you will need to reference the excel object library in your access project.
1. Make sure you are in a code window (I.e. the Microsoft Visual Basic For Applications IDE)
2. Select Tools > References from the menu Bar
3. Select the Microsoft Excel Object Library you have installed on your machine. Refer to Figure 4, which shows the reference selection box
Figure 4. The reference manager in Visual Basic For Applications.
|
Dim xlsApp As Object Dim strFileToOpen as String set xlsApp = CreateObject("Excel.Application") ‘locate the xls file to be imported into the ‘data base using a file open dialog Set xlsApp = New Excel.Application xlsApp.Workbooks.Open strFileToOpen ‘Run your Import here ‘Check your Data here ‘Close the application xlsApp.Workbooks.Close set xlsApp = Nothing
|
Code Listing 1. Instantiation and closing an excel object.
Code Listing 1, shows how to instantiate the excel object. It is important to make sure you close the spreadsheet after use and set the object variable to nothing. Now if we do not know how many rows will need to be imported we need to find out from the excel spread sheet. The ideal case is that there are a set number of rows to be imported each time from a spreadsheet. Using the “Current Region” method in Code listing 2 works fine to find out the number of rows to import, if the rows are always filled in and there are not too many gaps in the data.
|
Dim strSheetName as String Dim strRowCount as Integer Dim i, j as Integer xlsApp.Worksheets(strSheetName).Cells(i, j).Select ‘Select the left top most cell in the work sheet. ‘you can use CurrentWorksheet or sheet Index ‘or as I have used the actual name. ‘cell refernce is by cell(row,column) xlsApp.ActiveCell.CurrentRegion.Select ‘this selects the region of data. It is much ‘easier to use templates with fixed numbers ‘of data entry rows. strRowCount = xlsApp.Selection.Rows.count
|
Code Listing 2. Finding out how many rows of contiguous data there are in the spreadsheet selected using Excel’s ‘CurrentRegion’ method.
We now know how many rows of data we are about to import.
- we could now set up a verification and validation routine that checks each piece of data against a rule or look up table – if it is incorrect we can color the excel cells with a pre determined color for a type of error or even insert comments with an error message. (Relationships, Existence of keys etc can also be checked here). See the next section.
If there were no errors we would then line by line extract the Excel data and input it into the required table(s). We can input it as either an ADO or DAO Record set. We would open the record set and then enter ‘edit’ mode with a Record set that remains open during the process or you could use a line by line execute of an SQL Insert (or Update) statement. The SQL takes longer to execute but is easier to set up if you are not ADO or DAO conversant. The next three code listings provide an example using each method. The ADO and DAO methods are very similar.
Code listing 3 gives an example of extracting data from each row in the spreadsheet selection and generating an SQL INSERT statement into a single table. We could, if needed, use two or more SQL statements here and access more than one table with either INSERT or UPDATE statements, depending on the information contained in your spreadsheet. It is important to note that each row will generate a message from access as it begins the SQL action. A second message will display if there is an error. So setting the warnings of will allow the process to flow without unnecessary messaging from Access. But you need to be careful! Access will not warn you if a row is not inserted due to a key violation or data error, it will just skip on to the next row. So you need to make sure before this step that your data is ready to be inserted or you can devise a way to determine how many rows are successfully inserted and if it does not match the number of rows in your spreadsheet than you can deal with the issue. Personally I feel checking the data first and cleaning up the data before this stage is the neatest way. Refer to the next section in the article on automating data verification and validation.
|
DIM sqlStr as string Dim strHole as string Dim strFrom, strTo as Single Dim strMag as Integer Dim xlsSheetApp as string xlsSheetApp = xlsApp.Worksheets(strSheetName) ‘set up a loop to enumerate the rows in the ‘Spread sheet and insert them into a single ‘table using SQL DoCmd.SetWarnings False ‘note this setting is important to stop a message ‘being generated for each row insert. For i = 1 To strRowCount strHole = xlsSheetApp.Cells(i, 1).Value strFrom = xlsSheetApp.Cells(i, 2).Value strTo = xlsSheetApp.Cells(i, 3).Value strMag = xlsSheetApp.Cells(i, 7).Value ‘Note with this method we can skip columns ‘in the rows or even grab information from ‘the row below or before using ‘xlsSheetApp.Cells(i+1,7) sqlStr = "INSERT INTO [table1]” &_ “( Field1, Field2, Field3)" &_ "VALUES ( '" & strHole & "'," & _ strFrom & "," & strTo & "," & strMag & " )" DoCmd.RunSQL sqlStr Next i |
Code Listing 3. Gathering data from the spreadsheet in a loop and inserting it row by row into a single table.
Whilst the SQL insert method is easy to implement, if you have never used DAO or ADO technologies, it does have disadvantages. One I have already pointed out is lack of data error handling during the loop. The next disadvantage is speed. SQL writes from code are ok if you have a block of data, but because we are operating on a single row at a time we are generating many SQL statements, each with its own overhead. In the ADO and DAO methods we have only one open statement to open a connection to a table or tables. We then leave the table open during the loop and write the row by row data as edit / append statements. I have found that the greatest over head in regards to speed with ADO or DAO is the open statement to connect to a table. Both the DAO and ADO statements are very similar.
Code listing 4 shows the same loop as in listing 3, but with ADO used instead of an SQL statement. We will assume for now that the record it ok to be inserted and does not already exist, and satisfies our other requirements.
|
Dim rstLogic As New ADODB.Recordset Dim cnCurrent As ADODB.Connection Set cnCurrent = CurrentProject.Connection logic.Open "logic_faliures", cnthisconnect, _ adOpenKeyset, adLockOptimistic, adCmdTable For i = 1 To strRowCount strHole = xlsSheetApp.Cells(i, 1).Value strFrom = xlsSheetApp.Cells(i, 2).Value strTo = xlsSheetApp.Cells(i, 3).Value strMag = xlsSheetApp.Cells(i, 7).Value ‘Note with this method we can skip columns ‘in the rows or even grab information from ‘the row below or before using ‘xlsSheetApp.Cells(i+1,7) ‘ Open a new record read for insert rstLogic.AddNew rstLogic![Field1] = strHole rstLogic![Field2] = strTo rstLogic![Field3] = strMag rstLogic.Update ‘commit the insert Next i rstLogic.Close |
Code Listing 4. Using the ADO insert method to import values from the Spreadsheet.
Look ups and validation in our Excel UI
AS I have said earlier, and I will re enforce now, build a template. This will ensure that the data is entered the same way each time and provide a familiar layout for the data entry users
Excel has inbuilt validation that you can access from the menu item, “Data” > “Validation”.
Figure 5. Setting up Validation in Excel.
Figure 5 shows some of the options you have with data validation. You can apply the rule to a single Cell or to an entire Column. Using the “Input Message” Tab you can provide hints or tips to the data entry users. These messages are quiet handy because they are in the same format as an Excel Comment are un obtrusive and do not require the User to press a button to remove the message. The message appears when the user selects a cell that has had validation applied to it. The Message Box appears to the right of the cell. When the user moves of the cell the message disappears or is replaced by the message that refers to the cell that the user has just moved to. If you find your Users being overloaded with messages, you could put a list of permanent Messages in Colored boxes to the side of the template. The “Error Alert” Tab can be used to set up a message box to alert the user that data inconsistent with the input cell has been entered. Because this is a message box and it would appear each time an incorrect value is entered, you may choose to use a routine that checks the whole sheet after the data entry is complete. I recommend you use limited message boxes, as they can become annoying to your users, if you use the “Input Message” the user is already prompted with an example of what should be in the cell and most important of all, if the user “Copies and Pastes” then the input by passes any of the error checking! You can use ‘Information’, ‘warning’ or ‘Stop’ message boxes with a description or example of the type of data that should be entered.
When you set up the validation rule you have different options depending on the data type you select, for example you can choose data between a certain range, greater than, less then and so on.
If you decide to use a list for Text based input and have it ‘pop down’ in the cell then you will need to provide a location for where the list exists. This may now require that you hide and protect the cells that contain your list(s) because if the users can see them, they can change them. Another thing to consider in placement of the list cells is that if a user deletes an entire row or column, it may delete information from the lists. One strategy that works is to partition your spreadsheet template into quadrants and then using protection, and ‘split’ or ‘freeze panes’ from the Windows Menu item. Placing the data input location in either the Top Left Quadrant or Bottom Right Quadrant and the lists in the opposite corner bypass the row / column deletion problem.
Things to remember
• Validation rules can be applied to a whole row or column – this is handy where a variable number of data entry rows will be entered each time
• Copy and Paste Actions Ignore the Validation rules
• Range Data from Lists can be accidentally deleted or changed (or intentionally), so you either need to protect that area of the web site or place it strategically – such as the upper left hand corner and using a template.
• These validation rules alone do not guarantee that your data is ready for data entry
Populating a Validation List in Excel From an Access Table
As the data in the database changes, including the data in look up tables, rather than copying and pasting the data from access to excel (with the premise that you actually remember to do this!) a more elegant solution is to create a routine that executes on the spreadsheet template load event that determines the number of records in the lookup table and then populates the spreadsheet look up area with those values.
Code Listing 6 shows the basic code in Excel for setting up a validation list.
|
Columns("G:G").Select ‘This is the cell range that you want ‘to have the list drop down. Select ‘a cell range if you do not want the full ‘Column or row. With Selection.Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:= xlBetween, _ Formula1:="=$U$4:$U$27" ‘Formula1 is the range where you are going to ‘place the items for the drop down list .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With |
Code Listing 6. The VBA code from Excel for setting up a drop down list. Note that the selection can be any range, and that Forumla1 is the range that contains the items that will populate the drop down list.
Note that you could use the Modify method of the Validation object to reset the range in “Forumla1” using the following
expression.Modify(Type, AlertStyle, Operator, Formula1, Formula2)
where Type is the only required property and Expression is the selection.
Now putting this together we use the “This WorkBook” Object in the VBA IDE and Select the “Open” event for “WorkBook”. You could use the intrinsic Excel Data Import Routine that makes use of DSN’s or you could use Automation. I prefer to use Automation as the DSN route means that you have to set up a DSN for each machine in multiple workstation situations.
|
Dim rsLKTl As New ADODB.Recordset Dim recCount, i As Integer Dim cnNew As String Dim rsRows As Variant 'open the connection Set rsLKTl = New ADODB.Recordset cnNew = "PROVIDER=microsoft.jet.oledb.4.0;” & _ data source= c:\temp\lookups.mdb;" rsLKTl.Open "select * from Lookup", cnNew, _ adOpenKeyset, adLockReadOnly recCount = rsLKTl.RecordCount rsRows = rsLKTl.GetRows For i = 0 To recCount - 1 Worksheets(1).Cells(i + 1, 8).Value = _ rsRows(0, i) Next i rsLKTl.Close Set rsLKTl = Nothing |
Code Listing 7. Automating Access from Excel to populate a range in Excel using ADO.
Code Listing 7 shows an ADO routine (You need to add a reference to ADO in you Excel VBA IDE References, a Reference to Access is not required) for getting the rows in a look up table in the Access Database, Looping through it and extracting each value in to a field. In the Cell reference we are actually asking Excel to use Cell “H1” and to add down the column for succeeding rows. After this we could then call the code from Code Listing 6 to modify the range of an existing List or add a new list, we know what the starting cell will be for the list and because we know the record count we can add this to the first cell to obtain the new expanded range for the list if our Look up table in Access has been added to.
Validating Spreadsheet Data prior to Import
This can be done from Access by automating Excel just prior to import, or you can place some buttons in the template. If you do place buttons to check data in the template I would recommend that you only check to make sure values exist where required and that data contained in cells is consistent with rules that have been made with Excel Validation (I.e. you are catching errors that have slipped through by “Copy and Paste”). Checking data against relationships and data rules may be best run from Access.
By placing some buttons in your sheet, you can ask your users to validate their data entry, looping through each row and looking at particular cells to make sure either data exists, or that it is consistent with data that should be in those cells. I usually color the cell if there is an error, on running the check button again cells have their color returned to the default color so the user knows most of the errors are taken care of. You may also like to place a comment against the particular error to help the user determine what is needed to correct the error. Code listing 8 shows an example where data entry is checked row by row, and checks that one numeric field is greater than another and that fields exist, it also adds a comment with verbose comment if the user needs more information.
|
Dim strhole Dim j Dim i Dim strfrom Dim strto
For i = 1 To 100 J=i+1 strhole = Worksheets("Sheet1").Cells(j, 7).Value strfrom = Worksheets("Sheet1").Cells(j, 8).Value strto = Worksheets("Sheet1").Cells(j, 9).Value If strhole Like "OT*" And Len(strfrom) < 1 Then Worksheets("Sheet1").Cells(j, 8).Select Selection.Interior.ColorIndex = 3 Selection.Interior.Pattern = xlSolid End If If Len(strhole) < 1 Then Worksheets("Sheet1").Cells(j, 7).Select Selection.Interior.ColorIndex = 3 Selection.Interior.Pattern = xlSolid End If If strfrom > strto Then Worksheets("Sheet1").Cells(j, 9).Select Selection.Interior.ColorIndex = 5 Selection.Interior.Pattern = xlSolid ActiveCell.AddComment ActiveCell.Comment.Visible = False ActiveCell.Comment.Text = "To should be Greater than From" Next i |
Code Listing 8. Code in Excel VB (Which can also be used in automation from access) checking data input and coloring cells where errors are found.
Here we will validate the data in each row of the Spread sheet against rules and look up tables in the database – This should be done as a batch rather than have a message box appear every time there is an error during import. A message box system means the user has to manually note problem rows – if the error message gives that row, and in some cases the application may end on a particular error. In some cases if you have 1000’s of Bits of information and all of them wrong pressing ok to every error will take Hours.
A better solution is to test for errors, if there are no errors then commence the batch import of the spreadsheet data. If there are errors, as previously stated, color the problem data cells. Using a predetermined color for particular errors, or programmatically entering cell comments will assist in correcting the data problems.
Code listing 9 shows some code that can be used in a loop of the excel sheet or within access. In this case we want to check to make sure that ‘Hole’ exists in the Hole table and we want to make sure that in the One-to-many table “Interval” ‘Hole’ and ‘dFrom’ do not already exist. Hole forms the Primary Key in the Hole Table, and Hole and dFrom form the Primary key in the Interval table and Hole is also a foreign key that references the Hole table.
|
rstHole.Open "SELECT * FROM HOLE WHERE [Hole] &_ = '" & strHole,cnthisconnect,adOpenForwardOnly,_ adLockReadOnly, adCmdText ‘Ado Open command ……….. strHole1 = rstHole(“Hole”) strFrom1 = rstInterval(“fFrom”) If isnull(strHole1) = true or strhole1 = “” Then ‘Error Hole must exist to create one to many ‘relationship!!! Else If strFrom = strFrom1 then ‘Error strFrom is part of the key with Hole ‘to form a one to many relationship ‘strFrom & StrHole can not exist already!! End if End If |
Code listing 9. Checking data from excel to see if it is ready for importing.
I have used ADO here, but you can also use DAO and in simple checks even DLookup could be used.
From here it is a simple matter to write error colors and comments back into the excel spreadsheet and end the session warning the Importer that there has been errors and the spreadsheet has been ‘notified’ or continue on to the import phase that I discussed in section one.
Conclusion
Using Excel for data entry runs a poor second against User Interfaces that can be designed in access using intrinsic access features such as the power of linking a combo box in table design to a table that functions purely as a look up table. If you do have to deal with Excel as a data entry front end, then with a little hard work and good design you can cope and provide your users with a User Interface that leaves them with a comfortable ‘warm fuzzy spreadsheet feeling’ but ensures that data is valid and reduces the amount of work required of the Data Base Administrator at each import.
Author Biography. Scott McManus is the Principal of Skandus. Scott’s interests are in object rich databases, XML, Outlook and 3D objects in the Mining Industry. Outside the office he enjoys, archaeology, natural science, music and the Mid North Coast of NSW. www.skandus.com skott@skandus.com
This appeared in SmartAccessOnline www.smartaccessnewsletter.com
