Excel spreadsheet locked after procedure

[morgan23]

I have a procedure where I write a subform to an excel spreadsheet. The procedure works fine but after I have ran it and then try to open the spreadsheet I get a message that is in use. How do I get the lock off of the spreadsheet after my procedure runs? This is what I have:

Private Sub Command6_Click()
Dim oXL As Excel.Application
Dim oWkb As Excel.Workbook
Dim oWks As Excel.Worksheet
Dim rstSubForm As DAO.Recordset
Dim iRow As Integer

Set oXL = CreateObject("Excel.Application")
Set oWkb = oXL.Workbooks.Open("C:\Documents and Settings\weimha\My Documents\WO Notes\Object Log Project\Work Order Maintenance Log.xls")
Set oWks = oWkb.Sheets(1)

' write to spreadsheet

oXL.Visible = True
Set oWks = Nothing
Set oWkb = Nothing
Set oXL = Nothing

End Sub

 

[morgan23]

I figured out that if I save the spreadsheet, renaming it, after it has been opened by my procedure then it works fine. But if I just close the spreadsheet after my procedure as opened it is when I continue to have a lock on the spreadsheet and I am not able to open it again outside of my procedure. Any ideas how to release the lock if I just want to close the spreadsheet without saving it?

Morgan

 

[richary]

Have you explictly closed and saved the spreadsheet file, rather than just setting the object to Nothing?

 

[morgan23]

The excel spreadsheet comes up after command is ran. I just close it manually. Do I need to close it in the code, and how would I do that?

Morgan

 

[richary]

use

owkb.save '(if you need to save the file, I assume you do)
owkb.Close
oXL.Quit


richary

 

[morgan23]

Where would I place the code:

owkb.Close
oXL.Quit

to keep the excel application from wanting to close right way?

For example, right now I have:

oXL.Visible = True
owkb.Close
oXL.Quit
Set oWks = Nothing
Set oWkb = Nothing
Set oXL = Nothing
This causes the "do you want to save" window to come up right after the sheet is displayed. What I would like to be able to do is display the spreedsheet, be able to look at or modify it, decide if I want to save it and then close the spreed sheet from excel and return to the access program>
This is what I would like to be able to do:

Private Sub ....
... do some stuff

oXL.Visible = True
// use view/modify excel spreedsheet.

//then on close of excel return to procedure and do
owkb.Close
oXL.Quit
Set oWks = Nothing
Set oWkb = Nothing
Set oXL = Nothing
End Sub

Also is there a way of using the Close command and not have the save question come up?

Thansk for your help,
Morgan

 

[Xmas]

You can close the workbook without the question window:
owkb.Close false


Excel VBA help:

Close Method

Closes the object. The Workbooks collection uses Syntax 1. Window and Workbook objects use Syntax 2.

Syntax 1

expression.Close

Syntax 2

expression.Close(SaveChanges, FileName, RouteWorkbook)

SaveChanges Optional Variant. ... If there are changes to the workbook but the workbook doesn't appear in any other open windows, this argument specifies whether changes should be saved, as shown in the following table.

True -- Saves the changes to the workbook. If there is not yet a file name associated with the workbook, then FileName is used. If FileName is omitted, the user is asked to supply a file name.
False -- Does not save the changes to this file.
Omitted -- Displays a dialog box asking the user whether or not to save changes.

 

[Bat17]

have a look at UserControl in XL help, I suspect it has something to do with your original locked file problem.

Peter