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

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

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