P
pkillebrew
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=MS
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
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=MS
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