Access Data in Excel Cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bernie Hunt - 28 Mar 2006 21:48 GMT

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

Reply to this Message

Mikeopolo - 28 Mar 2006 23:24 GMT

You can do this with a custom function; prepare the code, save it as a
XLA, load the add-in on the user's computer, then it's available fo
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
Mik

Reply to this Message

Bernie Hunt - 29 Mar 2006 00:22 GMT

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

> 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
[quoted text clipped - 43 lines]
> Regards
> Mike

Reply to this Message

Mikeopolo - 29 Mar 2006 01:09 GMT

Hi, not sure about documentation, and I can't remember how I latched o
to my first example, but I did a search on Google, for example yo
could use oConn.Open as your search term, although I didn't kno
anything like that at the time.

The custom function only does one process per function, so I've create
a number of functions all using different SQL statements to retur
different things. They all need different parameters.

They are however all in the one XLA file.

Regards
Mik

Reply to this Message