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:
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
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:
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
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