Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2014

    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.

    An example is below:

    Date Class Attendance
    1/7/2014 Albertson 22
    1/7/2014 Nomura 26
    1/7/2014 Reynolds 25
    1/7/2014 Maplebaum 32
    1/8/2014 Albertson 25
    1/8/2014 Nomura 22
    1/8/2014 Reynolds 33
    1/8/2014 Maplebaum 23
    1/9/2014 Albertson 30
    1/9/2014 Nomura 23
    1/9/2014 Reynolds 27
    1/9/2014 Maplebaum 28
    1/10/2014 Albertson 28
    1/10/2014 Nomura 34
    1/10/2014 Reynolds 33
    1/10/2014 Maplebaum 25

    I have seen a lot of code to export data from Access to Excel, but all that Ive 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 -- -- 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 Ive been struggling with this for a month and am finally at the point I definitely require help.

    Thank you for your help on this.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    You could use a function such as:
    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
        cnn.Open c_Connect
        Set rst = New ADODB.Recordset
        rst.CursorLocation = adUseServer
        rst.Open strSQL, cnn
        If rst.BOF = False Then
            var = rst.GetRows
            ImportClassData = var(0, 0)
        End If
        Set rst = Nothing
        Set cnn = Nothing
    End Function
    Have a nice day!

  3. #3
    Join Date
    Jan 2014

    Thank you


    Thank you so much for your solution: it worked perfectly. And after a month of struggling with this on my own. Greatly appreciated.


  4. #4
    Join Date
    Mar 2009
    Provided Answers: 15
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts