Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003

    Unanswered: spreadsheet locked after reading with Access

    I am reading multiple spreadsheets into an Access table using ADO. After the code runs, the spreadsheets are still locked and remain that way until I reboot the computer. My comments and questions are commented in the code. The code is short and I would appreciate any help. Thank you
    - jc -

    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
    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

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland

    but politically correct coding requires you to kill ALL the objects, as in
    set pimport = nothing
    set dbs = nothing
    set sht = nothing
    set xlwb = nothing
    set xlObj = nothing
    ...etc for any i missed

    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2003
    Thank you - that did the trick. I had tried different combinations of close and nothing, but somehow I did not hit on the correct combination. Is there any reference that will tell me what to kill and how?
    Thanks again.
    - jc -

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts