Excel 2007 workbook locked after access from Access 2007

0
votes

I've got a project where I'm reading data from an Excel worksheet and saving it in Access tables (not a direct import--see this question if you're interested). My current problem is that any time I run my "import" and then try to open the workbook in Excel, it's "locked for editing" unless/until I close Access. I don't think this should be the case. My process is

  1. Open the workbook with Automation.
  2. Build a collection of sheet names.
  3. Release the Automation objects.
  4. If there's more than one sheet, get user input on which to process.
  5. Open an ADO recordset on a specific range & read some data.
  6. Release the recordset & connection.
  7. Open an ADO recordset on a different specific range & read a bunch o' data.
  8. Release the recordset & connection.
  9. Close the controling form.

Steps 1 - 4 and 9 live in the form file, the remainder in a module.

Am I missing something? I think I've released all the references to the workbook....

1
excelms-accessvbams-access-2007

1 Answers

1
votes

If your step #1 includes something like this:

Dim objExcel As New Excel.Application

And later releasing the object is this:

Set objExcel = Nothing

Trying including this line just before you set the object variable to Nothing:

objExcel.Quit

It also helps to make the Excel application instance visible after starting it so you're less likely to leave Excel running unseen:

objExcel.Visible = True