Excel: VBA project isn't closing

[Fabrice Lambert]

Hello,

I'm working on an excel application wich is causing me troubles, when I
close the file (without closing Excel) , the VBA project stay open, wich
interfer with futur opening of the file again.

I'm using ADO objects, wich are in my opinion, not destroyed properly.
Here is a sample of my source code:

Dim conn as ADODBConnection
Dim recs as ADODB.Recordset

Private Sub Workbook_Open()
Set conn = New ADODB.Connection
Set recs = New ADODB.Recordset

'### I'm using the excel file as database ###
ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"Dbq=" &
Application.ActiveWorkbook.FullName & ";" & _
"ReadOnly=False;"
conn.Provider = "MSDASQL"
conn.ConnectionString = ConnectionString
conn.Open
recs.CursorLocation = adUseClient

'### 'MEDECIN is the sheet holding my datas (sorry, french name) ###
recs.Open "SELECT * FROM [MEDECIN$]", conn, adOpenDynamic,
adLockOptimistic
|
|
'### <code reading the recordset> ###
|
|
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
recs.Close
conn.Close
Set recs = Nothing
Set conn = Nothing
End Sub



Next, I'm using a combo box in a sheet named PATIENT, I would like to take
control of it.
I'm able to fill it up:

ThisWorkbook.Sheets("PATIENT").DropDowns.AddItem ("item1")
ThisWorkbook.Sheets("PATIENT").DropDowns.AddItem ("item2")
ThisWorkbook.Sheets("PATIENT").DropDowns.AddItem ("item3")

But I'm unable to select a particular item
ThisWorkbook.Sheets("PATIENT").DropDowns.Value = "item2" '### this
doesn't work ###

Any ideas ?

- F.Lambert

 

[JP]

Shouldn't it be

Dim conn as ADODB.Connection

Or is that just a typo in your post?

--JP

 

[Fabrice Lambert]

yes, it's a typo