Problem with Excel file after Importing to Access

[Monardo]

Hello

I am experiencing a problem, which I could not resolve with Google.

In my application I am importing Excel into Access table and also extracting data from one cell from the same file with following code:

    Dim fd As Object
    Dim File_Name As String
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    Dim ExcelApp As Excel.Application
    Set ExcelApp = CreateObject("Excel.Application")
    
    Dim WkBk As Excel.Workbook
    
    With fd
        .AllowMultiSelect = False
        .Title = "Please select a file to import"
        .InitialFileName = varFilesImport
        .Filters.Clear
        .Filters.Add "Excel workbook", "*.xlsx"
        
        If .Show = True Then
            File_Name = fd.SelectedItems(1)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "t_Spoligo_Import_Temp", File_Name, True, "FI!B8:AT105"
            
            Set WkBk = ExcelApp.Workbooks.Open(filename:=File_Name)
            Dim varExcDate As String
            varExcDate = CDate(Mid(WkBk.Sheets("FI").Range("A2").Value, 19, 11))
            
            Me.txtDateTested = varExcDate
            Me.cmbTester.SetFocus
        End If
    End With
    
    Set ExcelApp = Nothing
    Set WkBk = Nothing
    Set fd = Nothing

All works great from Access side, but the Excel file itself gets the problem. It does not open as it should. When I open file, excel software flashes and disappears. If I open any other file, then two windows open one with new file and another "old" file (one I imported).

It feels as if the Access locked the Excel file and did not "unlock", but I am not sure what line I should add for that.

Thanks for help

 

[JHB]

I can't see in your code that you close Excel, you set ExcelApp to nothing, but it isn't the same as close it.

 

[Monardo]

I can't see in your code that you close Excel, you set ExcelApp to nothing, but it isn't the same as close it.

What would be the code? Would this work?

Set WkBk = ExcelApp.Workbooks.Close(filename:=File_Name)

 

[vbaInet]

Like JHB said you need to close something, and it's the Workbook you close.

Below is fyi, if you're going to use early binding then it should be consistent.

    Dim ExcelApp As [COLOR="blue"]Excel.Application[/COLOR]
    Set ExcelApp = [COLOR="Blue"]New Excel.Application[/COLOR]

Otherwise, late binding:

    Dim ExcelApp As [COLOR="blue"]Object[/COLOR]
    Set ExcelApp = [COLOR="blue"]CreateObject("Excel.Application")[/COLOR]

 

[Monardo]

So how do I "Close" it, what would be the line of code?

 

[vbaInet]

WkBk.Close?

 

[Monardo]

Slightly modified as:

WkBk.Close SaveChanges:=False, filename:=File_Name

did the charm.

Thanks vbaInet

 

[vbaInet]

Glad we could.