Excel still running in task manager after import to access 1

[icewiper]

Hello,

I have been reading the tek-tips on this issue but I still can't seem to get the matter resolved. I am importing multiple excel files into access. The importing works with no problem the first time, but I have excel.exe running in the task manager. When I import a second time the task manager shows a second Excel.exe during the execution. I used the END code and it removes the second Excel.exe with no problem but the first Excel.exe is still there and I get a message box that pops up saying "File Now Available"
"(filename) is now available for editing choose Read-Write to open it for editing."

If anyone has any ideas to stop this i would greatly appreciate it.

Private Sub btnImportData_Click()

Dim GetFile
Dim sql_Del As String
Dim sql_Upd As String
Dim VAR_SLASH As Integer
Dim VAR_SLASH_FNL As Integer
Dim CHAR_SLASH As String
Dim FILE_STR As String
Dim xl As Excel.Application
Set xl = New Excel.Application

GetFile = xl.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select Site Excel file", , True)

On Error GoTo Err_btnImportData_Click:

For Each item1 In GetFile

For VAR_SLASH = 1 To 500
 CHAR_SLASH = Left(Right(item1, VAR_SLASH), 1)
 If CHAR_SLASH = "\" Then
 VAR_SLASH_FNL = VAR_SLASH
 Exit For
 Else
 End If
 Next VAR_SLASH
 FILE_STR = Right(item1, VAR_SLASH_FNL - 1)

xl.Workbooks.Open item1

DoCmd.SetWarnings False
xl.ActiveWorkbook.Unprotect PassWord:="password"
xl.ActiveSheet.Unprotect PassWord:="password"
    xl.Cells.Select
    xl.Range("J1").Activate
    xl.Selection.EntireColumn.Hidden = False

DoCmd.TransferSpreadsheet acImport, , "tbl_ImportData_Temp", item1, True, "A:I"

sql_Upd = "UPDATE tbl_ImportData_Temp LEFT JOIN tbl_ImportData ON (tbl_ImportData_Temp.DateNominated ="
sql_Upd = sql_Upd & " tbl_ImportData.DateNominated) AND (tbl_ImportData_Temp.AwardType = tbl_ImportData.AwardType)"
sql_Upd = sql_Upd & " AND (tbl_ImportData_Temp.Site = tbl_ImportData.Site) AND (tbl_ImportData_Temp.EmpID ="
sql_Upd = sql_Upd & " tbl_ImportData.EmpID) SET tbl_ImportData.EmpID = tbl_ImportData_Temp.EmpID, tbl_ImportData.LastName"
sql_Upd = sql_Upd & " = tbl_ImportData_Temp.LastName, tbl_ImportData.FirstName = tbl_ImportData_Temp.FirstName,"
sql_Upd = sql_Upd & " tbl_ImportData.MI = tbl_ImportData_Temp.MI, tbl_ImportData.Site = tbl_ImportData_Temp.Site,"
sql_Upd = sql_Upd & " tbl_ImportData.AwardType = tbl_ImportData_Temp.AwardType, tbl_ImportData.Amount ="
sql_Upd = sql_Upd & " tbl_ImportData_Temp.Amount, tbl_ImportData.Notes = tbl_ImportData_Temp.Notes,"
sql_Upd = sql_Upd & " tbl_ImportData.DateNominated = tbl_ImportData_Temp.DateNominated;"
sql_Del = "DELETE tbl_ImportData_Temp.* FROM tbl_ImportData_Temp;"
DoCmd.RunSQL sql_Upd
DoCmd.RunSQL sql_Del
xl.Workbooks(FILE_STR).Close savechanges:=False
xl.Quit
Next

Set xl = Nothing
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
MsgBox "Done Importing!"
End
Err_btnImportData_Click:
Exit Sub

End Sub

 

[combo]

Try to remove error handler and see what happens, anyway, you should quit excel here. Guess that importing (TransferSpreadsheeet) works with excel file rather than excel application. What access see, is opened, password protected file that can't be imported. I would rather use the following sequence:
1. in excel application (automation):
- opene excel file,
- unprotect it,
- save under different name (temporary),
- close
2. import saved file
3. delete temporary excel file.
You can have some problems in timing.

