Thread: Access Data in Excel Cell

I have a customer who has extensive reports build in Excel. Many of the
report pull data from other spreadsheets, but ultimates somewhere the data
is manually input. My task is to automate the input of the data.

Is there a way to enter a formula into a cell that goes off to an Access
database and gets either a value from a record/field combination, or that
runs some code that generates a value?

I know I can do this will code outside of the spreedsheet that will get the
data out of Access and write to a cell in the spreadsheet. But I'd like to
find a way that the user can continue to create and modify their own
reports, rather than me having to re-write/modify code every time they want
a change.

Thanks,
Bernie

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 spreadsheet
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

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@excelforum-nospam.com> wrote
in news:Mikeopolo.25ehzn_1143584702.9714@excelforum-nospam.com:

>
> 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 spreadsheet
> 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
>
>