PDA

View Full Version : Terminate Excel Process



oglezsa
04-16-2010, 09:45 AM
Hi there!!!...I use Word to create a Document based on a template via userform and the information from the userform
to an Excel spreadsheet. Everything works fine on that side, problem is when i want to open the excel
file, a msg comes up stating that i can only view the file in ready-only mode,
this is because the Excel process doesn't terminate. The solution is, I have to go into the process manager
and end the Excel process manualy, then I can run Excel again and have full access to the file. My question is how can
I terminate excel with VBA? here is what i have


Option Explicit
Public appXL As Excel.Application
Public xlFile As Excel.Workbook

On the userform_Initialize
Set appXL = CreateObject("Excel.Application")
Set xlFile = appXL.Workbooks.Open _
(FileName:="C:\Documents and Settings\oog1814\Desktop\Test.xls")
...

On THE EXCEL PART OF THE BUTTON CLICK
appXL.Visible = True
xlFile.Worksheets("Sheet1").Range("A2").Select

Range("a65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Select
If ActiveCell = "" Then
ActiveCell.Value = TextBox22.Text
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = TextBox1.Text
ActiveCell.Offset(0, 1).Range("A1").Select
End If
ActiveWorkbook.Save
'appXL.Visible = True
xlFile.Close True
Set xlFile = Nothing

appXL.Quit
Set appXL = Nothing


Thanks for all your help!!!

hardlife
04-16-2010, 10:51 AM
Hi, You can try this :hi:



Sub testDOSCommands()

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")

DOSCommands = "TASKKILL /F /IM EXCEL.EXE"

'MsgBox DOSCommands

Set objDOSFile = objFSO.CreateTextFile("DOSCommands.bat", 1)
objDOSFile.Write DOSCommands
objDOSFile.Close

strCommand = "cmd /c DOSCommands.bat "

objShell.Run strCommand, 0, True
'objFSO.DeleteFile "DOSCommands.bat", 1

'MsgBox "OK"

'Set objShell = Nothing
'Set objFSO = Nothing

End Sub

mdmackillop
04-17-2010, 04:37 AM
A little more (http://www.computerhope.com/taskkill.htm) on that function

p45cal
04-17-2010, 07:18 AM
I've been playing with this trying to reproduce the problem with some success, but not complete. Although I didn't get the same error message (that the file could only be opened read-only) Excel was still running). Occasionally I got the recovery pane up when I tried to view test.xls normally, but it had been saved properly.

The message you get implies that not only is Excel running, but also that the file is still open in it.
I note that you have
ActiveWorkbook.Save
then right up on the heels of that:
xlFile.Close True

I took out the Activeworkbook.Save and let the Close statement take care of saving and things got a lot cleaner. Excel closed down properly.

I realise that I'm not getting the same message as you but.. who knows?
Later, things got slicker when I made a few more changes taking out the .Visible=True and all the selecting and all ActiveCell/ActiveAnything references (note that I changed some textbox names etc. to suit my userform - and I hope I got the cell offsets right):
Dim xx As Excel.Range
'AppXL.Visible = True
Set xx = XLFile.Worksheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 0)
If xx = "" Then 'it has to be! (because XlUp has just run over it)
xx.Value = TextBox1.Text
xx.Offset(0, 1).Value = TextBox2.Text
End If
'ActiveWorkbook.Save
XLFile.Close True
Set XLFile = Nothing
appXL.Quit
Set appXL = Nothing
ps. If you're expecting others to use taskill on their machines I'd be scared it would kill all their instances of Excel!

mdmackillop
04-17-2010, 08:13 AM
Never used this, but I have had trouble with multiple instances so nice to learn something new.

This seems to delete only the Word created instance, by defining it using the Excel File name


Option Explicit

Public appXL As Excel.Application
Public xlFile As Excel.Workbook

Sub test()
Dim FName As String
FName = "md001"

Set appXL = CreateObject("Excel.Application")
Set xlFile = appXL.Workbooks.Open _
(FileName:="C:\Temp\" & FName & ".xls")
appXL.Visible = True
xlFile.Worksheets("Sheet1").Range("A2").Select
Range("a65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Select
If ActiveCell = "" Then
ActiveCell.Value = "MD"
End If
ActiveWorkbook.Save
'appXL.Visible = True
xlFile.Close True
Set xlFile = Nothing
appXL.Quit
Set appXL = Nothing

Call DOSDelete(FName)

End Sub

Sub DOSDelete(ExcelName As String)
Dim objFSO As Object
Dim objDOSFile As Object
Dim objShell As Object
Dim DOSCommands As String
Dim strCommand As String

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")

DOSCommands = "TASKKILL EXCEL.EXE-" & ExcelName

Set objDOSFile = objFSO.CreateTextFile("DOSCommands.bat", 1)
objDOSFile.Write DOSCommands
objDOSFile.Close

strCommand = "cmd /c DOSCommands.bat "

objShell.Run strCommand, 0, True
Set objFSO = Nothing
Set objShell = Nothing

End Sub