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.
Determine which workbook opened the current
-
- 4StarLounger
- Posts: 584
- Joined: 12 Jul 2012, 10:34
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Determine which workbook opened the current
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?
What are you trying to accomplish?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 584
- Joined: 12 Jul 2012, 10:34
Re: Determine which workbook opened the current
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.
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.
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Determine which workbook opened the current
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":
2) The code in the "callee" to check the "caller":
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
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
Hans
-
- 4StarLounger
- Posts: 584
- Joined: 12 Jul 2012, 10:34
Re: Determine which workbook opened the current
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.
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.
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Determine which workbook opened the current
I don't see how that will work. Excel can open a workbook only once...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 817
- Joined: 24 Jan 2010, 15:56
Re: Determine which workbook opened the current
I'd put a public property in the callee workbook that can then be set by the caller.
Regards,
Rory
Rory
-
- 4StarLounger
- Posts: 584
- Joined: 12 Jul 2012, 10:34
Re: Determine which workbook opened the current
Why didn't I think of that.rory wrote:I'd put a public property in the callee workbook that can then be set by the caller.
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.
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Determine which workbook opened the current
What about using the commandline for that:
http://dailydoseofexcel.com/archives/20 ... ts-in-vba/" onclick="window.open(this.href);return false;
http://dailydoseofexcel.com/archives/20 ... ts-in-vba/" onclick="window.open(this.href);return false;
-
- 4StarLounger
- Posts: 584
- Joined: 12 Jul 2012, 10:34
Re: Determine which workbook opened the current
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.
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.