Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace ActiveCell w/Query Results?
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace VLOOKUP results with data | Excel Worksheet Functions | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
how do i automatically replace formula with results | Excel Worksheet Functions | |||
Find & Replace - view results | Excel Discussion (Misc queries) | |||
Web Query Sleep for until results come | Excel Worksheet Functions |