hi,
i am importing files from excel to access. the problem is that when i finish importing the excel process (in task manager) is still running. is there any way to terminate the process from VBA i tried "close" and "quit" mothods and it's not working.
this is my code:
Public Function ImportFile(xlFile As String, tbl As String) As BooleanOn Error GoTo errorHandler
Dim ObjXL As New Excel.Application
Dim ObjXLBook As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet
Set ObjXLBook = ObjXL.Workbooks.Open(xlFile)
Set ObjXLSheet = ObjXLBook.ActiveSheet
Row = 1
Col = 1
While (ObjXLSheet.Cells(Row, Col).Value = "" Or Mid(ObjXLSheet.Cells(Row, Col).Value, 1, 1) = " ") And Row < 100
Col = 1
While (ObjXLSheet.Cells(Row, Col).Value = "" Or Mid(ObjXLSheet.Cells(Row, Col).Value, 1, 1) = " ") And Col < 100
Col = Col + 1
Wend
If ObjXLSheet.Cells(Row, Col).Value = "" Or Mid(ObjXLSheet.Cells(Row, Col).Value, 1, 1) = " " Then
Row = Row + 1
End If
Wend
If Row = 1 And Col = 1 And ObjXLSheet.Cells(Row, Col).Value = "" Then
MsgBox "The file has no data to import. Please check it and try again.", vbInformation, "No Data"
Exit Function
End If
ULRow = Row
If Int(Col / 26) = 0 Then
ULCol = Chr((Col Mod 26) + 64)
Else
ULCol = Chr(Int(Col / 26) + 64) & Chr((Col Mod 26) + 64)
End If
While ObjXLSheet.Cells(Row, Col).Value <> "" And Mid(ObjXLSheet.Cells(Row, Col).Value, 1, 1) <> " "
While Mid(ObjXLSheet.Cells(Row, Col).Value, Len(ObjXLSheet.Cells(Row, Col).Value), 1) = " "
ObjXLSheet.Cells(Row, Col).Value = Mid(ObjXLSheet.Cells(Row, Col).Value, 1, Len(ObjXLSheet.Cells(Row, Col).Value) - 1)
Wend
Col = Col + 1
Wend
Col = Col - 1
While ObjXLSheet.Cells(Row, Col).Value <> "" And Mid(ObjXLSheet.Cells(Row, Col).Value, 1, 1) <> " "
Row = Row + 1
Wend
Row = Row - 1
LRRow = Row
If Int(Col / 26) = 0 Then
LRCol = Chr((Col Mod 26) + 64)
Else
LRCol = Chr(Int(Col / 26) + 64) & Chr((Col Mod 26) + 64)
End If
rng = ULCol & ULRow & ":" & LRCol & LRRow
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, tbl, ObjXLBook.FullName, True, rng
'ObjXL.Application.DisplayAlerts = False
'ObjXL.Application.Save
'ObjXL.Application.DisplayAlerts = True
ObjXLBook.Saved = True
Set ObjXLBook = Nothing
Set ObjXLSheet = Nothing
ObjXL.Quit
Set ObjXL = Nothing
ImportFile = True
Exit Function
errorHandler:
ImportFile = False
End Function
please help me solve this problem...
thanks,
refael
close excel when finish
m14cus
thanks for your reply,
it's still there... the thing is i want excel to be closed if i call it from access but i want it to stay open if i open excel regularly.
thanks
refael
brsphere
Hi,
Try calling ObjXL.Quit when your finished with Excel. The instance of Excel you create in code will be independent of instances that are started by a user.
Hansonlion
Try entering
ObjXLBook.Close
before
Set ObjXL = Nothing
I Think this should close the application
Leo Dragos
Hi
Derek is right without the xlObj.quit line in your original code above Excel stays open. I copied your code to a module and removed the code that works with the sheet, and put in two lines to make the application and the worksheet visible, then a msgbox. Excel did not quit becuasethe sheet I called was still open. Closing the sheet before calling xlObj.quit closed the Excel application
TBeaulieu
thanks guys,
i tried your recomendations but it didn't work.
i solved the problem by moving the "TransferSpreadSheet" method to the end of the code (ofter the xlobj.quit) and it works like a charm.
thanks anyway!
refael