Duplicate Project in VBE
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 |
All times are GMT +1. The time now is 01:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com