Excel workbook not released after being read from MS Access

Posted on 2014-04-27
Last Modified: 2014-04-27
I use VBA in Access to read data from an Excel workbook and append it to a table in Access.

Problem is that I cannot edit the Excel workbook afterwards, because it is "open in Excel". Below is the message that I get when trying to rename the Excel workbook. Then below that is the  code that I use.

What should I do?

Error message
Private Sub cmdReadAndAppend_Click()

Dim ExcelApp As excel.Application
Dim ExcelBook As excel.Workbook
Dim ExcelSheet As excel.Worksheet

Set ExcelApp = CreateObject("Excel.Application")
Set ExcelBook = ExcelApp.Workbooks.Open(txtWorkbookToOpen)
'Define which sheet to read from
Set ExcelSheet = ExcelBook.Worksheets("Agreement Terms")

'Set ExcelSheet = ExcelBook.Worksheets(1)

Dim var As Variant 'there may be a better type to use here
'var = WkBk.Sheets(1).Range("A1").Value
'var = WkBk.Sheets(ExcelSheet).Range("A1").Value

    var = ExcelSheet.Cells(1, 1).Value
'    var = ExcelBook.Sheets(ExcelSheet).Cells(1, 1).Value
    txtExcelValueIs = var
    MsgBox txtExcelValueIs

Dim Licensor As Variant
Dim Period As Variant
Dim Licensee_Id As Variant
Dim Licensee As Variant
Dim Agreement As Variant
Dim Agreement_Currency As Variant
Dim Promotional_Rate As Variant
Dim Agreement_Period_From As Date
Dim Agreement_Period_To As Date
Dim Template_Inclusion_Cutoff As Date
Dim Statement_Inclusion_Cutoff As Date
Dim Comment As Variant
Dim i As Integer

    Licensor = ExcelSheet.Cells(3, 2).Value

For i = 9 To 12
        Period = ExcelSheet.Cells(i, 1).Value
        Licensee_Id = ExcelSheet.Cells(i, 2).Value
        Licensee = ExcelSheet.Cells(i, 3).Value
        Agreement = ExcelSheet.Cells(i, 4).Value
        Agreement_Currency = ExcelSheet.Cells(i, 5).Value
        Promotional_Rate = ExcelSheet.Cells(i, 6).Value
        Agreement_Period_From = ExcelSheet.Cells(i, 7).Value
        Agreement_Period_To = ExcelSheet.Cells(i, 8).Value
        Template_Inclusion_Cutoff = ExcelSheet.Cells(i, 9).Value
        Statement_Inclusion_Cutoff = ExcelSheet.Cells(i, 10).Value
        Comment = ExcelSheet.Cells(i, 11).Value
Dim tablename As String
Dim variables As String
Dim values As String

    tablename = "tblAgreement_Terms"
    variables = "([Licensor], [Period], [Licensee_Id], [Licensee], [Agreement], [Agreement_Currency], [Promotional_Rate], [Agreement_Period_From], [Agreement_Period_To], [Template_Inclusion_Cutoff], [Statement_Inclusion_Cutoff], [Comment])"

    values = "(" & "'" & Licensor & "', " & "'" & Period & "'," & "'" & Licensee_Id & "'," & "'" & Licensee & "'," & "'" & Agreement & "'," & "'" & Agreement_Currency & "'," & "'" & Promotional_Rate & "'," & "'" & Agreement_Period_From & "'," & "'" & Agreement_Period_To & "'," & "'" & Template_Inclusion_Cutoff & "'," & "'" & Statement_Inclusion_Cutoff & "'," & "'" & Comment & "'" & ")"
'    MsgBox values
'    values = "( 'Disney' )"
'    MsgBox values
'    AppendToTable tablename, variables, values
DoCmd.RunSQL "INSERT INTO " & tablename & variables & "VALUES" & values & ";"
Next i

ExcelBook.Close savechanges:=False 'close without changes
Set ExcelSheet = Nothing
Set ExcelBook = Nothing
Set ExcelApp = Nothing

End Sub

Open in new window

Question by:Fritz Paul
LVL 27

Accepted Solution

MacroShadow earned 500 total points
ID: 40025599
There are several problems with your code:
1. all variables should be dimensioned in the beginning of the code, since that is the way the compiler works.
2. you are using a mish-mash of early and late binding, choose one and stick to it.

As far as your issue is concerned, I don't see a problem with the code, it seems to be closing excel. Insert a breakpoint at line 73, is Excel still open at that point?

Author Closing Comment

by:Fritz Paul
ID: 40025626
Thanks, I made the suggested changes and it works.

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question