-
Terminate Excel Process
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
[VBA]
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
[/VBA]
Thanks for all your help!!!
-
You, can try this, may be not the best solution
Hi, You can try this
[vba]
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
[/vba]
-
A little more on that function
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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):
[vba]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[/vba]
ps. If you're expecting others to use taskill on their machines I'd be scared it would kill all their instances of Excel!
Last edited by p45cal; 04-17-2010 at 08:33 AM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
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
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules