ADO - accessing excel from Access

[JC]

I can access Excel spreadsheets with the following code;
however, once the code completes, the Excel spreadsheets
are LOCKED and remain locked until I reboot. I have
placed comments and questions within the code snippet
below. I am almost positive that I am either opening the
spreadsheets incorrectly or not closing them...
The code is short and any help would be appreciated.
Thank you

Sub xlimport()

Dim fs
Dim xlObj As Object
Dim xlWB As Workbook
Dim sht As Worksheet
Dim dbs As DAO.Database
Dim Pimport As DAO.Recordset
Dim i As Integer
Dim NoOfFiles As Integer
Dim FileArray() As String
Dim PathStr As String


PathStr = "C:\documents and settings\ridgewayjc\My
Documents\access\mcm\import files"

Set dbs = CurrentDb
Set Pimport = dbs.OpenRecordset("tblPImport",
dbOpenDynaset)
Set fs = Application.FileSearch

With fs
.LookIn = PathStr
.FileName = "*.xls"
NoOfFiles = .Execute(SortBy:=msoSortbyFileName,
SortOrder:=msoSortOrderAscending)
If NoOfFiles > 0 Then
Set xlObj = New Excel.Application
ReDim FileArray(1 To NoOfFiles)
For i = 1 To NoOfFiles
FileArray(i) = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

For i = 1 To NoOfFiles
xlObj.Application.Workbooks.Open FileArray(i)
Set xlWB = xlObj.Application.Workbooks(1)
Set sht = xlObj.ActiveWorkbook.Sheets(1)
xlObj.Visible = False

' I have the spreadsheet open and the recordset open
so... I
' read values from some of the cells and place them
into the
' proper fields of the table (PImport)


' at this point, since I am through with this
particular spreadsheet
' shouldn't I have to close the spreadsheet?
' Do I need to close all 3 - xlObj and xlWB and sht?
' If so, HOW do I do that?

Next i

End Sub

 

[John Nurick]

Hi JC,

It looks as if you're opening one workbook each time you go through your
loop, and never closing any of them.

So just before Next statement at the end of the loop, close the workbook
with
Set sht = Nothing
xlWB.Close False 'close without saving changes
Set xlWB = Nothing

Then at the end of the procedure, close Excel. Since it's possible that
other workbooks have been opened (e.g. by add-ins or by autoexec code in
the workbooks you have explicitly opened) it's a good idea to make sure
that there are no open workbooks before closing, with something like:

With xlObj
Do While .Workbooks.Count > 0
.Workbooks(.Workbooks.Count).Close False
Loop
.Quit
End With
Set xlObj = Nothing

 

[JC]

Thanks John...that is what I needed to know and it works
perfectly.

- jc -