4

When I go to open Excel (manually), I receive the warning that my Personal.xls is already in use by "another user". This suggests there is an Excel process running. I confirm this on the Task Manager -- Excel is indeed running.

So I put together a quick sub to attempt to close it, and this procedure raises the 429 error on Set x = GetObject(, "Excel.Application").

Sub QuitExcel()
Dim x As Object
Set x = GetObject(, "Excel.Application")
x.Quit
End Sub

Question: Why is the GetObject method failing?

Excel appears to be running, as indicated by the task manager and also by the alert that is displayed when I open Excel from the taskbar.

Update from comments

Jean-Francois suggests using a pathname, since the alert indicates a problem with my Personal.xls, I use that path. I am not really sure this is working, though. When I do this, I still see the Excel.EXE image in taskbar.

Sub GetExcel()
Dim x As Object
On Error Resume Next
    Set x = GetObject(, "Excel.Application")
    x.Quit
    Exit Sub
If Err Then
    Set x = GetObject("C:\Users\david_zemens\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.xls")
End If
On Error GoTo 0
x.Parent.Visible = True
x.Parent.Quit


End Sub
Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • This will sound funny for sure, but MSDN says that 429 is because there's no instance of that class running: http://msdn.microsoft.com/en-us/library/e9waz863(v=vs.90).aspx This is the help from VB, not VBA (true). Is it possible that that instance of Excel is not run under your username? then maybe you don't have access to the application, but you can see it... –  May 28 '14 at 16:23
  • Also, could you try to see if there's a difference between `Set x = GetObject(, "Excel.Application")` and `Set x = GetObject("", "Excel.Application")`? This latter should try to create a new instance of Excel... –  May 28 '14 at 16:28
  • Yes I saw that same documentation and I'm aware of what the 429 means which is why it's so perplexing to me. I am the only user on this machine. While it could be run "as Adminstrator" (e.g., from a logon script, etc.), if that were the case I should be experiencing this *often* or *always*, rather than only sometimes. – David Zemens May 28 '14 at 16:29
  • 1
    `GetObject("C:\...\Personal.xls")` is worth a try! – Jean-François Corbett May 28 '14 at 16:30
  • @Jean-FrançoisCorbett I'll give that a shot next time it happens. I have killed the process manually (for the time being). – David Zemens May 28 '14 at 16:31
  • @Jean-FrançoisCorbett OK, that does work. Any idea why that method works and the Classname does not work? – David Zemens May 28 '14 at 18:04
  • @Jean-FrançoisCorbett on closer inspectin, I'm not sure that's working. I still see the Excel application in taskmanager. – David Zemens May 28 '14 at 18:47
  • Why do you say `x.Parent.Quit` and not just `x.Quit`? `x` is the Application. Not that it should make a difference: by convention the `Parent` of `Application` is `Application` itself. – Jean-François Corbett May 28 '14 at 18:51
  • @Jean-FrançoisCorbett see revision... sorry, it is necessary to use `x.Parent.Quit` because using the method `GetObject("c:\..\Personal.xls")` returns a `Workbook` object. – David Zemens May 28 '14 at 21:38
  • 2
    Would it not be better to figure out what started the instance of Excel in the first place, rather than to stumble around trying to figure out how to kill it? – Ken White May 28 '14 at 21:54
  • @KenWhite yes, but for now my question is: *Why is the GetObject method failing?*, and not "How can I kill this process" (I can kill the process manually, and that is fine workaround) but I suspect that the reasons which might prevent me from getting the object will provide some clue(s) for me to troubleshoot the root cause of why that object even *exists* in the first place. – David Zemens May 28 '14 at 22:32
  • 1
    The typical reason for the invisible Excel process and this type of error is automation that doesn't properly clean up when it's finished, leaving an instance of Excel in memory, and that instance clearly has `personal.xls` opened exclusively and doesn't want to share. I'd use Process Explorer to find out what/how the Excel process was started in the first place, and fix the issue. You don't have to be able to (and probably can't) connect to the running instance, and I don't see how doing so is going to help troubleshoot anything. – Ken White May 28 '14 at 22:39
  • @DavidZemens I agree with Ken White, go through your personal.xls and check your code - maybe you're not freeing some resources. Try to identify the cause first. Also, since Jean's idea with the path worked for you at least once it would mean that the actual class name isn't `Excel.Application`. Maybe try `Window Detective` or `Spy++` when this happens to identify the **actual class name**? –  May 29 '14 at 07:24
  • @mehow 99% of the time the GetObject method works as expected with the classname `Excel.Application`, it is only sometimes that this orphaned Excel appears when it does not work. I will look in to my personal.xls but that is basically a repository of hundreds of macros and code samples that I've put together over the years so I would not even know where to begin... – David Zemens May 29 '14 at 13:27
  • @DavidZemens oh I see, I see... hm :/ –  May 29 '14 at 13:29
  • @mehow no worries... I'm pretty sure I'm responsible for this "extra" instance of Excel as a result of some troubleshooting/debugging I was trying to do, so I have a handle on hopefully preventing it in the future, but mostly just wondering if there was something obvious that I'm overlooking. – David Zemens May 29 '14 at 13:54
  • 1
    @DavidZemens probably nothing obvious. I myself never really found the `GetObject()` unreliable so I'd exclude the obvious reasons. –  May 29 '14 at 14:22
  • @mehow what I'm thinking is that I'm trying to get the active instance and `DisplayAlerts = False`, and then some more code happens, during which an error might have raised and caused me to end the vba execution, which might have (?) leave that instance open in an unresponsive state? – David Zemens May 29 '14 at 14:37
  • +1 David, this question provides a link to the full path approach that JCF mentioned. http://stackoverflow.com/questions/15908553/how-to-get-workbook-name-of-running-excel-instance-using-vbscript – brettdj May 30 '14 at 08:40

