Solved

Excel workbook not released after being read from MS Access

Posted on 2014-04-27
2
523 Views
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

0
Comment
Question by:Fritz Paul
2 Comments
 
LVL 26

Accepted Solution

by:
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?
0
 

Author Closing Comment

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 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

22 Experts available now in Live!

Get 1:1 Help Now