VBE Issue

[Seth]

OK,

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

 

[Fiona]

Hi Seth,

This stuff is way beyond me but if it helps, I noticed you are
creating 2 instances of a workbook,
1)Function RunQuery()As Workbook
2)Set Wkb = Workbooks.Add

you probably want the new copy of the workbook to stay open to work on
?
so you only want the first workbook opened to close ?

I would try "workbooks(1).close" right after the "RS.close"

If this doesn't help I hope it at least sets you off in the right
direction.