Excel VBA - Password prompt after Form Show and Import Data

Question

I have this macro that is password protected and shows a form. Recently I added code to import data from excel and every time I close it asks me for a password - I can cancel through it but I'd like to make it go away, I see no plausible reason why it would come.

I've separated out the few lines that causes this problem

Sub a()    

UserForm1.Show
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0" & _
    ";Data Source=" & "C:\Book1.xls" & _
    ";Extended Properties='Excel 8.0; IMEX=1'" & _
    ";Persist Security Info=False"
Set objRSGlobal = CreateObject("ADODB.Recordset")
objRSGlobal.Open "select * from [Sheet1$]", strConnString
objRSGlobal.Close
Set objRSGlobal = Nothing
End Sub

This goes into Module1. I have a blank form called UserForm1. I lock the code (for viewing as well) using a password and save everything in Book1.xls.

Now I open the file, do an Alt+F8 to run the macro, close the form and then close the file the password prompt comes up. I can't understand why it comes up and how to make it go away.

Thanks in advance!!

Note - for the record, this also happens if I just show the form and do an Import Data via the UI (Data > Import External Data > Import Data)

• Update - it looks like its happening only when I import from the same file I have open.

Answer 1

This looks like the problem described in Microsoft Knowledge Base Article KB280454 http://support.microsoft.com/kb/280454

I encountered this problem working with .NET and COM interop. The article advises ensuring COM references are released. I was unable to do this successfully in my case. I tried delegating commands from Excel to a non-VBA-password protected workbook which reduced the occurrence and finally set up a process to kill my app (with Xtreme Prejudice, using a second process). Neither of these seem appropriate in the case of this question but a similar kludge may be required.

Answer 2

http://www.officekb.com/Uwe/Forum.aspx/excel-prog/40862/VBA-project-does-not-close-when-workbook-is-closed

looks like a google desktop issue.

Answer 3

I had the same issue, and it is related to the Excel/Jet queries from open Excel Workbooks (including the current workbook). The following Microsoft kb article describes the problem.

The issue seems to be fixed in Excel 2010, although that did not help me (as my clients are still using Excel 2007). One way to do it is to save the data into another workbook, query it there and then copy the result back in to the original workbook, but that is a bit cumbersome.