Determine which workbook opened the current

syswizard
4StarLounger
Posts: 584
Joined: 12 Jul 2012, 10:34

Determine which workbook opened the current

Post by syswizard »

I thought this would be easy....but when there are hidden workbooks involved, it's not that simple.
I tried iterating over the Windows collection, but strangely, despite 2 other workbooks being opened, the count was set at 1.
One WB was visible, the other hidden.
I need a reference to the workbook with an index lower than the current active, but it cannot be hidden.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Determine which workbook opened the current

Post by HansV »

The collection of all windows in all workbooks is Application.Windows. But I don't think that'll help - when I checked, Application.Windows(1) was the currently active workbook and Application.Windows(2) was Personal.xlsb, although the latter had been opened first (with the application). Moreover, one workbook can have multiple windows open.
What are you trying to accomplish?
Best wishes,
Hans

syswizard
4StarLounger
Posts: 584
Joined: 12 Jul 2012, 10:34

Re: Determine which workbook opened the current

Post by syswizard »

I even tried Application.Caller - ERROR 2023.
I want to know which workbook called the currently active one because the name of that workbook determines what to do next.
So I want to determine this in the workbook open event handler.
So if workbook "A" calls me (opens me), I do "xyz". If workbook "B" calls me, I do "123".

This must be a VBA timing bug:
when I first trap the VBA code in the open event, the windows.count is 3.
However, when I step thru the rest of the code, the windows.count is reset to 1 !!!
I really need the windows count to determine which workbook is hidden as that one could have been opened last, but it likely didn't open the current one...
especially if it's not an XLAM or XLSM filetype.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Determine which workbook opened the current

Post by HansV »

I don't think there's a dependable way to determine from which workbook the active one was opened, so it should be approached from the other side: the "caller" should put up a signal that it opened the "callee". One way would be to write the name of the "caller" in a pre-determined cell of the "callee", but I don't know whether that would be acceptable. Another way would be store the info in the registry:

1) The code in the "caller" that opens the "callee":

Code: Select all

Sub OpenOtherWorkbook()
    Dim strCaller As String
    Dim strCallee As String
    Dim strPath As String
    strCaller = ThisWorkbook.Name
    strCallee = "Test.xlsm"
    strPath = "C:\Excel\"
    SaveSetting "SysWizard", strCallee, "Caller", strCaller
    Workbooks.Open strPath & strCallee
End Sub
2) The code in the "callee" to check the "caller":

Code: Select all

Private Sub Workbook_Open()
    Dim strCaller As String
    strCaller = GetSetting("SysWizard", Me.Name, "Caller")
    If strCaller = "" Then
        ' Workbook not opened from a "Caller"
    Else
        ' Do something with strCaller
    End If
End Sub
Best wishes,
Hans

syswizard
4StarLounger
Posts: 584
Joined: 12 Jul 2012, 10:34

Re: Determine which workbook opened the current

Post by syswizard »

Brilliant Hans....thanks.
The only problem is there could be multiple callers at the same time.
That's why grabbing the workbook name, which changes, would have been perfect.
An alternative is to call a procedure in the caller's VBAProject which sets a Property or global variable.

Here's what I am doing:
I have the windows scheduler calling Excel with a workbook name reference.
That workbook simply opens another common workbook that does all of the work.
However, multiple jobs could be running simultaneously....each calling the common workbook.
The workbook name reference in the scheduler is the key to which processsing takes place.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Determine which workbook opened the current

Post by HansV »

I don't see how that will work. Excel can open a workbook only once...
Best wishes,
Hans

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Determine which workbook opened the current

Post by rory »

I'd put a public property in the callee workbook that can then be set by the caller.
Regards,
Rory

syswizard
4StarLounger
Posts: 584
Joined: 12 Jul 2012, 10:34

Re: Determine which workbook opened the current

Post by syswizard »

rory wrote:I'd put a public property in the callee workbook that can then be set by the caller.
Why didn't I think of that.
Perfect....thanks Rory.
Now is there anyway to determine if that workbook is running from a call in the windows scheduler vs. the normal invocation on a workstation ? Setting a registry key is the only thing that comes to mind. Then of course it must be un-set in cases of early termination, etc.
I wish they had expanded Application.Caller to support this sort of stuff.

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Determine which workbook opened the current

Post by Jan Karel Pieterse »

What about using the commandline for that:

http://dailydoseofexcel.com/archives/20 ... ts-in-vba/" onclick="window.open(this.href);return false;
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

syswizard
4StarLounger
Posts: 584
Joined: 12 Jul 2012, 10:34

Re: Determine which workbook opened the current

Post by syswizard »

Thanks again Rory....I forgot about that /e option.
But one might ask why MSFT didn't provide a VBA property to return the arguments after the /e ?...Why does the windows API function need to be referenced....

While on this command line topic, is there much difference between using START vs. just referencing EXCEL.EXE in the command line to the windows scheduler ?
I am thinking I could elevate the execution priority with START.