Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with ADO and 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with ADO and VBE
Your record set object still exists.Try
Set RS = nothing -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with ADO and VBE
I meant
set RunQuery = nothing -----Original Message----- Your record set object still exists.Try Set RS = nothing -----Original Message----- 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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with ADO and VBE
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. "Seth" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|