1 Answers1

1

Personal.xls (or Personal.xlsb on Excel 2007+) does not contain a worksheet and may not be visible in Excel without going to the VBA editor. If you don't have any saved macros that you want constant access to when Excel opens, you can delete the Personal.xls* file from the %appdata%\Microsoft\Excel\XLSTART folder. Otherwise, the quickest and simplest way to terminate an invisible Excel instance is with a shell/command prompt/RUN command.

In VBA:

Shell "taskkill /f /im Excel.exe"

In Run/Command Prompt:

taskkill /f /im Excel.exe

Note: this will do a forced close on every Excel instance running - including the one running the macro. As someone else hinted to, the other Excel instance may be trying to show a notification/prompt that you're not seeing.

BAReese
  • 491
  • 2
  • 5
  • not really sure this answers why the `GetObject` is failing, though. That should be able to return an instance of Excel even if the application `.Visible = False`. I think the problem is more likely that an invisible instance has an open dialog which raises the error (a busy application cannot respond to vba); if that's the case then there is no way to get a handle on it. UNfortunately, I can't kill all Excel processes on users' machines this way. – David Zemens Jul 02 '14 at 14:38
  • Sounds like you're back to figuring out what's causing the problem in the first place then. Is the Personal.xls file needed or can the file just be deleted altogether? I don't remember how Excel 2003 works, but 2007 doesn't even create a worksheet. Would there be macros in the Workbook_Open portion of Personal.xls that is causing the problems? – BAReese Jul 02 '14 at 15:07
  • I'm using Excel 2010 actually, but my personal workbook still has an XLS extension. Yes, it does have one macro in the `_Open` event which adds a new workbook *only if* the Personal.xls is the only workbook in the workbooks collection. This should not raise any dialogs on its own under any circumstances (I have never observed it to cause any open dialogs in ~6 years of using that event handler). – David Zemens Jul 02 '14 at 15:19
  • No, the Personal.xls cannot be deleted and since this is part of a large distributed PPT Application, I can't go around deleting stuff from the users' machines, either. They have not reported such error, it only happened one day, over a month ago, when I was trying to debug something and probably I caused it by orphaning some instance of Excel, as noted... I'm not actively seeking to recreate this error condition, presently... – David Zemens Jul 02 '14 at 15:20