Microsoft Excel - Workbooks.Open executes, but not if run from a nested macro

I have a macro called ToGlobal which has the following code in it:

Dim wb As Object

Set wb = Workbooks.Open(Range("Fullname").Text, WriteResPassword:="GTP")

The macro is called from the Workbook_BeforeSave event and works fine.

However, I have a separate macro that times out the workbook- there is an ontime macro that afer a certain period of time calls a second macro which saves and closes the workbook. The  ToGlobal macro runs, but it doesn't open the workbook- there is no error message, it just doesn't seem to execute that line of code.

I have tried calling the macro which saves and closes the workbook from a separate macro to see if the ontime macro was the problem, but still the Workbooks.Open line of code in To Global does nothing.

I have also tried:
Application.Workbooks.Open ("S:\Tactical Planners\Global\Global TP Dec 13 - Mar 14.xls"), WriteResPassword:="GTP"

- no difference. I have also set teh workbook that the code is opening to visible- again, the same as above.

Hello John
This is a bit of a stab in the dark but perhaps your filename has gone into an undefined state by the time the macro gets to be executed.  Have you put in a break on that line and checked the state/value of the variables?
Perhaps you could post the relevent code from your timeout macro, that might help to spot a problem with sequencing or something.
Regards
Harry

Hi John,
I have been able to reproduce your problem not quite exactly as you describe and it would seem to me that closing/saving a file from a macro does not trigger the close/save event and I don't get to the relevant code.  However, if you believe the info out there, this should not be the case.
Regards
Harry

Hello again John,
Why not put your code directly into the timeout macro?  You can still have it in the before save since the save in the time out macro doesn't seem to trigger the event.  Works in my mock-up anyway.
Regards
Harry

Hi Harry

