Innovative Interfacing of Microsoft Excel and Access

Karthikeyan.R

The present age of information technology overshadows the earlier technologies mainly due to its versatile and vast application in all fields of life. The durable and elegant Microsoft Excel is one of the most popular spreadsheet applications. It is widely used in handling, inspecting and processing data. Excel as a spreadsheet is an effective tool to view a set of data in different ways. Manipulation and processing data is much more easier than in any common database package. This inherent strength of spreadsheet is widely used in the data-processing community mainly due to its menu driven approach, which requires less computer operational knowledge. In general spreadsheets are very strong in data processing and report generation, but it cannot store and share data.  This constitutes the main weak point of the spreadsheet application.

Those who are familiar with spreadsheet often use multiple spreadsheets for different sets of data for the same purpose. The reason is obvious. Spreadsheet stores data together with all its attributes (like formulae and heading etc.) necessary for data processing and report generations. Whereas relational database, such as Access, stores data in separate tables under a database and one can write codes in models to process the data to generate reports. In that way, in Access, data is more secure and enjoys the benefit of data independence and sharing in a networked environment. But, Access lacks the power of easy data entry and related data processing, such as, sorting, calculations (for that one should know SQL!) etc. Now is it possible to have the benefits of both the world in one go? The answer is Yes!! Using very simple macro codes, it is possible to store data of a given Excel spreadsheet in an Access table and reuse the same Excel sheet for another set of data for the same purpose and in case of necessity it’s also possible to reprocess the same set of data by reloading it into the previous locations from the access database. So, what is the benefit? One can use spreadsheet for data entry and related processing and Access for storing data for future use. In that way, it is possible to use the same spreadsheet permanently for a particular type of application, for example, calculation of income tax of salaried employees.

Excel spreadsheet is a static monolithic database structure. It contains data, formula, comments and graph all together and that is the source of its main drawback (absence of data independence). Here data is not a separate entity, the data cannot be used for some other purpose, where as in any RDBMS data structure, stored data are application independent and are used independently for different purpose in a shared network environment. Spreadsheet is very poor in record maintenance, especially when it’s a routine job, moreover, due to its open monolithic structure; data are vulnerable for getting corrupted.

Suppose there is an elegant spreadsheet to calculate the tax burden of an employee in an organization. Now, how to use it for all employees!!! A simple solution is to copy and modify it accordingly for each employee. Then what is the output? Several spreadsheets! One for each employee!! It is now very difficult to handle so many spreadsheets and simply not possible to keep a track of tax related information of the employees, as we can’t use any query over these spreadsheets. Here we have addressed this problem of data independence and multiple spreadsheets with an example of simple Student Information System and tried to solve it by connecting Excel, Access together using Visual Basic. The solution is very simple and has some added advantages also.

How to get the data independence?

To achieve the data independence in Excel, one has to separate out the data from all other spreadsheet attributes like formula, colour, text attributes etc. This can be done by storing only the data into Access database tables and reloading the data into the same spreadsheet for data reprocessing. This will help us to use the same spreadsheet/workspace for different sets of data.

How to do it?

The whole process can be briefly stated as below:

  • Design the workspace of the spreadsheet as per requirement.
  • Enter data of a particular set.
  • Process the data and generate report as per requirement.
  • Store only the data in Access database with the help of a macro.
  • Clear the spreadsheet to load another set of data for processing.
  • Store the new set of data again in the Access and repeat the process for all sets of data
  • If necessary, reload data from Access tables into the spreadsheet.
  • Modify the data, if necessary, and store the modified data in the database.

As an example let us considered  a semester result processing system of an engineering college (Figure 1) with basic inputs like admission no, session, semester, discipline and marks of three subjects. There are two components of the two mechanism, the backend and the front-end. The backend consists of Access and the front-end is the Excel Spreadsheet. The basic approach is to enter the data through spreadsheet and store the data in Access to make the same spreadsheet reusable for other set of data.

Create an Access Database

Create an Access database on student information system for semester examination marks processing system, say, stuinfo.mdb with the following fields: admission no.(Admno), name (Stuname), with Marks, say, thermodynamics (TRM), machine drawing (MCM), environment engineering (EVS), total marks(Total) and Average (Average).

It is possible to import Access database table into Excel by query of table. This can be achieved through new database query of "Get External Data" of Data Menu. This method copies the table as per the mentioned query to the location pointed by the cursor. But if  it is required to copy in a particular predefined format then it is not possible. Whereas copying an Excel spreadsheet into Access database (to separate tables) is not possible through any menu item.

Figure 2: Excel Spreadsheet with data and command buttons

Create an Excel Spreadsheet

A new Excel sheet (Figure 2) may be created with two command buttons and a tabulation sheet of marks. First button is to Save excel data (pure) in the marks table of Access and the next one is to Load the same from the tables of Access to the previous locations of the spreadsheet. The Save button checks the Access table for the key fields, if it is not there, it calls addition routine to add records, whereas if its already there, then it goes for updation, giving an opportunity of updation of the spreadsheet. For modification of old data, one has to load the data in the Excel sheets, then save the modified data again in the Access database tables using add/update button.  So these two functions (Save & Load) give us the necessary two-way communication between Excel and Access.

 

Figure 3: Access Database with two tables

Save Macro

This is the first step of the macro development. The purpose of this command button is to save the Excel data sheet to Access database. Functionally this can be divided in three routines, namely, OpenDatabase, ReadExcelRecords(), and AddtoDatabaseAddtoDatabase calls two subroutines, UpdateExistingRecord and AddNewRecords, depending upon the existence of the records in the database tables,. All routines are as in following program listings:

 

Sub SaveToAccess()
flag = OpenAllFiles()
Session = "0304"
Semester = "BTCSE4"
If flag Then
Call ReadExcelRecords()
Call AddToDatabase()
End If

End Sub

Macro – 1  Main Save routine

Function OpenAllFiles() As Boolean

                 Dim dbPath As String
dbPath = "d:\Excel\stuinfo.mdb"
On Error GoTo dbErrors
Set dbContact = DBEngine.Workspaces(0).OpenDatabase(dbPath, False)
OpenAllFiles = True
Exit Function
dbErrors:
OpenAllFiles = False
MsgBox (Err.Description)

End Function

Macro –1.1 Open  database

Sub ReadExcelRecords()
Dim a As String
Dim startr As Integer
Dim endr As Integer
Dim c As Integer
Dim m As Integer
Dim n As Integer
Dim r As Integer
Dim i As Variant
Dim x As Variant
Dim StuRecordTemp(20) As Variant
startr = 13
endr = 26
c = 0
ColSet = Array("B", "C", "D", "E", "F", "G", "H", "I")
m = 0
NoOfStudents = 0
For r = startr To endr
n = 0
For Each i In ColSet
a = i & r
Range(a).Select
x = ActiveCell.Value
StuRecordTemp(n) = x
n = n + 1
Next
n = 0
If (StuRecordTemp(1) = Session And UCase(StuRecordTemp(2)) = Semester) Then
For Each i In ColSet
StuRecord(m, n) = StuRecordTemp(n)
n = n + 1
Next
NoOfStudents = NoOfStudents + 1
m = m + 1
End If

Next   r

End Sub

Macro – 1.2  Read data from excel  spreadsheet

Sub AddToDatabase()
Dim i As Integer
Dim j As Integer

Dim MatchRollNo As Boolean

     sqlNames = "Select * "
sqlNames = sqlNames & " From Marks"
sqlNames = sqlNames & " Where Session = "
sqlNames = sqlNames & "'" & Session & "'"
sqlNames = sqlNames & " and Semester = "
sqlNames = sqlNames & "'" & Semester & "'"
sqlNames = sqlNames & " Order by Session,Semester,RollNo"

Set rsAllNames = dbContact.OpenRecordset(sqlNames)
j = rsAllNames.RecordCount
i = 0
Do While i < NoOfStudents
With rsAllNames
If (j > 0) Then
.MoveFirst
‘ Seek the record in the table
MatchRollNo = SeekRollNo(!RollNo, StuRecord(i, 0), rsAllNames)

    ‘If found then update else add

    If MatchRollNo And Not (.EOF) Then
Call UpdateExistingRecords()
Else
Call AddNewRecords (i)             ' if there is record of the given semester but not the particular                                                                                   ‘adm. no.
End If
Else
Call AddNewRecords (i)             ' if there is no record in the access
End If
i = i + 1                                                               ‘Next  Record
End With
Loop
rsAllNames.Close                                                ‘Close Record Set
dbContact.Close                                                  ‘Close the Database
End Sub

 

Macro - 1.3  Check the database tables and call add or update macro

Sub UpdateExistingRecords()

On Error GoTo ErrorHandler                             ' Enable error-handling routine.
Dim i As Integer                                                   'Student no counter
Dim j As Integer                                                   'Subject Counter
Dim MatchAdmno As Boolean
Dim MatchSubject As Boolean
Dim rsAllNames As Recordset
Set rsAllNames = dbContact.OpenRecordset(sqlNames, dbOpenDynaset)
rsAllNames.Edit
rsAllNames.Update
i = 0
Do While i <= (NoOfStudents - 1)
With rsAllNames
j = 0
.MoveFirst
MatchAdmno = SeekRecords(!Adm_no, Stu_Array(i, 1), rsAllNames)
Do While MatchAdmno And Not (.EOF)          'j <= (NoOfSubjects - 1)
.Edit
!SRN = Stu_Array(i, 0)
!RollNo = Stu_Array(i, 1)
!Session = Session
!Semester = Semester
If (splflg) Then
!subje_pf = IIf(!Subje_ftsp <> "SP", "SF", "SP")
Else
!subje_pf = IIf(!Subje_ftsp <> "P", "F", "P")
End If

!PassFail = IIf(Trim(!remarks) <> "Passed", "F", "P")
.Update
j = j + 1
.MoveNext
If Not (.EOF) Then
If (!Adm_no = Stu_Array(i, 1)) Then
MatchAdmno = True
'MatchAdmno = SeekRecords(!adm_no, Stu_Array(i, 1), rsAllNames)
Else
MatchAdmno = False
End If
End If
Loop
End With
'rsAllNames.MoveLast
'rsAllNames.Edit
i = i + 1
Loop
Exit Sub                                        ' Exit to avoid handler.

ErrorHandler:                                   ' Error-handling routine.
If Err.Number <> 0 Then
msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description & " Adm no: " & Stu_Array(i, 1) _
& " Sub Code: " & Sub_array(j, 1)
MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
End If
Resume Next     ' Resume execution at next line
' that caused the error.
End Sub

Macro - 1.4  Read data from spreadsheet and update Access tables

Sub AddNewRecords(i As Integer)

On Error GoTo ErrorHandler                                             ' Enable error-handling routine.
Dim col As Integer

With rsAllNames
.AddNew
col = 0
!RollNo    = StuRecord(i, col)
!Session   = StuRecord(i, col + 1) 'Trim(Mid$(Session, 3, 2) & Mid$(Session, 8, 2))
!Semester = StuRecord(i, col + 2)
!MCD       = StuRecord(i, col + 3)
!THR        = StuRecord(i, col + 4)
!MCP       = StuRecord(i, col + 5)
!Total       = StuRecord(i, col + 6)
!Average = StuRecord(i, col + 7)
.Update
.AddNew
End With
Exit Sub                                                                                ' Exit to avoid handler.

ErrorHandler:                                                                       ' Error-handling routine.
If Err.Number <> 0 Then
msg = "Error # [AddNewRecords] " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description & " RollNo: " & StuRecord(i, 0)
MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
End If
Resume Next                                                                     ' Resume execution at next line
' that caused the error.

End Sub

Function SeekRollNo(admno As String, TempStu As Variant, rsAllNames As Recordset) As Boolean

Dim MatchAdmno As Boolean
With rsAllNames
MatchAdmno = False
Do While Not (MatchAdmno) And Not (.EOF)

If (admno = TempStu) Then
SeekRollNo = True
MatchAdmno = True
Exit Do
Else
SeekRollNo = False

End If
.MoveNext

If Not (.EOF) Then
admno = !RollNo
'             MatchAdmno = SeekRecords(!adm_no, Stu_Array(i, 1))
End If
If (.EOF) Then
SeekRollNo = False
Exit Do
End If

Loop
End With

End Function

Macro - 1.5 Read data from spreadsheet and write into Access tables

Figure 4: Marks table with added records

Load Macro

The purpose of the macro is to load records from the access tables into the excel data sheet. While loading program takes care of the positions of the different fields in the spreadsheet.  So using this button one can call old records into the spreadsheet and modify it, after that using the save button he can save the modified data can be saved back into the database table.

Sub LoadToExcel()
flag = OpenAllFiles
Session = "0304"
Semester = "BTCSE4"
If flag Then
Call LoadRecords()
End If

End Sub

Macro – 2 Main Load Routine

Sub LoadRecords()
Dim i As Integer                                                   'Student no counter
Dim r As Integer                                                   'Subject Counter
Dim c As Integer
Dim FirstTimeSp As Boolean
Dim FirstTimeRg As Boolean
Dim MatchAdmno As Boolean
Dim MatchSubject As Boolean
Dim rsAllNames As Recordset
Dim sr As Integer

Dim subcode As Variant

sqlNames = "Select * "
sqlNames = sqlNames & " From Marks"
sqlNames = sqlNames & " Where session = "
sqlNames = sqlNames & "'" & Session & "'"
sqlNames = sqlNames & " and semester = "
sqlNames = sqlNames & "'" & Semester & "'"
sqlNames = sqlNames & " Order by Session,Semester,RollNo"

Set rsAllNames = dbContact.OpenRecordset(sqlNames, dbOpenSnapshot)
NoOfStudents = rsAllNames.RecordCount

If (NoOfStudents > 0) Then
r = 13                                                                                  ‘Starting row position of the spreadsheet
c = 2                                                                                   ‘Starting column position of the spreadsheet
For i = 1 To NoOfStudents
With rsAllNames
Cells(r, c)        = !RollNo
Cells(r, c + 1) = !Session
Cells(r, c + 2) = !Semester
Cells(r, c + 3) = !MCD                                                 'Machine Drawing
Cells(r, c + 4) = !THR                                                  'Thermodynamics
Cells(r, c + 5) = !MCP                                                  'Computer Programming
Cells(r, c + 6) = !Total
Cells(r, c + 7) = !Average
.MoveNext
End With
r = r + 1
Next
End If
rsAllNames.Close
dbContact.Close

End Sub

 

 

Conclusion

Any technical innovation should be evolutionary in nature rather than revolutionary. This is achievable only when benefits of existing techniques/applications are optimally used. The discussed in this article uses the advantages of both the Excel and Access in a hybrid mix. It is appears  that the data independency is achievable and it is also possible to reuse the same spreadsheet for different sets of data. The outcomes of the approach are:

  • Data independency is achievable in a spreadsheet.
  • Same spreadsheet can be used for more than one set of data.
  • Excel works as frontend to backend MS Access.
  • Since data is stored permanently in Access tables, interfacing with different platforms is also possible.
  • Maintenance of records can be made much more systematic and effective, compared to the record maintenance in Excel.

 








}