Re: Access Data in Excel Cell

Thanks Mike!

Can you point me to any documentation on how to do this or better yet
some key words of what it's called so I can DAGS it?

I need to master how to pass parameters back and forth so I don't have to
write 30 different funtions for each variation of the query they want,
hahahaha.

Thanks,
Bernie

Mikeopolo <Mikeopolo.25ehzn_1143584702.9714@xxxxxxxxxxxxxxxxxxxxx> wrote
in news:Mikeopolo.25ehzn_1143584702.9714@xxxxxxxxxxxxxxxxxxxxx:


You can do this with a custom function; prepare the code, save it as an
XLA, load the add-in on the user's computer, then it's available for
them.

For example (skeleton version of one I have done):

Public Function CompName(DSN As String) As String
'
' Function to read Company name (example only)
'
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String
Dim myResult

Set oConn = CreateObject("ADODB.Connection")
'
' For other connection strings see www.connectionstrings.com
'
oConn.Open ("Driver={Microsoft Access Driver _(*.mdb)};DSN=" & DSN &
";Uid=Admin;Pwd=;")
Set oRS = CreateObject("ADODB.Recordset")
With oRS
CursorLocation = adUseClient
Cursortype = adOpenStatic
End With

sSQL = "Select Yourtable.CompanyName FROM Yourtable"
oRS.Open sSQL, oConn

myResult = oRS.GetRows()

' This line returns the result to the spread***
CompName = myResult(0, 0)

oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing

End Function

The user types =CompName(DSN)
and it returns the company name (in this case)


Regards
Mike



.

Loading