ThomasFoege
asked on
Excel Query Function
Hello EE!
I'm trying to, with the previous help i got here, to create a function that can do a query to an access database. However I'm not certain about datatypes and the procedure for using functions, but so far I've come up with this:
The query function:
And then the code using it:
However, i get an error 91, Object variable or with block variable not set on the last line of the code
I cannot seem to figure out what I'm doing wrong. I hope one of you out there have an idea to my problem
Thanks in advance!
I'm trying to, with the previous help i got here, to create a function that can do a query to an access database. However I'm not certain about datatypes and the procedure for using functions, but so far I've come up with this:
The query function:
Private Function DoQuery(strQuery As String) As Range
' Connection Details
Dim cnn As ADODB.Connection, rst As ADODB.Recordset
Dim strPathToDB As String, strFormula As String, i As Long
Dim wks As Worksheet
Dim lngNextNum As Long, lngRow As Long, lngCol As Long
Dim varData
Dim strTable As String, strField1 As String, strField2 As String
' Path to database
strPathToDB = Worksheets("Menu").Range("D4").Value
strTable = Worksheets("Menu").Range("D5").Value
' Connect
Set cnn = New ADODB.Connection
With cnn
.CursorLocation = adUseServer
.ConnectionTimeout = 500
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPathToDB & ";"
.Open
.CommandTimeout = 500
End With
' Do query
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.Open strQuery, cnn, adOpenStatic, adLockPessimistic, adCmdText
If Not .EOF Then
DoQuery.CopyFromRecordset rst
End If
.Close
End With
' Clean up
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End FunctionAnd then the code using it:
Public Sub GetValidationData()
strPathToDB = Worksheets("Menu").Range("D4").Value
strTable = Worksheets("Menu").Range("D5").Value
q_db_year = Worksheets("Menu_DB_Definition").Range("db_year").Value
q_db_spend = Worksheets("Menu_DB_Definition").Range("db_spend").Value
Results = DoQuery("SELECT [" & strTable & "].[" & q_db_year & "], Sum([" & strTable & "].[" & q_db_spend & "]) AS SumSpend FROM [" & strTable & "] GROUP BY [" & strTable & "].[" & q_db_year & "];")
ActiveSheet.Cells(4, "G").CopyFromRecordset Results
End SubHowever, i get an error 91, Object variable or with block variable not set on the last line of the code
ActiveSheet.Cells(4, "G").CopyFromRecordset ResultsI cannot seem to figure out what I'm doing wrong. I hope one of you out there have an idea to my problem
Thanks in advance!
ASKER
Ah I see, using you functions i run into a runtime error 3704, application-defined or object defined error? Is that the references again?
Possibly - I should have specified the recordset type for the function:
as that could cause problems if you have a DAO reference set too.
Private Function DoQuery(strQuery As String) As ADODB.Recordset
' Connection Details
Dim cnn As ADODB.Connection, rst As ADODB.Recordset
Dim strPathToDB As String, strFormula As String, i As Long
Dim wks As Worksheet
Dim lngNextNum As Long, lngRow As Long, lngCol As Long
Dim varData
Dim strTable As String, strField1 As String, strField2 As String
' Path to database
strPathToDB = Worksheets("Menu").Range("D4").Value
strTable = Worksheets("Menu").Range("D5").Value
' Connect
Set cnn = New ADODB.Connection
With cnn
.CursorLocation = adUseServer
.ConnectionTimeout = 500
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPathToDB & ";"
.Open
.CommandTimeout = 500
End With
' Do query
Set DoQuery = New ADODB.Recordset
With DoQuery
.CursorLocation = adUseClient
.Open strQuery, cnn, adOpenStatic, adLockPessimistic, adCmdText
End With
cnn.Close
Set cnn = Nothing
End Functionas that could cause problems if you have a DAO reference set too.
ASKER
I'm getting same error, i got these references:
VB for applications
Microsoft excel 14
OLE Automation
Microsoft office 14
Microsoft ActiveX Data Objects 6.0
Microsoft Office 14
VB for applications
Microsoft excel 14
OLE Automation
Microsoft office 14
Microsoft ActiveX Data Objects 6.0
Microsoft Office 14
Where are you getting the error?
ASKER
Set Results = DoQuery("SELECT [" & strTable & "].[" & q_db_year & "], Sum([" & strTable & "].[" & q_db_spend & "]) AS SumSpend FROM [" & strTable & "] GROUP BY [" & strTable & "].[" & q_db_year & "];")
If Not Results.EOF Then ActiveSheet.Cells(4, "G").CopyFromRecordset ResultsI checked with a messagebox that the query is correct. I think the error is in the If Not line?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That works perfectly!
Is there any way to call this function from another sheet or should i copy it to all sheets i use it in?
Is there any way to call this function from another sheet or should i copy it to all sheets i use it in?
You should put the code into a normal module (not a worksheet code module). You can then call it from anywhere in the same project.
ASKER
Woks perfectly as always, what would I do without EE/rorya
Open in new window
then use:
Open in new window