Excel file does not close after import into Access using VBA

Hello, I have an Access application where I open an Excel file, select a sheet and import the data. However, the problem is that if there is another Excel file open, the imported file does not close automatically and a "read-only" dialog opens for the Excel file I imported, halting import process. I have two sets of code, one with functions in a module and the other code within the form that will import the Excel File.

1. Function Code in Module.

Option Compare Database

Dim m_OpenExcel As Excel.Application

Dim m_OpenWorkbook As Workbook

Public Function GetExcelFile()

Set fDialog = Application.FileDialog(3) 

With fDialog 

    ' Allow user to make multiple selections in dialog box 

    .AllowMultiSelect = False 

    ' Set the title of the dialog box. 

    .Title = "Please select Excel file to import" 

    ' Clear out the current filters, and add our own. 

    .Filters.Clear 

    .Filters.Add "Excel 2007", "\*.xls\*" 

    ' Show the dialog box. If the .Show method returns True, the user picked a 

    ' file. If the .Show method returns False, the user clicked Cancel. ' 

    If .Show = True Then 

        GetExcelFile = .SelectedItems(1) 

    Else 

        GetExcelFile = Null 

    End If 

End With 

End Function

Public Function OpenExcelWorkbook(path As String)

If m\_OpenExcel Is Nothing Then 

    Set m\_OpenExcel = New Excel.Application 'CreateObject("Excel.Application") 

End If 

If IsEmpty(m\_OpenWorkbook) Then 

    m\_OpenExcel.Workbooks.Open path 

    Set m\_OpenWorkbook = m\_OpenExcel.ActiveWorkbook 

ElseIf m\_OpenWorkbook Is Nothing Then 

    m\_OpenExcel.Workbooks.Open path 

    Set m\_OpenWorkbook = m\_OpenExcel.ActiveWorkbook 

ElseIf m\_OpenWorkbook.FullName <> path Then 

    m\_OpenExcel.Workbooks.Open path 

    Set m\_OpenWorkbook = m\_OpenExcel.ActiveWorkbook 

End If 

End Function

Public Function ExcelSheetsNameList(path As String) As String()

OpenExcelWorkbook (path) 

Dim shts() As String 

ReDim shts(m\_OpenWorkbook.Sheets.Count - 1) 

For x = 1 To m\_OpenWorkbook.Sheets.Count 

    shts(x - 1) = m\_OpenWorkbook.Sheets(x).name 

Next x 

ExcelSheetsNameList = shts 

End Function

Public Function GetExcelWorksheet(path As String, sheetName As String) As Worksheet

OpenExcelWorkbook (path) 

Set GetExcelWorksheet = m\_OpenWorkbook.Sheets(sheetName) 

End Function

Public Sub DeleteTableSafe(name As String)

On Error Resume Next 

DoCmd.DeleteObject acTable, name 

Exit Sub 

Error:

MsgBox Err.Description, vbInformation, "Error No: " & Err.Number 

End Sub

Public Sub NewImportUtilitiesModuleDispose()

If Not m\_OpenWorkbook Is Nothing Then 

    m\_OpenWorkbook.Close 

    Set m\_OpenWorkbook = Nothing 

End If 

If Not m\_OpenExcel Is Nothing Then 

    m\_OpenExcel.Quit 

    Set m\_OpenExcel = Nothing 

End If 

End Sub

2. Code in Form

Option Compare Database

Private Sub buttonBrowse_Click()

'The first requirement is to be able to browse to the Excel file. Here is the code that is executed in the form when the “Browse” button is clicked: 

'This code first clears out information in the form that may be applicable to the last file that was selected, and then the user is provided a dialog to select a file with the “xlsx” extension. If a file is not selected, then the code returns, otherwise the Excel file is opened to find its sheet names, which are displayed in a list box to allow the user to select the sheet to import. 

'First the information in the Form is cleared, including clearing out the file name TextBox, the sheet names ListBox, and the hide the sub form. 

'Clear out existing information before browse 

Dim itemsString As String 

textboxExcelFileToImport = "" 

listBoxWorksheets.RowSource = "" 

subFormData.Visible = False 

DoEvents 

'Allow user to browse Excel file to import 

textboxExcelFileToImport = GetExcelFile 

If IsNull(textboxExcelFileToImport) Then Exit Sub 

'Fill in list box that will contain the sheet names 

itemsString = Join(ExcelSheetsNameList(textboxExcelFileToImport), ";") 

listBoxWorksheets.RowSource = itemsString 

End Sub

Private Sub buttonImportBudgetandActuals_Click()

Dim response As VbMsgBoxResult

If subFormData.Visible = False Then

MsgBox ("No Data to Import. Please select file and sheet to be imported")

Else

DoCmd.SetWarnings False 'Stop the warning message that asks you if you are sure you want to continue

DoCmd.OpenQuery ("qry_Importbudgetandactuals") ' Make table from Temp table of Imported Records

Call delTbl 'delete the Excel$ error tables

DoCmd.SetWarnings True

response = MsgBox("Budget and Actuals Records have been imported, do you want to Close form?", vbQuestion + vbYesNo)

If response = vbYes Then

  DoCmd.Close acForm, "Frm\_ImportActuals" 

End If

End If

End Sub

Private Sub Form_Close()

NewImportUtilitiesModuleDispose

End Sub

Private Sub Form_Load()

textboxExcelFileToImport = "" 

listBoxWorksheets.RowSource = "" 

subFormData.Visible = False 

    'DeleteTableSafe "T\_Temp" 

End Sub

Private Sub listBoxWorksheets_Click()

Dim sheet As Worksheet 

Dim sheetRange As String 

subFormData.SourceObject = Empty 

DeleteTableSafe "T\_Temp" 

DoEvents 

Set sheet = GetExcelWorksheet(textboxExcelFileToImport, listBoxWorksheets) 

    sheetRange = listBoxWorksheets & "!" 'this code will import all the columns on the spreadsheet 

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "T\_Temp", textboxExcelFileToImport, True, sheetRange 

    subFormData.SourceObject = "Table.T\_Temp" 

    subFormData.Visible = True 

Set sheet = Nothing 

End Sub