|
|
Innovative Interfacing of Excel and Access
Posted On May 29, 2007 by Raja Kishore Reddy filed under
As a spreadsheet, Excel 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 spreadsheets 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 is the main weak point of a spreadsheet application.
Those who are familiar with spreadsheets 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 this way, in Access, data is more secure and enjoys the benefit of data independence and sharing in a networked environment. However, Access lacks the power of easy data entry and related data processing, such as, sorting, calculations (for that one should know SQL!), etc.
So now the big question is whether it is possible to have the benefits of both, Excel and Access, in one go? The answer is Yes!! Using very simple macro codes, it is possible to store data in 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 is 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 spreadsheets 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 graphs, all together. And this feature is the source of its main drawback (absence of data independence). Here, data is not a separate entity; data cannot be used for some other purpose. Whereas in any RDBMS data structure, stored data are application independent and used independently for different purposes in a shared network environment. Spreadsheet is very poor in record maintenance, especially when it is a routine job. Moreover, due to its open monolithic structure, data are vulnerable to corruption.
Suppose there is an elegant spreadsheet to calculate the tax burden of an employee in an organization. Now, how do you use it for all the 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 will be very difficult to handle so many spreadsheets and simply not possible to keep a track of tax related information of the employees, as we cannot use any query over these spreadsheets.
In this article, the problem of data independence and multiple spreadsheets have been addressed by providing an example, a simple Student Information System. The solution involves linking Excel and Access through Visual Basic. The solution is very simple and has some added advantages also.
How to achieve data independence?
To accomplish data independence in Excel, one has to separate the data from all other spreadsheet attributes like formula, color, 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 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 your requirements;
· 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 the Access tables and into the spreadsheet; and
· Modify the data, if necessary, and store the altered data in the database.
As an example, let us consider a semester result processing system of an engineering college with basic inputs like admission number, session, semester, discipline and marks of three subjects. The basic approach is to enter data through a spreadsheet and store the data in Access to make the same spreadsheet reusable for other data sets.
Creating an Access Database
Create an Access database on student information system for semester examination marks processing, 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 an Access database table into Excel using query of table. This can be achieved through a new database query, "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.
Creating an Excel spreadsheet
A new Excel sheet may be created with two command buttons and a tabulation sheet of marks. The first button is to Save excel data (pure) in the marks table of Access while the next one is to Load the same from the tables of Access to previous locations of the spreadsheet. The Save button checks the Access table for key fields. If it is not there, it calls addition routine to add records, whereas if it is already there, then it goes for updating, giving an opportunity to update the spreadsheet. For the purpose of modification of old data, one has to load the data into Excel sheets, then save the modified data again in the Access database tables using add/update button. So, these two functions (Save and Load) give us the necessary two-way communication between Excel and Access.
Save Macro
This is the first step of macro development. The purpose of this command button is to save the Excel data sheet to Access database. Functionally, this can be divided into three routines, namely OpenDatabase, ReadExcelRecords() and AddtoDatabase. AddtoDatabase calls two subroutines, UpdateExistingRecord and AddNewRecords, depending on the existence of records in the database tables. All routines are as in the following program listings:
Macro 1: Main Save routine
Sub SaveToAccess()
flag = OpenAllFiles()
Session = "0304"
Semester = "BTCSE4"
If flag Then
Call ReadExcelRecords()
Call AddToDatabase()
End If
End Sub
Macro 1.1: Open database
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.2: Read data from excel spreadsheet
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.3: Check the database tables and call add or update macro
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.4: Read data from spreadsheet and update Access tables
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.5: Read data from spreadsheet and write into 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
Load Macro
The purpose of this macro is to load records from the access tables to 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 one can save the modified data back into the database table.
Macro 2: Main Load Routine
Sub LoadToExcel()
flag = OpenAllFiles
Session = "0304"
Semester = "BTCSE4"
If flag Then
Call LoadRecords()
End If
End Sub
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. This article discussed the uses and advantages of both Excel and Access in a hybrid mix. It is appears that data independency is achievable and it is also possible to reuse the same spreadsheet for different sets of data. Notable results of this approach include:
§ Data independency is achievable in a spreadsheet;
§ The same spreadsheet can be used for more than one set of data;
§ Since data is stored permanently in Access tables, interfacing with different platforms is also possible; and
§ Maintenance of records can be more systematic and effective, compared to record maintenance using Excel only.