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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now