Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Project Password prompt on Exit
I've read several posting about this problem, and all of them seem to have
been resolved by removing Google Desktop. The problem is that I do not have Google Desktop. Here is what I have found will make this problem occur. In my Excel file, I have a VBA form, which is displayed, then remove from memory and set to Nothing. After that, I create an ADO Connection Object that is opens a connection to the current Excel file (trying to get around this same issue when using MSQuery, but turns out it did not matter). The connnection is then closed, the Object set to Nothing. And that's all I have to do for the Project Password prompt to appear when exiting Excel. I am using late binding, so there is no reference set to the ADO library in my project. Any ideas? Thanks, Malcolm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Project Password prompt on Exit
Malcolm Makin wrote: I create an ADO Connection Object that is opens a connection to the current Excel file Not a good idea: BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO) http://support.microsoft.com/default...;en-us;Q319998 Try saving the relevant sheets to a temporary workbook, close it and query the closed workbook e.g. Sub Test() Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim Con As Object Dim rs As Object Dim strCon As String Dim strPath As String Dim strSql1 As String ' Amend the following constants to suit Const FILENAME_XL_TEMP As String = "" & _ "delete_me.xls" Const TABLE_NAME_CURRENT As String = "" & _ "MySheet" ' Do NOT amend the following constants Const CONN_STRING_1 As String = "" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=<PATH<FILENAME;" & _ "Extended Properties='Excel 8.0;HDR=YES'" ' Build connection strings strPath = ThisWorkbook.Path & _ Application.PathSeparator strCon = CONN_STRING_1 strCon = Replace(strCon, _ "<PATH", strPath) strCon = Replace(strCon, _ "<FILENAME", FILENAME_XL_TEMP) ' Build sql statement strSql1 = "" strSql1 = strSql1 & "SELECT Col1 FROM " strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]" ' Delete old instance of temp workbook On Error Resume Next Kill strPath & FILENAME_XL_TEMP On Error GoTo 0 ' Save copy of worksheet to temp workbook Set wb = Excel.Application.Workbooks.Add() With wb ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _ Copy .Worksheets(1) .SaveAs strPath & FILENAME_XL_TEMP .Close End With ' Open connection to temp workbook Set Con = CreateObject("ADODB.Connection") With Con .ConnectionString = strCon .CursorLocation = 3 .Open Set rs = .Execute(strSql1) End With ' <<do something with recordset rs.Close Con.Close End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Project Password prompt on Exit
Malcom,
Did you ever resolve this? I'm having the same Password Prompt problem, but not Google Desktop. Thanks, Jeff "Malcolm Makin" wrote: I've read several posting about this problem, and all of them seem to have been resolved by removing Google Desktop. The problem is that I do not have Google Desktop. Here is what I have found will make this problem occur. In my Excel file, I have a VBA form, which is displayed, then remove from memory and set to Nothing. After that, I create an ADO Connection Object that is opens a connection to the current Excel file (trying to get around this same issue when using MSQuery, but turns out it did not matter). The connnection is then closed, the Object set to Nothing. And that's all I have to do for the Project Password prompt to appear when exiting Excel. I am using late binding, so there is no reference set to the ADO library in my project. Any ideas? Thanks, Malcolm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No prompt on exit in Excel 2003 | Setting up and Configuration of Excel | |||
save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) | |||
Excel - Prompt to save on exit. | Excel Discussion (Misc queries) | |||
Password Prompt for VBA Project Appears After Excel Quits | Excel Programming | |||
Excel 2003 when closing Excel after having opened a project locked for viewing.. password prompt??? | Excel Discussion (Misc queries) |