Thanks for your time. I have tried the latter, but the problem is that the rest of the code DOES execute, so putting it in the timeout means it runs twice (albiet when called from the before save macro, the workbook doesn't open), and this causes other problems.

Re your first idea about it loosing the variable being a possibility, I have tried removing the variable which gives the full filepath and directly referring to the filename & path, but the same thing happens.

The macro which is called from before save (that runs perfectly when executed via the user clicking save) is as follows:
Sub ShutDown()
ThisWorkbook.Activate
ActiveWorkbook.Worksheets(1).Activate
ActiveSheet.Unprotect Password:="TP"
Application.ScreenUpdating = False
Range("A42:AJ48").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A1:AJ7").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
 Application.ScreenUpdating = False
 ActiveSheet.Protect Password:="TP", DrawingObjects:=False, Contents:=True, Scenarios:= _
    True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
    ActiveSheet.EnableSelection = xlNoRestrictions
 Range("BB37").Select
 Selection.Copy
Application.ScreenUpdating = True
Dim myFile As String, myFolder As String
myFolder = Range("Filepath")
myFile = myFolder & Range("Filename")
If Not IsFolderExists(myFolder) Then
    CreateObject("Scripting.FileSystemObject").CreateFolder myFolder
End If
If Not IsFileExists(myFile) Then
    MsgBox "The folder path & file " & myFile & " could not be found and the global TP cannot be updated." & vbCrLf _
    & "Please inform the administrator"
    UserForm1.Hide
    Exit Sub
End If
If ActiveWorkbook.Saved = False And ActiveWorkbook.ReadOnly = False Then
ActiveWorkbook.Save
UserForm1.Show
UserForm1.Repaint
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets(1).Activate
Range("A3:AJ7").Select
Selection.Copy
Dim CurrentGlobal As String
CurrentGlobal = Replace(Range("Filename"), "\", "")
Application.Workbooks.Open (Range("Fullname").Text), WriteResPassword:="GTP"
Application.Windows(CurrentGlobal).Activate
If ActiveWorkbook.ReadOnly = True Then
UserForm1.Hide
MsgBox "Another user has the global TP open. Could not update- please click save again to update the global TP"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Exit Sub
End If
ActiveWorkbook.Worksheets(1).Activate
Range("A3").Select
ActiveWorkbook.Worksheets(1).Paste
'PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.Close
Application.DisplayAlerts = True
Range("B3").Select
UserForm1.Hide
End If
Application.Quit
End Sub

The macro which executes on workbook open is:

Private Sub Workbook_Open()
Call SetTime
End Sub

The SetTime macro is:
Dim DownTime As Date
Sub SetTime()
DownTime = Now + TimeValue("00:05:00")
Application.OnTime DownTime, "ShutDown"
End Sub

The ShutDown macro that it calls after five minutes is:
Sub ShutDown()
ThisWorkbook.Save
Application.Quit
End Sub

As the ShutDown macro saves the workbook it should and does trigger the ToGlobal macro, but the line opening the other workbook doesn't do anything.

Hello John,
I have had a quick look at your ShutDown macro and there a re a couple of points that might or might not be relevant.
The first is  just a matter of semantics but you have an unnecessary Application.ScreenUpdating = False at line 4 or line 11.
The second point is that your open workbook is enclosed in an If statement that requires .Saved = False and .ReadOnly = False so unless both conditions are met your open will never be executed.  Further, within that If statement you have another If statement that requires .Readonly to be true which would clearly be impossible.
Not sure whether either of these are the cause of the problem but either way it would seem that your logic needs a bit of work.
I will peruse further but let me know if this helps.
Regards
Harry

Thanks Harry- I appreciate my code is probably clunky and inelegant- I am self trained so there is an element of muddling along!

I was rushing and I posted an older version of the code, so my apologies for that. Here is the correct code for the main macro:
Sub ToGlobal()
ThisWorkbook.Activate
ActiveWorkbook.Worksheets(1).Activate
ActiveSheet.Unprotect Password:="TP"
Application.CutCopyMode = False
Selection.Copy
Range("A1:AJ7").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Protect Password:="TP", DrawingObjects:=False, Contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
ActiveSheet.EnableSelection = xlNoRestrictions
Range("BB37").Select
Selection.Copy
Dim myFile As String, myFolder As String
myFolder = Range("Filepath")
myFile = myFolder & Range("Filename")
If Not IsFolderExists(myFolder) Then
CreateObject("Scripting.FileSystemObject").CreateFolder myFolder
End If
If Not IsFileExists(myFile) Then
MsgBox "The folder path & file " & myFile & " could not be found and the global TP cannot be updated." & vbCrLf _
& "Please inform the administrator"
UserForm1.Hide
Exit Sub
End If
If ActiveWorkbook.Saved = False And ActiveWorkbook.ReadOnly = False Then
UserForm1.Show
UserForm1.Repaint
ActiveWorkbook.Worksheets(1).Activate
Range("A3:AJ7").Select
Selection.Copy
Dim CurrentGlobal As String
CurrentGlobal = Replace(Range("Filename"), "\", "")
WriteResPassword:="GTP"
Dim wb As Object
Set wb = Workbooks.Open(Range("Fullname").Text, WriteResPassword:="GTP")
Application.Windows(CurrentGlobal).Activate
If ActiveWorkbook.ReadOnly = True Then
UserForm1.Hide
MsgBox "Another user has the global TP open. Could not update- please click save again to update the global TP"
ActiveWorkbook.Close
Exit Sub
End If
ActiveWorkbook.Worksheets(1).Activate
Range("A3").Select
ActiveWorkbook.Worksheets(1).Paste
Application.CutCopyMode = False
Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.Close
Range("B3").Select
UserForm1.Hide
End If
End Sub

I have taken out the duplicate Application.ScreenUpdating = False, but it makes no difference, and I have also tried taking out all such instances- again, no result. Thanks for pointing it out though & Thanks for your time on this.

Regards,
John

Hi John,
I have to rush so won't get a chance to look too hard for a while.
It seems to me that there is still the logic clash where inside one if loop you have an opposite logic requirement that can't possibly be met.  Also for the Workbooks.Open (and other parts to execute both .Saved and .ReadOnly have to be false  so are you sure that this is what you want and that both do apply when you are testing?  The other thing is, what is it that calls/triggers the ToGlobal Sub, it isn't called from any of the other code that you supplied in your earlier post?
Will get back to you later.
Regards
Harry

Hi Harry
The macro is called from the before save event, and when the workbook is saved, it works perfectly. The problem is when the timed shutdown has the line to save the workbook. The ToGlobal macro does run, but fails to open the workbook 'Global TP'- I know this because the userform opens (the useform is only to alert the user what is happening). When it gets to the line Application.Windows(CurrentGlobal).Activate it hits a 'subscript out of range error' and I find that the 'Global TP' isn't open. If I take out the line Application.Windows(CurrentGlobal).Activate then the code runs through but fails to do anything. As I say, this isn't the case when it is triggered by the user clicking save or closing the workbook and clicking 'yes' when asked 'save changes', and the macro runs as it should.

I have tried calling the 'Shutdown macro from another macro to see if it is anything to do with the ontime method, but the problem persists. I have also tried stripping away everything except for the opening of the 'Global TP' in the ToGlobal macro, and again, the problem persists.

Re: the line If ActiveWorkbook.Saved = False And ActiveWorkbook.ReadOnly = False Then this is to ensure that the workbook being pasted from is not read only, and is hasn't already been saved after that, all it does is copy cells and declare a variable and open the Global TP. The next reference to read only is in the line If ActiveWorkbook.ReadOnly = True Then, and this is in reference to the Global TP which the code has just opened and activated. If that workbook is found to be read only, it closes it and exits the macro is it wouldn't be possible to paste to it, and it then exits the if statement.

Thanks again for your help. I hope you are dashing off to start enjoying the holiday season.

Regards

John

Hello John,
Just back from my game of golf.
The .Windows giving a subscript out of range error suggests that it is looking for a value inside the brackets eg .Windows(1).Activate.  I wonder whether if you are trying to use a file name it has to be enclosed in quotes eg .Windows("MyFile.xls").Activate.
One other small point, I wonder whether the first line of
WriteResPassword:="GTP"
Dim wb As Object 
is really required.
As I asked before, have you checked the actual value of the parameters at various stages through the macro when it does run by putting in Breakpoints and Watches.  This could help immeasurably with sorting out exactly what is going on and what might be going wrong.
Regards
Harry

Hi there

I have already tried breakpoints, and from what I can see, the 'subscript out of range' error is beacause a workbook that isn't open is being referenced. The line WriteResPassword:="GTP" is necessary as there is a password, and taking it out makes no difference. What I have tried is stripping removing all but the relevant code form the workbook, inluding the ontime which I have replaced with a macro call for the ShutDown macro that I execute manually. I have also replaced the file name viariable with the actual full filename, so:

I manually run:
Sub ShtDn()
Call ShutDown
End Sub

Which calls:

Sub ShutDown()
ThisWorkbook.Save
End Sub

Which triggers:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call ToGlobal
End Sub

Which calls ToGlobal:

To Global ()
MsgBox Application.Workbooks.Count
Application.Workbooks.Open ("C:\Users\Johnny\Desktop\Global TP Jan 14 - Mar 14.xls")
MsgBox Application.Workbooks.Count
End Sub

Again, when I save the workbook, a message box tells me 1 workbook is open, the Global TP Jan 12- March 14.xls opens (although I am now asked for a password as there isn't the line mentioned earlier), and then a message box tells me there are 2 workbooks open, and then the workbook containing the code saves.

When I run the ShtDn macro, a message box tells me 1 workbook is open, and then a message box tells me there is 1 workbook open, and then the workbook containing the code saves.

The message boxes I have added as I though there may be an issue with the workbook being opened being visible and to check how far the ToGlobal macro is running.

Regards,
John

Hello John,
I have implemented your test routines and as you say, the workbook open does not work either when it is included in the BeforeSave or the ToGlobal macro is called from there.  However it does work if you include it in the ShutDown macro or call ToGlobal from  the Shutdown.  I even tried relocating the ToGlobal macro to the WorkBook Module but to no avail.
From what I have been able to find on the web there is nothing to contra-indicate that what you are trying to do should be achievable so maybe there is some sort of a bug.
I guess the big question is, to save a lot more time agonising over it, is whether the code has to be in the before save event module or whether it can be called directly which does work at least.
Regards and compliments of the season.
Harry

Hello again John,
I found this statement (but have no idea of the source of the 'facts' and haven't seen anything like this on MS):
It seems that Workbook_BeforeSave disables Excel from opening more files, and I guess there's a good reason for doing that, since the File > Open option is still visible in the File menu, but it can't be clicked. Strangely, the Open toolbar icon/button still works, and so whilst I can manually open the file from there, I wonder if it's because it's impossible to call this action from VBA code and that's why they allowed it?
The writer also suggested that putting the open in a separate macro in the workbook module fixed it for him but my tests did not work.
But this still doesn't explain why it works when you do a manual save.
Regards
Harry

Hi Harry


Thanks for all your time on this. When I started out on what became the ToGlobal macro, the code was in the before save event, but as you say, I found that the lines opening the Global TP workbook and referring to it didn't work- I had a hunch that putting it in a stand alone macro and callling it from the before save event would fix it, and it did, but then I found that if the save was triggered by code I ran into the save/ opening ToGlobal problem again. I can't call ToGlobal directly from the ShutDown macro as this would result in the code running, but then when it has run and there is the line to Save the source workbook, it woud execute again, albiet incompletely.
I could put it in the before close event, but it's not ideal as users will likely have already saved the workbook, and it the ToGlobal makes changes to th eworkbook which need to me saved again- some users have a poor connection to the server, so the less saves the better. Added to that, they may not want to save changes anyway, although I guess I could put  in a question asking of they want to save changes, but if they have saved it at an earlier time whilst having it open, they would probably click no.
I'm truly stumped on this one!
Compliments of the season to you too.

Regards

John

Hello John,
There are obviously layers within layers here that I don't quite understand which makes it a bit difficult to get my head around.  Ultimately, I reckon there is nothing that can't be worked around.
Have you flow-charted your process and the program logic?  Sometimes, getting away from the code and getting back to basics can change your whole outlook on the problem.  Of course, in the ideal world, this should be the first step in development, not the last but there is nothing to say that you can't visit or revisit it later in the process either.
If you do have something along these lines, perhaps if you post it, another set of eyes could help further.
Regards
Harry

Hi Harry

I will try doing that and try to find an alternative. For reference, I found this:
http://support.microsoft.com/kb/898511
It seems that the reason why GlobalTP cannot be opened is because it is a 'menu command'. Annoying that you are not alerted that it is now permitted.

Thanks for all your time and have a happy holiday.

Regards
John