Get Word to close a specific instance of Excel

[sts023]

Hi guys - as usual I'm going round in circles with this one. Any help would be appreciated.

Background
In Word, when opening a specific document, I want to open a specific Excel spreadsheet (lets call it the DB Workbook), which it is entirely possible that the User is already working in. In Word VBA I can detect this successfully, and when I do, I present the User with an Userform warning them that the DB Workbook is open, and offer them three options, viz - 1. Abandon (i.e. Exit Word), 2. Close and save the DB Workbook, or 3. Close the DB workbook without saving it.
All of this is working, but....

The Problem
I always end up with an instance of Excel with a blank workbook if I choose either of the Save options.
Having tried stepping through my code, it appears that the line

gobjDBWkb.Close SaveChanges:=True
seems to close the Workbook, but leaves the instance of Excel which opened the Workbook still active.
I use the following Sub to identify if any instance of Excel was loaded from the file whose fully qualified address is held in the variable “gstrMastreBalancesDataFull”, defined as

Public gstrMasterBalancesDataFull As String
Other relevant definitions are

Public gobjDBWkb As Workbook

Public Sub GetAllXLWindows()
Dim lngHWnd As Long
Dim lngHWndDesk As Long
Dim lngHWndMain As Long
Dim lngRet As Long
Dim objWkb As Workbook
Dim strClassName As String
Dim strMsg As String
Dim strText As String
gbooXLWasAlreadyOpen = False 'Pessimist!
'*
'** Returns Handle to XLMAIN (Top level
'** reference to Excel).
'*
lngHWndMain = FindWindowEx(0, 0, "XLMAIN", vbNullString)
'*
'** Is Excel running?
'*
If lngHWndMain <> 0 Then 'Yes!
'*
'* Load each instance of Excel
'** into a Collection.
'*
Set gcolWindows = New Collection
Do
'*
'** Find XLDESK (instance of Excel on Desktop).
'*
lngHWndDesk = FindWindowEx(lngHWndMain, 0, "XLDESK", vbNullString)
Do While lngHWndDesk <> 0
'*
'** For each Child of XLDESK....
'*
lngHWnd = FindWindowEx(lngHWndDesk, 0, vbNullString, vbNullString)
Do While lngHWnd <> 0
'*
'** Set up a string of 100 nulls, to be used
'** by GetclassName to return the Class Name.
'** Note, the length of the name is the
'** result of GetClassName
'*
strText = String$(100, Chr$(0))
'*
'** Get the Class Name
'** We're looking for "EXCEL7", which is
'** the Class name for a Workbook.
'*
lngRet = GetClassName(lngHWnd, strText, 100)
strClassName = Left(strText, lngRet)
Select Case strClassName
Case "EXCEL7" 'Excel Workbook
strText = String$(100, Chr$(0))
lngRet = GetWindowText(lngHWnd, strText, 100)
If lngRet > 0 Then
'*
'**Add XLObject to the collection, with a
'** key of the lngHWnd.
'*
gcolWindows.Add funGetXlObjFromHwnd(lngHWnd), CStr(lngHWnd)
End If
Case "EXCEL6" 'Worksheet cell in edit mode.
Case "NUIPANE" '?? (Found by inspection)
End Select
lngHWnd = FindWindowEx(lngHWndDesk, lngHWnd, vbNullString, vbNullString)
Loop 'End of Child loop
lngHWndDesk = FindWindowEx(lngHWndMain, lngHWndDesk, vbNullString, vbNullString)
Loop
'*
'** Get next Windows object
'*
lngHWndMain = FindWindowEx(0&, lngHWndMain, "XLMAIN", vbNullString)
Loop While lngHWndMain <> 0
'*
'** End of Discovery phase.
'*
On Error Resume Next
For lngRet = 1 To gcolWindows.Count
Set objWkb = gcolWindows(lngRet)
If objWkb.Windows(1).Visible And _
Not objWkb.IsAddin And _
objWkb.FullName = gstrMasterBalancesDataFull Then
Set gobjDBWkb = objWkb
gbooXLWasAlreadyOpen = True
End If
Next lngRet 'Examine next Collection item.
End If
End Sub 'GetAllXLWindows

Note that it is also possible that the User may have other unrelated instances of Excel open, so if I were to issue a sort of "Find Excel and shut it down", I'd probably shut every instance down, which I don't want to do.
I've tried

gobjDBWkb.Quit after the Close,
but I just get "Automation error - The Object invoked has disconnected from its clients".

Any ideas? (Please!)

[Frosty]

Well, I'm not sure how you're starting Excel from the above. Are you using GetObject or CreateObject (or some combination of the two)? Normally, people use GetObject, and then if nothing is there... they use CreateObject (which will start the application).

But you need to have more than a global object referencing your workbook - you probably also need a global object referencing the application object (which you should be able to get off the .Parent property of your gobjDBWkb).

Try something like this (not sure where you'd put it in your code -- but needs to be *before* you close the workbook).
Dim gobjMyExcelApp As Object (or As Excel.Application -- depending on whether you want to use early or late binding -- looks like you're using early binding, but remember you have to explicitly use Excel.Application if you're within Word VBA -- just "Application" will default to using the Word.Application object).

Set gobjMyExcelApp = gobjDBWkb.Parent

Then you can use the .Quit method on gobjMyExcelApp after you've actually closed the workbook without saving changes.

I think you're right that you don't want to use the GetAllXLWindows function -- although you *could* use it to identify the Application your open workbook is in, but you should already have that info much more simply off of your Workbook object.

[Frosty]

Technically, the answer to your question *would* require that GetAllXLWindows code, because apart from PID (which is really only a way of knowing which Excel applications were opened first), the best way to identify a specific instance of Excel would be by what workbooks are open. So you'd do something like writing a function where you pass in a full path of a workbook you know... and then loop through until all instances of the Excel windows, and then all workbooks open in each of those windows, and then test against your path.

But I don't think you actually need to have something that robust for what seems to be your issue.

[sts023]

Hi Frosty, thanks for the reply!

In the interim between posting the query and getting your reply, I'd been thinking along similar lines, and am slowly working towards a solution, which I'll post when (if???) I get it working.

The Word application hasn't (yet) opened the Excel Workbook: all this is an attempt to say to the User "I know you've got the Workbook open, so please decide what you want to do in this situation. Your options are 1) Abort the Word process, 2) Ask Word to close and save the Workbook, or 3) Ask Word to close the Workbook without saving it".

My current investigations lead me to believe that the routine I eventually hope to develop will be "Application Independent" (i.e. it will work in any of the main Office applications), so I take on board your comments about Dimming variables as "Excel.Application" instead of just "Object".

This is my first venture into the weird world of API, and I get a bit bamboozled by Handles, Threads, and API function definitions which seem to have scant documentation. Thanks to sites like VBAX I can find code snippets, but although these snippets often show me WHAT to do, the WHY I need to do it in that particular sequence seems elusive!
But hey!, that's the nature (and joy) of learning!

More later....


Steve

[Frosty]

Steve: there's no problem with dimming as Excel.Application or Object. Both are valid. That falls under a concept of late-binding vs early-binding (both of which have their place).

I was talking about Application vs. Excel.Application... Which will actually be different things when you run in different flavors of VBA.