Access Data in Excel Cell

#1 03-28-2006, 03:55 PM

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

Mikeopolo · #2 03-28-2006, 05:24 PM

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

#3 03-28-2006, 06:25 PM

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

Mikeopolo · #4 03-28-2006, 07:09 PM

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

The custom function only does one process per function, so I've created a number of functions all using different SQL statements to return different things. They all need different parameters. (to make that clearer, a query can be parameterised so that the user types in =Yourfunction(datefrom,dateto,etc etc...,DSN) and your query returns the appropriate value).

They are however all in the one XLA file.

Regards
Mike

Welcome to the Excel Forum

Forum Statistics