Fritz Paul
asked on
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?
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER