Unanswered: From Access to Excel single cell via user defined function.
I have an Access database table which lists the attendance for each class and on each date for a school. I want to be able to create a user-defined function in Excel to pull the data of attendance for a specific class on a specific date into a specific cell in Excel ; I don't want to simply dump the entire table into Excel, or all the data for all classes on a specific date. Ideally the user defined function would find its conditions from cells in Excel -- such as a function =GetAttendance([class],[date]) located in cell C1 could be =GetAttendance(+A1,+B1) where the conditions are in A1 and B1. I am using Microsoft Office 2007.
The database is schooldata.mdb
The table is ClassData
The Class is in ClassData.Class
The Date is in ClassData.Date
The Attendance is in ClassData.Attendance
(Each Class in ClassData has many other columns beyond Attendance and Date such as ClassData.Absences.)
For any date and class there will be only one attendance number.
I have seen a lot of code to export data from Access to Excel, but all that I’ve seen is for whole tables of data. I haven't seen a clear way to bring in a single cell via user defined function. There is supposedly something on the Code Bank -- http://www.dbforums.com/microsoft-ac...ml#post6222281 -- but I have been unable to figure out where.
As a secondary consideration (and significantly farther down the list), I'd like this user defined function (and whatever code -- VBA or other) to be fast/efficient because eventually this table will have tens of thousands of entries as I expand to additional years and schools.
I apologize if I put this in the wrong forum -- it could have easily gone into the Excel forum as it encompasses both Excel and Access. I also apologize that, as a novice here, I probably documented or asked for most things here incorrectly. But I’ve been struggling with this for a month and am finally at the point I definitely require help.
Public Function ImportClassData(ByVal SomeDate As Date, ByVal SomeClass As String) As Variant
Const c_SQL As String = "SELECT ClassData.Attendance " & _
"FROM ClassData " & _
"WHERE (ClassData.Date=#@D#) AND (ClassData.Class='@C');"
Const c_Connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=U:\Access\schooldata.mdb;User Id=Admin;Password=''"
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strDate As String
Dim strSQL As String
Dim var As Variant
strDate = Format(SomeDate, "mm/dd/yyyy")
strSQL = Replace(Replace(c_SQL, "@D", strDate), "@C", SomeClass)
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open strSQL, cnn
If rst.BOF = False Then
var = rst.GetRows
ImportClassData = var(0, 0)
Set rst = Nothing
Set cnn = Nothing