Excel File Stays Locked after Closing It (1 Viewer)

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 03:14
Joined
Oct 17, 2012
Messages
3,276
Basically, I'm running a routine that verifies the headers of a spreadsheet, and if they match what is expected, it will import the spreadsheet. All of that is working, but I'm getting an odd error, in that the spreadsheet it self remains locked afterward until the user has logged off for the day.

Here is the code I'm using:

Code:
Public Function funImportMain() As Integer
 
' ************************************************************
' Created by       : (Frothingslosh)
' Parameters       : None
' Result           : Integer
' Date             : 5/28/13
' Remarks          : This function imports the selected spreadsheet and returns a value based upon result.
'                    0 - Miscellaneous/Untrapped Error
'                    1 - Verification Failure
'                    2 - Import Process Failure
'                    3 - Cancelled by User
'                    4 - Successful
' Changes          :
' ************************************************************
 
On Error GoTo funImportMain_Err
 
    Dim strProcName As String                       'Procedure Name
    Dim strFilePath As String                       'Path of the file to import.
    Dim strFileName As String                       'Name of the file to import.
    Dim strPath As String                           'Import file's path and file name.
    Dim strCompany As String                        'The company name for the file being imported.
    Dim strLastPath As String                       'The path used last time.
    Dim strNewFilePath As String                    'strPath + "*.xls*
    Dim objWorksheet As Worksheet                   'Worksheet to be imported.
    Dim objWorkbook As Workbook                     'Workbook to be imported from.
    Dim strMsg As String                            'Message prompt used for MsgBox function
    Dim intVerified As Integer                      'Result of verification routine.
 
    strProcName = "funImportMain"
 
    'First off, get the name and path of the file to import.
    strLastPath = funGetTextOpt(1, 1)
    strPath = funOpenFile("Please select file to import", strLastPath, fdExcel)
 
    'Verify user didn't cancel.
    If strPath = "cancel" Then
        funImportMain = 3                           'Return cancel code.
        Exit Function
    End If
 
    'First up, verify that this file meets import requirements.
    Set objWorkbook = Excel.Workbooks.Open(strPath)
    Set objWorksheet = objWorkbook.Sheets(1)
 
    'Verify existance of necessary headers.
    intVerified = funVerifySpreadsheet(objWorksheet)
 
    'Report test value *REMOVE WHEN COMPLETE*
    MsgBox "Verification code: " & intVerified
 
    'Now break it down into the path and the file name.
    strFilePath = funReturnFolderName(strPath)
    strFileName = funReturnFileName(strPath)
 
    'Return dummy value until full implementation.
    funImportMain = 1
 
funImportMain_Exit:
 
    If Not objWorkbook Is Nothing Then
        'Worksheet is still open - close and release it.
        Workbooks.Close
        Set objWorksheet = Nothing
        Set objWorkbook = Nothing
    End If
    Exit Function
 
funImportMain_Err:
 
    MsgBox "Error occurred" & vbCrLf & vbCrLf & _
    "In Function:" & vbTab & strProcName & vbCrLf & _
    "Err Number: " & vbTab & Err.Number & vbCrLf & _
    "Description: " & vbTab & Err.Description, vbCritical, _
    "Error in " & Chr$(34) & strProcName & Chr$(34)
    Resume funImportMain_Exit
 
End Function

Most of the functions I'm calling have pretty obvious returns. The other one, funVerifySpreadsheet, is a bit more complicated, but it's working as expected. In that one, you pass a spreadsheet variable, it compares the headers in that spreadsheet against what's expected, and returns a numeric code corresponding to the results.

I can't for the life of me figure out why it won't release the selected spreadsheet once it's done, so any help with this would be massively appreciated. (And yes, obviously this thing is unfinished. It's eventually going to drive an entire import process; I just was trying to figure out how to release the spreadsheets before moving on.)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:14
Joined
Jan 20, 2009
Messages
12,849
Change Workbooks.Close to objWorkbook.Close

BTW. One thing I never understand is why some developers include unnecessary guff in their comments.

"First up", "first off" (how are they both first?). "Now" (well when else would it be?)
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 03:14
Joined
Oct 17, 2012
Messages
3,276
Change Workbooks.Close to objWorkbook.Close

That was actually what I had at first. Didn't work either, which is what led me to trying things like Workbooks.Close.

BTW. One thing I never understand is why some developers include unnecessary guff in their comments.

"First up", "first off" (how are they both first?). "Now" (well when else would it be?)

1) That's just how I talk.
2) I cut and pasted a few times moving things around. That has since been cleaned up.
3) That's how I talk.

And seriously, it takes me less than a second to type those 'excess words' out, and they're stripped out of the compiled code when I turn it into an .accde anyway. Better that than the method of the guy I replaced - not commenting anything!

I can post the completed procedure for your critique tomorrow when I get to work if you'd like. ;) I'm honestly thinking the locking issue has something to do with having passed the worksheet to funVerifySpreadsheet.
 
Last edited:

Users who are viewing this thread

Top Bottom