close excel from access

[leroiv]

Hi!
I have a form in Access with a button with the code:

Private Sub cmdPrintToWord_Click()
Dim strSelect As String
Dim appWord As Word.Application
Dim appExcel As Excel.Application
Dim doc As Word.Document
Dim exlBook As Excel.Workbook
Dim exlSheet As Excel.Worksheet
Dim rs, rst, rstTipOP, rstTipCO As ADODB.Recordset
Dim r, rez As Boolean
Dim lngCounter, i, j, ii, jj As Integer
Dim perTerminat, elemTablou(1 To 9) As Variant
Dim luna, OldElem, CurentElem, zile, strSelect1, strSelect2 As String

Me!CboLuna.SetFocus
If Me!CboLuna.Text = "" Then
r = MsgBox("Select month!", vbExclamation, "Attention")
Exit Sub
End If
luna = "Month(Operatii.Perioada_start)"
strSelect = "SELECT Operatii.Nume, Operatii.Initiala_tatalui, Operatii.Prenume, Operatii.Perioada_start, Operatii.Perioada_stop, Operatii.Nr_zile_concediu, FROM Operatii WHERE (((" & luna & ")=" & Me!CboLuna.Value & ")) GROUP BY Operatii.Nume;"
'Avoid error 429, when Word isn't open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
End If
'Set appExcel object variable to running instance of Excel.
Set appExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
'If Excel isn't open, create a new instance of Excel.
Set appExcel = New Excel.Application
End If
'Populate recordset object.
Set rs = New ADODB.Recordset
rs.Open strSelect, "Myconn", adOpenKeyset, adLockOptimistic
'first document
If rs.RecordCount > 0 Then
DoCmd.Hourglass True
r = MsgBox("Pentru luna ' " & Me!CboLuna.Text & " ' au fost gasite " & rs.RecordCount & " inregistrari.", vbInformation, "Info")
rs.MoveFirst
Set doc = appWord.Documents.Open("C:\tabele_situatie_lunara_concedii.doc", , True)
lngCounter = 1
i = 1
j = 1
OldElem = rs!Tip_concediu
For lngCounter = 1 To rs.RecordCount
CurentElem = rs!Tip_concediu
If CurentElem <> OldElem Then
i = i + 1
j = 1
End If
'write in my document file ....
'saving ...
doc.SaveAs "C:\tabele_situatie_lunara_concedii_" & Me!CboLuna.Text & ".doc", wdFormatDocument
doc.Close
'*****************************second document
Set doc = appWord.Documents.Open("C:\situatie_lunara_concedii.doc", , True)
rs.MoveFirst
'write in second document ...
doc.SaveAs "C:\situatie_lunara_concedii_" & Me!CboLuna.Text & ".doc", wdFormatDocument
doc.Close
Set exlBook = appExcel.Workbooks.Open("C:\tabel.xls")
Set exlSheet = exlBook.Sheets("Sheet1")
exlSheet.[A1] = 56576767
'exlSheet.Cells.Find(What:="whatever", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
exlBook.SaveAs ("C:\tabel_" & Me!CboLuna.Text & ".xls")
exlBook.Close False, "tabel.xls"
exlBook.Close False, "C:\tabel_" & Me!CboLuna.Text & ".xls"
appExcel.Quit
DoCmd.Hourglass False
MsgBox "Finished !"
'******************************************
Else
r = MsgBox("Uups ! No records !!", vbExclamation, "Error")
End If
appWord.Quit
Set doc = Nothing
Set appWord = Nothing
Set rs = Nothing
Set rst = Nothing
Set rstTipOP = Nothing
Set rstTipCO = Nothing
Set exlSheet = Nothing
Set exlBook = Nothing
Set appExcel = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub

The problem is at the final of my code... When I close the Excel application it closes but the process "excel.exe" remains active and I see it in task manager. When i try to open a .xls file , excel opens but don't load my file... I open the task manager, kill the process and now I can open .xls files. I noticed something ; if I remove or comment the line:
"'exlSheet.Cells.Find(What:="whatever", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select" ,
Excel closes with no problems and isn't present as process in task manager.
How to fix this problem in my code, to close excel? Any suggestions? Thank you for your time!

[stanl]

include

appExcel.Quit

and in the beginning of your code

appExcel.DisplayAlerts = False

to eliminate the "Do You want to Save..." when Excel Closes.

Stan

[CreganTur]

You should definately disable the display alerts and see if that fixes your problem.

If it doesn't then I believe the issue has to do with the line of code you commented out. I imagine that the issue has to do with the fact that you select the returned cells, but you never release the selection.

If you manually enter a cell in edit mode and then try to exit Excel it won't allow you. You have to exit the edit mode for that cell first. That may be what the issue is.

Why are you running a find as your last step before closing anyway, and why are you selecting the found range if you're not going to do anything with it?


NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: FYI, in the future please be sure to wrap your code in VBA tags (click the green VBA button). This will properly indent your code and color code it according to VBIDE- it makes it a lot easier to read.

[leroiv]

THANK YOU for your replies! It helps me much!
I founded how to fix :


adr = exlSheet.Cells.Find(What:="Nechita", After:=exlSheet.[A1], LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Columns.Address

This was causing the error : "After:=ActiveCell" and replaced with the red piece of code from above.
Excel runs clean and don't remains active in task processes. Now I can open the .xls files!

[leroiv]

and to reply to


Why are you running a find as your last step before closing anyway, and why are you selecting the found range if you're not going to do anything with it?

is: my code isn't finished and it was only to test that I find strings. From here I will continue to edit the finded strings.