+ Reply to Thread
Results 1 to 3 of 3

Reference Class Module in Access from Excel

  1. #1
    reclusive.monkey@gmail.com
    Guest

    Reference Class Module in Access from Excel

    I have created a custom function in Access. I used the Class Module
    method as I wanted it to be accessible to Excel. I have this in a class
    module in access;
    Code:

    Option Compare Database

    Public Property Get strFinancialYear() As String
    myDate = Date - 1
    myYear = Year(myDate) - myYearModifier ' Get the year for
    yesterday, with our modifier
    If (MONTH(myDate)) >= 4 Then ' If we are after april, this year is
    first part of FN year
    strFinancialYear = myYear & "/" & Right(myYear + 1, 2)
    Else
    strFinancialYear = myYear - 1 & "/" & Right(myYear, 2)
    End If
    End Property

    Public Function FinancialYear() As String
    FinancialYear = Me.strFinancialYear
    End Function

    This is then called from a module in access;

    Function ReportingYear() As String
    Dim myReportingYear As New CFinancialYear
    ReportingYear = myReportingYear.strFinancialYear
    End Function

    I then have

    ReportingYear()

    In a query field to give "2005/06". This works fine. However, when I
    try to access this same query from Excel, I get the following error;

    [Microsoft][ODBC Microsoft Access Driver] Undefined function
    'ReportingYear' in expression

    In my references dialog in Excel, I have ticked;

    Visual Basic for Applications
    Microsoft Excel 10.0 Object Library
    Microsoft Forms 10.0 Object Library
    Microsoft Access 10.0 Object Library


    I cannot find a reference for ODBC anywhere and I have tried ticking as
    many references which might seem to be relevant, but I am getting
    nowhere. Can anyone see what I am doing wrong as I don't seem to be
    able to get anywhere on this I have been trying to get this working for
    months now and its becoming very frustrating.


  2. #2
    K Dales
    Guest

    RE: Reference Class Module in Access from Excel

    No need to add a reference for ODBC; you are already using ODBC (when you
    query Access). In fact, the error you are getting is being sent from the
    ODBC driver. I know the reason but no easy solution. I assume you are using
    either MSQuery or ADO to get your Access data; both of these convert the
    query to SQL. Now, while your Access project understands your functions the
    SQL interpreter doesn't, thus the error. The easiest thing I can think of is
    to omit that field from your query and duplicate it in an Excel function.
    But that can lead to frustration down the road if you change it 5 years from
    now and forget that it also needs to be changed in Excel. A potentially
    better method would be to add a reference to MSAccess to your Excel project
    and actually open the database and manipulate it directly via automation.

    There maybe a way of using custom functions in an external database query
    that I don't know of so you may need to research this further.
    --
    - K Dales


    "reclusive.monkey@gmail.com" wrote:

    > I have created a custom function in Access. I used the Class Module
    > method as I wanted it to be accessible to Excel. I have this in a class
    > module in access;
    > Code:
    >
    > Option Compare Database
    >
    > Public Property Get strFinancialYear() As String
    > myDate = Date - 1
    > myYear = Year(myDate) - myYearModifier ' Get the year for
    > yesterday, with our modifier
    > If (MONTH(myDate)) >= 4 Then ' If we are after april, this year is
    > first part of FN year
    > strFinancialYear = myYear & "/" & Right(myYear + 1, 2)
    > Else
    > strFinancialYear = myYear - 1 & "/" & Right(myYear, 2)
    > End If
    > End Property
    >
    > Public Function FinancialYear() As String
    > FinancialYear = Me.strFinancialYear
    > End Function
    >
    > This is then called from a module in access;
    >
    > Function ReportingYear() As String
    > Dim myReportingYear As New CFinancialYear
    > ReportingYear = myReportingYear.strFinancialYear
    > End Function
    >
    > I then have
    >
    > ReportingYear()
    >
    > In a query field to give "2005/06". This works fine. However, when I
    > try to access this same query from Excel, I get the following error;
    >
    > [Microsoft][ODBC Microsoft Access Driver] Undefined function
    > 'ReportingYear' in expression
    >
    > In my references dialog in Excel, I have ticked;
    >
    > Visual Basic for Applications
    > Microsoft Excel 10.0 Object Library
    > Microsoft Forms 10.0 Object Library
    > Microsoft Access 10.0 Object Library
    >
    >
    > I cannot find a reference for ODBC anywhere and I have tried ticking as
    > many references which might seem to be relevant, but I am getting
    > nowhere. Can anyone see what I am doing wrong as I don't seem to be
    > able to get anywhere on this I have been trying to get this working for
    > months now and its becoming very frustrating.
    >
    >


  3. #3
    reclusive monkey
    Guest

    Re: Reference Class Module in Access from Excel

    Thanks for the reply K, thats helped my understanding of the problem. I
    think the best route to go down would be to replicate the function in
    Excel. The exporting to excel is a seperate function from anything else
    used in the database, so it should work out better than the method I am
    currently using. Again, thanks for the reply.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1