SQL2000 and Excel2000 VBA UDF

luxcs · June 1st, 2004, 08:01 AM

I currently have a database which I'm trying to connect Excel to to automatically fill in some work reports.
I need the UDF to take in 3 variables - two of which are for the WHERE section of the SQL statement to match the result, the third is for selecting which field/column is req'd. The database and tablename are hardcoded.
The database will only ever have one match for the two variables - i.e. only one ref with a particular date, so when I ask for a particular field to be returned there will only be a single answer (which could be a string, date, time or integer, depending on what I request).

It keeps returning #VALUE!

Code:

Public Function ANITEDataExtract(d As String, f As String, r As String) As String

    ' Create a connection object.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection

    ' Provide the connection string.
    Dim strConn As String

    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"

    'Connect to the Pubs database on the local server.
    strConn = strConn & "DATA SOURCE=ops-serv;INITIAL CATALOG=ANITEFigures;"

    'Use an integrated login.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"

    'Now open the connection.
    cnPubs.Open strConn

    ' Create a recordset object.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
    Dim sqlStr As String
    sqlStr = "SELECT " & r & " FROM S04 WHERE Date='" & d & "' AND FltNum='" & f & "'"

    With rsPubs
            ' Assign the Connection object.
            .ActiveConnection = cnPubs
            ' Extract the required records.
            .Open (sqlStr)
            ' Copy the records into cell A1 on Sheet1.
            ANITEDataExtract = Str(rsPubs)

            ' Tidy up
            .Close
    End With

    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing

End Function

Can anyone please help me solve this problem?

virtualdave · June 1st, 2004, 12:00 PM

Don't you need a closing semicolon? Is this set up as an ODBC data source where you could test your SQL in either Access or MS Query?

Dave

luxcs · June 1st, 2004, 12:22 PM

Quote:

quote:Originally posted by virtualdave
Don't you need a closing semicolon? Is this set up as an ODBC data source where you could test your SQL in either Access or MS Query?

Dave

Put in closing semicolon. Missed that one. No change though.
Used Get External Data in Excel and managed to connect and pull in entire DB.
This was working originally when I had it as public Sub DataExtract which just did a SELECT * FROM S04.
Its just stopped working properly since I tried to alter it to a Function and have the SQL statement dynamic.

Chris

helentoomik · June 1st, 2004, 12:28 PM

Does the function work if you call it from another sub, and only fail when you use it as a UDF (i.e. call it from worksheet cell)?

chrislepingwell · June 1st, 2004, 12:29 PM

Your problem is that you are returning the entire recordset as if it were a String

Code:

ANITEDataExtract = Str(rsPubs)

try

Code:

ANITEDataExtract = rsPubs.Fields(1).Value

or keep it as a Sub and use

Code:

Range("A1").copyFromRecordset rsPubs

Personally, I'd never use a UDF to connect to a Database. It can totally kill your spreadsheet when it tries to update on a recalc.

HTH

Chris

There are two secrets to success in this world:
1. Never tell everything you know

luxcs · June 1st, 2004, 12:54 PM

heletoomik -:

couldn't get it to run at the moment from another sub either

chrislepingwell -:

Altered code to use rsPubs.Fields(1).Value. No change yet.

The reason for it being a UDF is that is will be used in a season report. The function will take three variables (all of which are in the excel sheet and alter for each required piece of info).

I was planning on altering the code (once I get this test working) to open the DB connection on worksheet open and close it on close (to save hundreds of db connections opening and closing).

It would be almost impossible for me to hardcode individual cells into a sheet as the format of the reportt varies almost daily as do the parameters passed.