Help with ADO and VBE

[Seth]

The following code works well. It uses the ADO object to
draw data from a workbook and place it in another.
Everything is great except this: when the routine is
completed, a copy of the original workbook is placed in
the VBE (I can see it in the explorer window!). The only
way to close it is to close Excel and restart. I thought
that closing the recordset would stop this, but it does
not.

Note: I have significantly abridged the code for
readability - I don't think I am missing any of the key
components, however.

Public Function RunQuery(Sql As String, ConnString As
String, Optional ShtName As String = "Query Result", _
Optional QueryString As String, Optional StartDate As
Date, Optional EndDate As Date) As Workbook

Dim Wkb As Workbook, Wks As Worksheet
Dim x As Integer

Set RS = New ADODB.Recordset
' Open the recordset
RS.Open Sql, ConnString, adOpenForwardOnly,
adLockReadOnly, adCmdText

' In case no records are returned
If RS.EOF Then
MsgBox GLOBALS.msgNORECORDS & vbCrLf &
QueryString, vbExclamation, AppName
RS.Close: Set RS = Nothing
Exit Function
End If

Application.ScreenUpdating = False ' Turn off
screenupdating for faster run
Set Wkb = Workbooks.Add: Set Wks = ActiveSheet

With Wks
.Range("A2").CopyFromRecordset RS
.Name = ShtName
End With

Set RunQuery = Wkb
RS.Close ' Close the recordset - IS THIS THE PROBLEM?
Application.ScreenUpdating = True
' Release object variables
Set Wkb = Nothing: Set Wks = Nothing: Set RS = Nothing
Exit Function

Thanks!

Seth

 

[AA]

Your record set object still exists.Try

Set RS = nothing

 

[Guest]

I meant

set RunQuery = nothing

-----Original Message-----
Your record set object still exists.Try

Set RS = nothing

.

 

[onedaywhen]

Seth, I've seen at least three of your posts on this issue. I too have
see this happen i.e. a workbook's VBA project remains visible in the
VBE Project Explorer after it has been closed. I don't know what
causes it, it hasn't happen to me for a few weeks, but at this time
it's my opinion this is an Excel 'feature' i.e. I don't think it's
specifically your code. Which version are you running? I've been
running four versions recently and can't say which one I saw it on.

BTW in your clean up code you only need to set the RS = Nothing,
because you initialized it with the New keyword.