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

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


"Sh0t2bts" wrote in message
...
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

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" wrote in message
...
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


"Sh0t2bts" wrote in message
...
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

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

--
Regards,
Tom Ogilvy


Sh0t2bts wrote in message
...
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" wrote in message
...
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


"Sh0t2bts" wrote in message
...
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
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" wrote in message
...
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

--
Regards,
Tom Ogilvy


Sh0t2bts wrote in message
...
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" wrote in message
...
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


"Sh0t2bts" wrote in message
...
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

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

That option was added in xl2000.

--
Regards,
Tom Ogilvy

"Sh0t2bts" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy


Sh0t2bts wrote in message
...
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" wrote in message
...
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


"Sh0t2bts" wrote in message
...
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