Excel file locked after finishing import

[kalakeli]

Hi there .. any help highly appreciated. Maybe I am just too blind to see it and it is very simple - I actually think it should be.

I have written an import program that opens an excel file, displays the sheets and lets the user choose one. The Excel sheet is quite complex so I had to write my own procedure.
The program then checks the plausibility of the data in the Excel worksheet and if everything is correct, imports. If not, it shows a message, writes a textfile, asks the user to fix the sheet, and stops. BUT the file is still locked. And I cannot figure out how to close the worksheet, workbook, excel application so that it actually works. I always get "file is locked by ..."

I created a couple of variables
Public objExcel As Excel.Application
Public objBook As Excel.Workbook
Public objSheet As Excel.Worksheet

then ask whether for example objBook exists and simply say: objBook.Close

Could someone please point out to me how to do it? :banghead:

 

[Galaxiom]

The most common problem in this kind of code and problem is referring to the workbook other than via the Workbook object.

Show us the code you are using to instantiate and manipulate the application and workbook.

 

[kalakeli]

Thanks for the quick reply.

As I wrote I have variables

I open the workbook when I ask the users to choose the needed worksheet.
Public Function readxlsSheets(fn) As String
Dim xlsFile As String
Dim reStr As String

xlsFile = fn
reStr = ""

' find an open Excel
Set objExcel = New Excel.Application
Set objBook = objExcel.Workbooks.Open(xlsFile)

If objExcel.Worksheets.count > 0 Then
For Each objSheet In objExcel.Worksheets
reStr = reStr & objSheet.Name & ", "
Next objSheet
End If

readxlsSheets = Left(reStr, Len(reStr) - 2)

End Function

Then, when I later need it, I always check for its existance and then continue..

' check to see whether they exist already
ok = IsNothing(objExcel) 'isNothing is a simple method checking for Nothing
If (ok) Then
Set objExcel = New Excel.Application
End If

ok = IsNothing(objBook)
If (ok) Then
Set objBook = objExcel.Workbooks.Open(fn)
End If

' the given sheet
Set objSheet = objBook.Worksheets.item(sheetname)

Do you something here?

 

[pwbrown]

Instead of objBook.Close
Try :
objBook.ActiveWorkbook = Nothing
objBook.Quit
Set objBook = Nothing

 

[Solo712]

Thanks for the quick reply.

As I wrote I have variables

I open the workbook when I ask the users to choose the needed worksheet.
Public Function readxlsSheets(fn) As String
Dim xlsFile As String
Dim reStr As String

xlsFile = fn
reStr = ""

' find an open Excel
Set objExcel = New Excel.Application
Set objBook = objExcel.Workbooks.Open(xlsFile)

If objExcel.Worksheets.count > 0 Then
For Each objSheet In objExcel.Worksheets
reStr = reStr & objSheet.Name & ", "
Next objSheet
End If

readxlsSheets = Left(reStr, Len(reStr) - 2)

End Function

Then, when I later need it, I always check for its existance and then continue..

' check to see whether they exist already
ok = IsNothing(objExcel) 'isNothing is a simple method checking for Nothing
If (ok) Then
Set objExcel = New Excel.Application
End If

ok = IsNothing(objBook)
If (ok) Then
Set objBook = objExcel.Workbooks.Open(fn)
End If

' the given sheet
Set objSheet = objBook.Worksheets.item(sheetname)

Do you something here?

You might want to try

Set objBook = objExcel.Workbooks.Open(fn,,True)

to open the Excel file as read-only. Seems your app does not need to write into the sheets.

Best,
Jiri

 

[kalakeli]

Thanks to you all for your replies.
True, Jiri, I am not writing into them, so your idea works fine.
With pwbrown's answer I also figured out how to do it.

Setting all objects to Nothing seems to work just fine.

Set objSheet = Nothing
objBook.Close
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing

Thanks a lot again =)