Excel workbook not released after being read from MS Access

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

Fritz PaulAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MacroShadowConnect With a Mentor Commented:
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?
0
 
Fritz PaulAuthor Commented:
Thanks, I made the suggested changes and it works.
0
All Courses

From novice to tech pro — start learning today.