Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 1st, 2004, 08:01 AM
Authorized User
 
Join Date: Mar 2004
Location: , , United Kingdom.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL2000 and Excel2000 VBA UDF

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?
Reply With Quote
  #2 (permalink)  
Old June 1st, 2004, 12:00 PM
Authorized User
 
Join Date: Feb 2004
Location: Sacramento, CA, USA.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old June 1st, 2004, 12:22 PM
Authorized User
 
Join Date: Mar 2004
Location: , , United Kingdom.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old June 1st, 2004, 12:28 PM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)?
Reply With Quote
  #5 (permalink)  
Old June 1st, 2004, 12:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old June 1st, 2004, 12:54 PM
Authorized User
 
Join Date: Mar 2004
Location: , , United Kingdom.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel 2007 vba UDF DavidReese BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 1 February 24th, 2009 01:10 AM
UDF references DavidReese BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 0 November 2nd, 2008 11:04 AM
trying to run an udf miguel.ossa SQL Server 2005 7 July 22nd, 2007 11:12 AM
how can i execute SQL in UDF vinod_mnr SQL Server 2000 1 March 18th, 2005 12:51 PM
UDF: allowed in one DB but not another??? ea SQL Server 2000 2 January 18th, 2005 03:14 PM



All times are GMT -4. The time now is 12:40 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.