Dick,

Thanks for the response, but I'm still a bit confused. I've made the
changes you suggested, but I'm not sure why you are having me define two
variables , strWhere and lngField). I'm assuming strWhere would be the
value I want to use for the value of id, but I don't know where the lngField
fits in. Here is the code I have based on your recommendations. I am
getting an #VALUE error when using the formula:

=GetMySql(B1)

Function GetMySql(strWhere As String, lngField As Long) As Variant

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

myconn.Open "DSN=MySQL TEST"
mySQL = "SELECT number FROM master where id = " & strWhere
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.LockType = adLockOptimistic
myrs.CursorType = adOpenKeyset
myrs.Open , , adCmdTable

myrows = myrs.RecordCount
iRow = 1
iCol = 1
x = 0

With myrs
If .BOF Then GoTo NODATAHERE

'GET FIELD NAMES
For Each AField In .Fields
lString = AField.Name
Cells(iRow, iCol) = lString
iCol = iCol + 1
Debug.Print lString
Next
iRow = iRow + 1
iCol = 1
End With

'COLLECT DATA
myrs.MoveFirst
GetMySql = myrs.Fields(lngField).Value


NODATAHE

myrs.Close
myconn.Close

End Function


Thanks for your help and looking forward to your response.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
Michael

It depends on what you want to return. Do you want to get one field from
one row, or are you planning on using an array formula to return all of
the
fields? If you macro works now, then your almost there. You need to
define
what arguments you're going to pass to the UDF. For instance, you might
do
this

Function GetMySql(strWhere as String, lngField as Long) as Variant

then change this line

mySQL = "SELECT number FROM master where id = " & Range("C2").Value

to

mySQL = "SELECT number FROM master where id = " & strWhere

Then you don't need to loop through all the fields because you're simply
returning one value to one cell. Instead, you would have something like

myrs.MoveFirst
GetMySql = myrs.Fields(lngField).Value

You should only need a MoveFirst, because there should only be one record
in
your recordset.

Now to fill a whole table, you might use the UDF like this

=GetMySql($A1,Column())

With your unique Id in A and your table starting in B2, you can copy this
formula as far left and down as you need. I think this may be
particularly
slow however.

Post back if that doesn't hit the mark.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Michael J. Malinsky" wrote in message
...
I know that VBA can be used to read/write info from/to a mysql database
to/from an Excel worksheet using a Sub routine and putting the results
of
a
query into a recordset then pasting the recordset into a cell on the
sheet.
What I'm wondering is if a UDF can be used in a similar manner. The
query
uses a table with unique values, so having a result with more than one
value
would not occur. This is the code I have that does what I want, but I'd
like this to be in a UDF. I've tried and failed and would greatly
appreciate any assistance.

Private Sub test()

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long
Dim lString As String
Dim iRow As Integer
Dim iCol As Integer

myconn.Open "DSN=MySQL EPACE"
mySQL = "SELECT number FROM master where id = " & Range("C2").Value
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.LockType = adLockOptimistic
myrs.CursorType = adOpenKeyset
myrs.Open , , adCmdTable

myrows = myrs.RecordCount
iRow = 1
iCol = 1
x = 0

Worksheets("Sheet2").Range("A2").Select

With myrs
If .BOF Then GoTo NODATAHERE

'GET FIELD NAMES
For Each AField In .Fields
lString = AField.Name
Cells(iRow, iCol) = lString
iCol = iCol + 1
Debug.Print lString
Next
iRow = iRow + 1
iCol = 1

'COLLECT DATA
.MoveFirst
While Not .EOF
For Each AField In .Fields
Cells(iRow, iCol) = .Fields(x).Value
x = x + 1
iCol = iCol + 1
Next
iRow = iRow + 1
x = 0
iCol = 1
.MoveNext
Wend

End With

NODATAHE

myrs.Close
myconn.Close

End Sub

TIA

--
Michael J. Malinsky