Frothingslosh
Premier Pale Stale Ale
- Local time
- Today, 02:30
- 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:
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.)
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.)