Problem with ADO with Parameter Object

[Tod]

I have vbscripts that open a workbook, run it's Sub Main
procedure, then close the workbook. Each workbook is using
ADO to get data from a database. The basic code for the
script looks like this:

Set xl = CreateObject("Excel.Application")
Set CurrentBook = xl.Workbooks.Open
("C:\MyPath\MyWorkbook.xls")
xl.Run "Main"
CurrentBook.Save
CurrentBook.Close
xl.Quit
Set CurrentBook = Nothing
Set xl = Nothing

This works fine. However, two of the workbooks stay open
in memory after the Sub Main has run. Upon investigation,
these are the only two workbooks in the bunch that use the
ADO Parameter object. That code looks something like:

'Where cn is my ADO Connection
cn.Open "Driver={Microsoft Access Driver
(*.mdb));Dbq=C:\DatabasePath\MyDatabase.mdb;"

'Where cm is my ADO cmWith cm
With cm
.ActiveConnection = cn
.CommandText = "qryMyAccessQuery"
.CommandType = adCmdStoredProc
Set pm1 = cm.CreateParameter
("DateField",adDate,adParamInput)
.Parameters.Append pm1
pm1.Value = #4/1/2004#
Set pm2 = cm.CreateParameter
("DateField",adDate,adParamInput)
.Parameters.Append pm2
pm2.Value = #4/30/2004#
End With

'Where rs is my ADO Recordset
rs.Open cm

ActiveSheet.Range("A2").CopyFromRecordSet rs

rs.Close
cn.Close

Set cn = Nothing
Set rs = Nothing
Set pm1 = Nothing
Set pm2 = Nothing

If I open the workbook manually and run the code it works
fine. If I run the script, it opens the workbook and runs
the code, but does not close the workbook.

tod

 

[onedaywhen]

I have vbscripts that open a workbook, run it's Sub Main
procedure, then close the workbook. Each workbook is using
ADO to get data from a database. The basic code for the
script looks like this:

Set xl = CreateObject("Excel.Application")
Set CurrentBook = xl.Workbooks.Open
("C:\MyPath\MyWorkbook.xls")
xl.Run "Main"
CurrentBook.Save
CurrentBook.Close
xl.Quit
Set CurrentBook = Nothing
Set xl = Nothing

This works fine. However, two of the workbooks stay open
in memory after the Sub Main has run.

Are you allowing enough time for

xl.Run "Main"

to complete before executing

CurrentBook.Save
CurrentBook.Close

?

--