combo

 

[icewiper]

Hi Combo,

Thanks for the reply, i was reading more in the tek-tips and ran across something PHV had said. It is pretty much the same thing you are saying too. I moved the xl.quit and the xl.workbooks.close the just before the importing. That still didn't seem to work so well until i changes the save workbooks to true. Now it works like a charm.
Thanks again for your help and I’ll post the code incase others would like to see the working change.

Private Sub btnImportData_Click()

Dim GetFile
Dim sql_Del As String
Dim sql_Upd As String
Dim VAR_SLASH As Integer
Dim VAR_SLASH_FNL As Integer
Dim CHAR_SLASH As String
Dim FILE_STR As String
Dim xl As Excel.Application
Set xl = New Excel.Application

GetFile = xl.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select Site Excel file", , True)

On Error GoTo Err_btnImportData_Click:

For Each item1 In GetFile

For VAR_SLASH = 1 To 500
 CHAR_SLASH = Left(Right(item1, VAR_SLASH), 1)
 If CHAR_SLASH = "\" Then
 VAR_SLASH_FNL = VAR_SLASH
 Exit For
 Else
 End If
 Next VAR_SLASH
 FILE_STR = Right(item1, VAR_SLASH_FNL - 1)

xl.Workbooks.Open item1

DoCmd.SetWarnings False
xl.ActiveWorkbook.Unprotect PassWord:="password"
xl.ActiveSheet.Unprotect PassWord:="password"
    xl.Cells.Select
    xl.Range("J1").Activate
    xl.Selection.EntireColumn.Hidden = False
    [COLOR=red][b]xl.Workbooks(FILE_STR).Close savechanges:=True[/b][/color]
    [COLOR=red][b]xl.Quit[/b][/color]
DoCmd.TransferSpreadsheet acImport, , "tbl_ImportData_Temp", item1, True, "A:I"

sql_Upd = "UPDATE tbl_ImportData_Temp LEFT JOIN tbl_ImportData ON (tbl_ImportData_Temp.DateNominated ="
sql_Upd = sql_Upd & " tbl_ImportData.DateNominated) AND (tbl_ImportData_Temp.AwardType = tbl_ImportData.AwardType)"
sql_Upd = sql_Upd & " AND (tbl_ImportData_Temp.Site = tbl_ImportData.Site) AND (tbl_ImportData_Temp.EmpID ="
sql_Upd = sql_Upd & " tbl_ImportData.EmpID) SET tbl_ImportData.EmpID = tbl_ImportData_Temp.EmpID, tbl_ImportData.LastName"
sql_Upd = sql_Upd & " = tbl_ImportData_Temp.LastName, tbl_ImportData.FirstName = tbl_ImportData_Temp.FirstName,"
sql_Upd = sql_Upd & " tbl_ImportData.MI = tbl_ImportData_Temp.MI, tbl_ImportData.Site = tbl_ImportData_Temp.Site,"
sql_Upd = sql_Upd & " tbl_ImportData.AwardType = tbl_ImportData_Temp.AwardType, tbl_ImportData.Amount ="
sql_Upd = sql_Upd & " tbl_ImportData_Temp.Amount, tbl_ImportData.Notes = tbl_ImportData_Temp.Notes,"
sql_Upd = sql_Upd & " tbl_ImportData.DateNominated = tbl_ImportData_Temp.DateNominated;"
sql_Del = "DELETE tbl_ImportData_Temp.* FROM tbl_ImportData_Temp;"
DoCmd.RunSQL sql_Upd
DoCmd.RunSQL sql_Del
Next

Set xl = Nothing
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
MsgBox "Importing is Done!"
End
Err_btnImportData_Click:
Exit Sub

End Sub

 

[combo]

Glad it works. For future testing code with excel automation, you can use:

Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Visible = True ' test mode

This will allow to read excel messages and close excel when code fails.

combo