Form freezes after the connection to Excel is closed

[lzsj6h]

I am having a problem with the Access form freezing on me in Access 2002 after I close my connection to Excel. I have checked and Excel is indeed closing. I have tried this code as both DAO and ADODB and am getting the same thing. I have to physically close Access and open it back up. I did not have this problem in Access 97. The code works fine. This code works great except for the form freezing on me. I am attaching a copy of the ADODB code to see if anyone can see something I am missing.

Private Sub
cmdImport_Click()
Dim strFileName As String
Dim strSheetName As String
Dim xlApp As Object
Dim xlwb As Object
Dim sheet As Object
Dim rs As ADODB.Recordset
Dim db As ADODB.Connection
Dim varXLRowNum As Integer
Dim msg As String

' keep user updated on what's happening
DoCmd.Hourglass True
DoCmd.Echo False, "Importing data from spreadsheet..."

strFileName = Forms!frmTest!cboDrive.Value & ":\CashPay\" & Forms!frmTest!txtFileName.Value
strSheetName = Forms!frmTest!txtSheetName.Value

Set db = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "tblCases", db, , , adCmdTable

Set xlApp = CreateObject("Excel.application&quot
Set xlwb = xlApp.Workbooks.Open(strFileName)
Set sheet = xlwb.sheets(strSheetName)

xlwb.Application.DisplayAlerts = False
varXLRowNum = 10

Do Until sheet.Cells(varXLRowNum, 1).Value = ""

rs.AddNew
rs!Case = sheet.Cells(varXLRowNum, 1).Value
rs!Account_Number = sheet.Cells(varXLRowNum, 2).Value
rs!Initial_Contact_Date = sheet.Cells(varXLRowNum, 3).Value
rs!Claim_Amount = sheet.Cells(varXLRowNum, 6).Value
rs!Prov_Credit_Given_Date = sheet.Cells(varXLRowNum, 8).Value
rs!Prov_Credit_Letter_Date = sheet.Cells(varXLRowNum, 9).Value
rs!Reversal_Letter_Date = sheet.Cells(varXLRowNum, 10).Value
rs!Reversal_To_Account_Date = sheet.Cells(varXLRowNum, 11).Value
rs!Resolution_Of_Claim_Date = sheet.Cells(varXLRowNum, 12).Value
rs!Final_Credit_Date = sheet.Cells(varXLRowNum, 13).Value
rs!Final_Resolution_Letter_Date = sheet.Cells(varXLRowNum, 14).Value
rs!Category = sheet.Cells(varXLRowNum, 16).Value
rs!Affidavit_Request_Date = sheet.Cells(varXLRowNum, 17).Value
rs!Affidavit_Receive_Date = sheet.Cells(varXLRowNum, 18).Value
rs!Affidavit_Followup_Letter_One = sheet.Cells(varXLRowNum, 19).Value
rs!Affidavit_Followup_Letter_Two = sheet.Cells(varXLRowNum, 20).Value
rs!Comments = sheet.Cells(varXLRowNum, 21).Value
rs.Update

varXLRowNum = varXLRowNum + 1
Loop

rs.Close
db.Close
xlwb.Application.DisplayAlerts = True
xlwb.Close SaveChanges:=True
xlApp.Quit

Set xlApp = Nothing
Set sheet = Nothing
Set xlwb = Nothing

msg = MsgBox("Import is complete!", vbOKOnly, "Import Status&quot
DoCmd.Hourglass False
End Sub

Thanks.

 

[Mute101]

Just a thought but try changing;

Set xlApp = Nothing
Set sheet = Nothing
Set xlwb = Nothing

to

Set sheet = Nothing
Set xlwb = Nothing
Set xlApp = Nothing

Dunno if it will work but thats the first thing that jumped out at me.
----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------