problem with code in workbook_open event
 

I have inserted the code below in PERSONAL.XLS
This works if Excel is launched first, then I launch another template.

However I get an error if I launch another template first, no matter what
other template it is and whether it has code in it or not.

I get a runtime error 91: Object variable or with block variable not set.


Private Sub Workbook_Open()
Call LaunchForecast
End Sub

Public Sub LaunchForecast()
'
Dim strWorkBookname As String
Dim oWorkb As Object
Set oWorkb = Application.ActiveWorkbook

strWorkBookname = oWorkb.Name
DoEvents

If Right(oWorkb.Name, 3) = "CSV" Then
MsgBox "hold it"
Application.ScreenUpdating = False

Application.Workbooks.Add(Template:="\\Wmc-srv-2\Templates\PM
Templates\dmsForecast.xlt").RunAutoMacros Which:=xlAutoOpen
End If
End Sub

problem with code in workbook_open event
 

when you launch Excel you open the default book1. Excel then opens Personal
..xls
At this point there is as active workbook for yon code to code on. When you
launch a file Excel will open first, then Personal. xls and then your file.
This means that there is no active book for yow code to work on.

The solution would be for your Personal file lo include a class module that
has a variable dim'd With Events as an Excel Application ,So that you can
trap the workbook opening and then call yow aile.
--
Patrick Molloy
Microsoft Excel MVP

"Dominique Schroeder" wrote in message
...
I have inserted the code below in PERSONAL.XLS
This works if Excel is launched first, then I launch another template.

However I get an error if I launch another template first, no matter what
other template it is and whether it has code in it or not.

I get a runtime error 91: Object variable or with block variable not set.


Private Sub Workbook_Open()
Call LaunchForecast
End Sub

Public Sub LaunchForecast()
'
Dim strWorkBookname As String
Dim oWorkb As Object
Set oWorkb = Application.ActiveWorkbook

strWorkBookname = oWorkb.Name
DoEvents

If Right(oWorkb.Name, 3) = "CSV" Then
MsgBox "hold it"
Application.ScreenUpdating = False

Application.Workbooks.Add(Template:="\\Wmc-srv-2\Templates\PM
Templates\dmsForecast.xlt").RunAutoMacros Which:=xlAutoOpen
End If
End Sub

problem with code in workbook_open event
 

Patrik,

thanks for responding so quickly.

So if I understand correctly, Personal.xls is not considered a workbook.
Dominique

"Patrick Molloy" wrote in message
...
when you launch Excel you open the default book1. Excel then opens
Personal
.xls
At this point there is as active workbook for yon code to code on. When
you
launch a file Excel will open first, then Personal. xls and then your
file.
This means that there is no active book for yow code to work on.

The solution would be for your Personal file lo include a class module
that
has a variable dim'd With Events as an Excel Application ,So that you can
trap the workbook opening and then call yow aile.
--
Patrick Molloy
Microsoft Excel MVP

"Dominique Schroeder" wrote in message
...
I have inserted the code below in PERSONAL.XLS
This works if Excel is launched first, then I launch another template.

However I get an error if I launch another template first, no matter
what
other template it is and whether it has code in it or not.

I get a runtime error 91: Object variable or with block variable not
set.


Private Sub Workbook_Open()
Call LaunchForecast
End Sub

Public Sub LaunchForecast()
'
Dim strWorkBookname As String
Dim oWorkb As Object
Set oWorkb = Application.ActiveWorkbook

strWorkBookname = oWorkb.Name
DoEvents

If Right(oWorkb.Name, 3) = "CSV" Then
MsgBox "hold it"
Application.ScreenUpdating = False

Application.Workbooks.Add(Template:="\\Wmc-srv-2\Templates\PM
Templates\dmsForecast.xlt").RunAutoMacros Which:=xlAutoOpen
End If
End Sub

problem with code in workbook_open event
 

Personal.xls is a standard workbook it's 'hidden' is all.

--
Patrick Molloy
Microsoft Excel MVP

"Dominique Schroeder" wrote in message
...
Patrik,

thanks for responding so quickly.

So if I understand correctly, Personal.xls is not considered a workbook.
Dominique

"Patrick Molloy" wrote in message
...
when you launch Excel you open the default book1. Excel then opens
Personal
.xls
At this point there is as active workbook for yon code to code on. When
you
launch a file Excel will open first, then Personal. xls and then your
file.
This means that there is no active book for yow code to work on.

The solution would be for your Personal file lo include a class module
that
has a variable dim'd With Events as an Excel Application ,So that you
can
trap the workbook opening and then call yow aile.
--
Patrick Molloy
Microsoft Excel MVP

"Dominique Schroeder" wrote in message
...
I have inserted the code below in PERSONAL.XLS
This works if Excel is launched first, then I launch another template.

However I get an error if I launch another template first, no matter
what
other template it is and whether it has code in it or not.

I get a runtime error 91: Object variable or with block variable not
set.


Private Sub Workbook_Open()
Call LaunchForecast
End Sub

Public Sub LaunchForecast()
'
Dim strWorkBookname As String
Dim oWorkb As Object
Set oWorkb = Application.ActiveWorkbook

strWorkBookname = oWorkb.Name
DoEvents

If Right(oWorkb.Name, 3) = "CSV" Then
MsgBox "hold it"
Application.ScreenUpdating = False

Application.Workbooks.Add(Template:="\\Wmc-srv-2\Templates\PM
Templates\dmsForecast.xlt").RunAutoMacros Which:=xlAutoOpen
End If
End Sub