My Macro keeps stopping??

[Sh0t2bts]

Hi All

I have a macro in one workbook, which opens lots of other workbooks:-
Sub Auto_Open()
' Turn Off Auto Calcs....
With Application
.Calculation = xlManual
.CalculateBeforeSave = False
End With
' Open First workbook
Workbooks.Open(Filename:= _
"M&G.xls", UpdateLinks:=0 _
).RunAutoMacros Which:=xlAutoOpen
'Open Second workbook.
Workbooks.Open(Filename:= _
"ET.xls", UpdateLinks:=0 _
).RunAutoMacros Which:=xlAutoOpen

It does the first bit and opens the first workbook, the first workbook once open runs and Auto_Open macro does it's bit and then closes that Active window,
all is fine at this point the macro then comes back into this macro, where it should open the second workbook but it fails and the macro stops running


The Auto_Open macro in the workbooks that are opened looks like this:-
Sub Auto_Open()
do_i_run = Workbooks("Run All Service
Tracking.xls").Worksheets("Sheet1").Range("A15")
If (What_Date_Did_I_Run = What_Was_I_Opened) Then
If do_i_run = 10 Then
ActiveWorkbook.Close
End If
End If

This is to just get the workbooks to close for testing.


The reason I need this is that I have 12 workbooks that I need to open and runn Auto_Open macros, the macros collect data from one of our SQL servers, as I have 12 of these and I can only run one at a time I need them to run one after another, I can not use scheduler as I do not know if the data collection from the SQL box wll take 1 minute or 10 minutes, it just depends on how many users are connected to it when I try to run my work.


Can someone advise how to get my macro to continue to run

OR

How I could get one workbook to open once the previouse one has ran?

Many Thanks

Mark

 

[Tom Ogilvy]

I would close the workbook in the original code. when you close a workbook,
the code execution terminates - perhaps this termination is not returning
control to your original code.

--
Regards,
Tom Ogilvy


Hi All

I have a macro in one workbook, which opens lots of other workbooks:-
Sub Auto_Open()
' Turn Off Auto Calcs....
With Application
.Calculation = xlManual
.CalculateBeforeSave = False
End With
' Open First workbook
Workbooks.Open(Filename:= _
"M&G.xls", UpdateLinks:=0 _
).RunAutoMacros Which:=xlAutoOpen
'Open Second workbook.
Workbooks.Open(Filename:= _
"ET.xls", UpdateLinks:=0 _
).RunAutoMacros Which:=xlAutoOpen

It does the first bit and opens the first workbook, the first workbook once
open runs and Auto_Open macro does it's bit and then closes that Active
window,
all is fine at this point the macro then comes back into this macro, where
it should open the second workbook but it fails and the macro stops running


The Auto_Open macro in the workbooks that are opened looks like this:-
Sub Auto_Open()
do_i_run = Workbooks("Run All Service
Tracking.xls").Worksheets("Sheet1").Range("A15")
If (What_Date_Did_I_Run = What_Was_I_Opened) Then
If do_i_run = 10 Then
ActiveWorkbook.Close
End If
End If

This is to just get the workbooks to close for testing.


The reason I need this is that I have 12 workbooks that I need to open and
runn Auto_Open macros, the macros collect data from one of our SQL servers,
as I have 12 of these and I can only run one at a time I need them to run
one after another, I can not use scheduler as I do not know if the data
collection from the SQL box wll take 1 minute or 10 minutes, it just depends
on how many users are connected to it when I try to run my work.


Can someone advise how to get my macro to continue to run

OR

How I could get one workbook to open once the previouse one has ran?

Many Thanks

Mark

 

[Sh0t2bts]

How would I do that??
I can close the active windows and close the application completly but I
don't know how to close another session of Excel?


Cheers

Mark

 

[Tom Ogilvy]

What makes you think you are working in another session. The code as
written opens the workbooks in the same session of excel

Sub Auto_Open()
' Turn Off Auto Calcs....
With Application
.Calculation = xlManual
.CalculateBeforeSave = False
End With
' Open First workbook
With Workbooks.Open(Filename:= _
"M&G.xls", UpdateLinks:=0 _
)
.RunAutoMacros Which:=xlAutoOpen
.close SaveChanges:=False
End With
'Open Second workbook.
With Workbooks.Open(Filename:= _
"ET.xls", UpdateLinks:=0 _
)
.RunAutoMacros Which:=xlAutoOpen
.Close SaveChanges:=False
End With

 

[Sh0t2bts]

Tom
I figured as two lots of Excel open at the bottom of the screen but it
doen't matter anyway what you suggested worked a treat and I can close the
windows using the first macro


Thanks

Mark

 

[Tom Ogilvy]

That is controlled by Tools=>Options=>View, show windows in Task bar.

That option was added in xl2000.