ExcelBanter - Replace ActiveCell w/Query Results?

PK

You should think of functions as only being able to return a value. When
you try to put a querytable in the same cell as the function I think it
confuses excel and returns its standard #Value error. What I don't get is
why it works with a cell reference. It seems that it shouldn't.

I would do this one of two ways: First, use DAO where all the querying is
behind the scenes and function merely gets the proper data from Access and
returns it to the cell. No querytable, no refreshing.

The second way would be to create a query with a cell based parameter. You
would but 8 in another cell and the query would refresh automatically using
8 as the criteria. The downside to this is that the query table is always
in the same cell. However, you could hide this query table and put your
user-defined function that simply refers to the querytable. Ex: You query
table data is in H2 and column H is hidden. In some other cell, you enter 8
which updates the query with that as criteria. In yet a third cell, you put
=pktest() which would look like this

Function pktest()
pktext = Range("H2").Value
End Function

That's not as clean and nice as pktest(8), but it would solve the value
problem.

A variant of that is to make the query table without a parameter, change the
query table in your function based on the argument, then get the value, so
=pktest(8) would look like this

Function pktest(pkvar as Double) as Double
With Sheet1.QueryTables(1)
.CommandText = Replace(.CommandText, more code to change the sql
.Refresh False
End With
pktest = Sheet1.Range("H2").Value
End Function

I don't know if refreshing the querytable inside a function would cause a
problem, but if you like that method it's worth a shot.

Bonus method: Use the worksheet change event. You simply enter 8 in a cell
and it creates a querytable in that cell - similar to what you're doing now,
but without the function.

If were doing this, I would use the first DAO option.

Is that enough options for you? If you like one of those, post back and I
can help you with the code for it. If you don't like any of them, tell me
why and I'll see if I can come up with something better for you.

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

"pkillebrew" wrote in message
...

I'm as much of a noob as there can be, so please feel free to tell me
that I'm an idiot if that is the case. I'm trying to write a function
that will query an access database and return a value, based on a
number submitted in the function. The function would be entered into
excel as "=pktest(8)" and would then query a database and display a
name. I'm sure that I have made this more complicated than it is, but
can anybody help?

It works fine if I use a specific cell destination, such as
Destination:=Range("B1")

But when I try to use ActiveCell as the destination, I just get a
#VALUE!

Function pktest(pkvar)
Call pktest2(pkvar)
End Function

Sub pktest2(pkvar)
Dim pkvar2 As Integer
pkvar2 = 8
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=ccg;DBQ=C:\ccg.mdb;DriverId=25;FIL=M S
Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=ActiveCell)
CommandText = "SELECT firstname FROM instructors WHERE
instructorid=" & pkvar
Name = "Query from ccg_1"
FieldNames = False
RowNumbers = False
FillAdjacentFormulas = False
PreserveFormatting = False
RefreshOnFileOpen = False
BackgroundQuery = True
RefreshStyle = xlInsertDeleteCells
SavePassword = True
SaveData = True
AdjustColumnWidth = False
RefreshPeriod = 0
PreserveColumnInfo = False
Refresh BackgroundQuery:=False
End With
End Sub

Thanks

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/