I've developed a SQL Server Reporting Services Application for use by my
client to track how long it is taking form/documents to be processed by
employees. On of the options of Reporting Services is the capability to
export files to different formats including Excel. The client would like
some formatting done of the spreadsheets upon opening (e.g. removing the
header banner from the Report, formatting cell colors - nothing too
complicated) by staff.

I'm writing an addin to take care of this and it's been a couple of years
since I've worked w/ VBA for Excel so I'm a little rusty. This addin needs
to verify that the workbook that has just been opened is in fact an exported
file from Reporting Services because reformatting other workbooks that would
be opened would be a bad thing. So I'm using the Workbook_Open function to
get the name of the workbook that has just been opened with the
ActiveWorkbook.Name property - the problem is that Excel keeps throwing an
'Object no set' error, so obviously I'm calling the property too soon. My
question is when should I be calling this property if not in the
Workbook_Open function.

TIA,
Bill Youngman

I created a Workbook in Excel 2000 and put MsgBox ActiveWorkbook.Name in a
WorkBook_Open sub in the ThisWorkbook module. On opening, it popped up a
message box with the name.

You might want to post your code - it may not be the Workbook object that's
hanging you up.
Ed

"Bill Youngman" wrote in message
...
I've developed a SQL Server Reporting Services Application for use by my
client to track how long it is taking form/documents to be processed by
employees. On of the options of Reporting Services is the capability to
export files to different formats including Excel. The client would like
some formatting done of the spreadsheets upon opening (e.g. removing the
header banner from the Report, formatting cell colors - nothing too
complicated) by staff.

I'm writing an addin to take care of this and it's been a couple of years
since I've worked w/ VBA for Excel so I'm a little rusty. This addin needs
to verify that the workbook that has just been opened is in fact an
exported
file from Reporting Services because reformatting other workbooks that
would
be opened would be a bad thing. So I'm using the Workbook_Open function to
get the name of the workbook that has just been opened with the
ActiveWorkbook.Name property - the problem is that Excel keeps throwing an
'Object no set' error, so obviously I'm calling the property too soon. My
question is when should I be calling this property if not in the
Workbook_Open function.

TIA,
Bill Youngman

Sorry - I overlooked the "Add In" part, and did not take that into account.

Still, posting the bit of code will probably get you more help.
Ed

"Ed" wrote in message
...
I created a Workbook in Excel 2000 and put MsgBox ActiveWorkbook.Name in a
WorkBook_Open sub in the ThisWorkbook module. On opening, it popped up a
message box with the name.

You might want to post your code - it may not be the Workbook object
that's
hanging you up.
Ed

"Bill Youngman" wrote in message
...
I've developed a SQL Server Reporting Services Application for use by my
client to track how long it is taking form/documents to be processed by
employees. On of the options of Reporting Services is the capability to
export files to different formats including Excel. The client would like
some formatting done of the spreadsheets upon opening (e.g. removing the
header banner from the Report, formatting cell colors - nothing too
complicated) by staff.

I'm writing an addin to take care of this and it's been a couple of
years
since I've worked w/ VBA for Excel so I'm a little rusty. This addin
needs
to verify that the workbook that has just been opened is in fact an
exported
file from Reporting Services because reformatting other workbooks that
would
be opened would be a bad thing. So I'm using the Workbook_Open function
to
get the name of the workbook that has just been opened with the
ActiveWorkbook.Name property - the problem is that Excel keeps throwing
an
'Object no set' error, so obviously I'm calling the property too soon.
My
question is when should I be calling this property if not in the
Workbook_Open function.

TIA,
Bill Youngman

Ed,

This is the code that I am using

Sub Workbook_Open()
'Check to see if this is an SMADashboard report
Dim wb As String

wb = ActiveWorkbook.Name

MsgBox "The active workbook is " & wb
End Sub


I have this in the 'ThisWorkbook' object of my AddIn project. If I run it
from a workbook that is already open it works just fine; however, if I close
this file and then reopen it I get the 'Object reference not set' error.

Bill

"Ed" wrote in message
...
I created a Workbook in Excel 2000 and put MsgBox ActiveWorkbook.Name in a
WorkBook_Open sub in the ThisWorkbook module. On opening, it popped up a
message box with the name.

You might want to post your code - it may not be the Workbook object
that's
hanging you up.
Ed

"Bill Youngman" wrote in message
...
I've developed a SQL Server Reporting Services Application for use by my
client to track how long it is taking form/documents to be processed by
employees. On of the options of Reporting Services is the capability to
export files to different formats including Excel. The client would like
some formatting done of the spreadsheets upon opening (e.g. removing the
header banner from the Report, formatting cell colors - nothing too
complicated) by staff.

I'm writing an addin to take care of this and it's been a couple of
years
since I've worked w/ VBA for Excel so I'm a little rusty. This addin
needs
to verify that the workbook that has just been opened is in fact an
exported
file from Reporting Services because reformatting other workbooks that
would
be opened would be a bad thing. So I'm using the Workbook_Open function
to
get the name of the workbook that has just been opened with the
ActiveWorkbook.Name property - the problem is that Excel keeps throwing
an
'Object no set' error, so obviously I'm calling the property too soon.
My
question is when should I be calling this property if not in the
Workbook_Open function.

TIA,
Bill Youngman

The code is fired when the addin is loaded. If you have the addin selected
in tools=Addins, then when the addin is loaded, there is no activeworkbook.
In any event, it sounds like you want this to run when any workbook is
opened - not when the addin is loaded. So you would need to instantiate
application level events in your addin. See Chip Pearson's page on this:

http://www.cpearson.com/excel/appevent.htm

--
Regards,
Tom Ogilvy


"Bill Youngman" wrote in message
...
Ed,

This is the code that I am using

Sub Workbook_Open()
'Check to see if this is an SMADashboard report
Dim wb As String

wb = ActiveWorkbook.Name

MsgBox "The active workbook is " & wb
End Sub


I have this in the 'ThisWorkbook' object of my AddIn project. If I run it
from a workbook that is already open it works just fine; however, if I
close
this file and then reopen it I get the 'Object reference not set' error.

Bill

"Ed" wrote in message
...
I created a Workbook in Excel 2000 and put MsgBox ActiveWorkbook.Name in
a
WorkBook_Open sub in the ThisWorkbook module. On opening, it popped up
a
message box with the name.

You might want to post your code - it may not be the Workbook object
that's
hanging you up.
Ed

"Bill Youngman" wrote in message
...
I've developed a SQL Server Reporting Services Application for use by
my
client to track how long it is taking form/documents to be processed
by
employees. On of the options of Reporting Services is the capability
to
export files to different formats including Excel. The client would
like
some formatting done of the spreadsheets upon opening (e.g. removing
the
header banner from the Report, formatting cell colors - nothing too
complicated) by staff.

I'm writing an addin to take care of this and it's been a couple of
years
since I've worked w/ VBA for Excel so I'm a little rusty. This addin
needs
to verify that the workbook that has just been opened is in fact an
exported
file from Reporting Services because reformatting other workbooks that
would
be opened would be a bad thing. So I'm using the Workbook_Open
function
to
get the name of the workbook that has just been opened with the
ActiveWorkbook.Name property - the problem is that Excel keeps
throwing
an
'Object no set' error, so obviously I'm calling the property too soon.
My
question is when should I be calling this property if not in the
Workbook_Open function.

TIA,
Bill Youngman

Tom,

Thanks, that is exactly what I needed.

Bill

"Tom Ogilvy" wrote in message
...
The code is fired when the addin is loaded. If you have the addin
selected
in tools=Addins, then when the addin is loaded, there is no
activeworkbook.
In any event, it sounds like you want this to run when any workbook is
opened - not when the addin is loaded. So you would need to instantiate
application level events in your addin. See Chip Pearson's page on this:

http://www.cpearson.com/excel/appevent.htm

--
Regards,
Tom Ogilvy


"Bill Youngman" wrote in message
...
Ed,

This is the code that I am using

Sub Workbook_Open()
'Check to see if this is an SMADashboard report
Dim wb As String

wb = ActiveWorkbook.Name

MsgBox "The active workbook is " & wb
End Sub


I have this in the 'ThisWorkbook' object of my AddIn project. If I run
it
from a workbook that is already open it works just fine; however, if I
close
this file and then reopen it I get the 'Object reference not set' error.

Bill

"Ed" wrote in message
...
I created a Workbook in Excel 2000 and put MsgBox ActiveWorkbook.Name
in
a
WorkBook_Open sub in the ThisWorkbook module. On opening, it popped
up
a
message box with the name.

You might want to post your code - it may not be the Workbook object
that's
hanging you up.
Ed

"Bill Youngman" wrote in message
...
I've developed a SQL Server Reporting Services Application for use
by
my
client to track how long it is taking form/documents to be processed
by
employees. On of the options of Reporting Services is the capability
to
export files to different formats including Excel. The client would
like
some formatting done of the spreadsheets upon opening (e.g. removing
the
header banner from the Report, formatting cell colors - nothing too
complicated) by staff.

I'm writing an addin to take care of this and it's been a couple of
years
since I've worked w/ VBA for Excel so I'm a little rusty. This addin
needs
to verify that the workbook that has just been opened is in fact an
exported
file from Reporting Services because reformatting other workbooks
that
would
be opened would be a bad thing. So I'm using the Workbook_Open
function
to
get the name of the workbook that has just been opened with the
ActiveWorkbook.Name property - the problem is that Excel keeps
throwing
an
'Object no set' error, so obviously I'm calling the property too
soon.
My
question is when should I be calling this property if not in the
Workbook_Open function.

TIA,
Bill Youngman

Tom,

You keep referring to Chip's page.

My preferred method is to use ThisWorkbook's codemodule for this and
dispense with the class alltogether (as you may know).

I never had any problems, but are there any advantages to using a
separate class module (Chip's method), that I'm not aware of?
I just can't see the point.

'Code for thisworkbook object module
Option Explicit

Dim WithEvents appXL As Application

Private Sub appXL_SheetActivate(ByVal Sh As Object)
Debug.Print "Activated "; Sh.Name; " in "; Sh.Parent.Name
End Sub

Private Sub Workbook_Open()
Set appXL = Application
End Sub

I quote from Chip: "Excel also supports Application level events, such
as adding a new workbook. However, these are not as simple to
implement as worksheet and workbook level events, because there is no
user-programmable object, similar to a worksheet or workbook, that
represents the Application object"

these are not so simple?
No user-programmable object?

I disagree with Chip here... but I'm always willing to be enlightened
<g


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

The code is fired when the addin is loaded. If you have the addin
selected in tools=Addins, then when the addin is loaded, there is no
activeworkbook. In any event, it sounds like you want this to run
when any workbook is opened - not when the addin is loaded. So you
would need to instantiate application level events in your addin.
See Chip Pearson's page on this:

http://www.cpearson.com/excel/appevent.htm

keepITcool wrote:
Tom,

You keep referring to Chip's page.

My preferred method is to use ThisWorkbook's codemodule for this and
dispense with the class alltogether (as you may know).

I never had any problems, but are there any advantages to using a
separate class module (Chip's method), that I'm not aware of?
I just can't see the point.

Tom,
You keep referring to Chip's page

It's the established way of doing things <g. It takes a lot to work to
successfully challenge deeply held views.

FWIW I agree with you that ThisWorkbook is a fine place to host the
WithEvents Excel.Application object and it too is my preferred
approach. One of the regulars accused me of having bad taste for
suggesting this so seems an emotive subject. IIRC Chip reserves the
ThisWorkbook code module solely for event handlers of the Workbook
events, so at least he is consistent with his methodologies <g.

Personally, I use ThisWorkbook for all properties and methods that need
to be available globally to the workbook (and I'm not talking Public, I
mean Friends). Again, this is not the establishment's way; the crowd
prefer standard modules.

It is my opinion that using a class module to host the WithEvents
Excel.Application object is a bit counterintuitive. Is it encapsulation
for the sake of it? How many instances of this EventClass will there be
in a single project? Always and only one, I fancy. Let's face it: the
only reason it's in a class module at is because the WithEvents keyword
is only supported for class modules. If they could get away with
putting it in a standard module, they would <g.

For many people, this EventClass is their first and only class module
but it isn't really a 'proper' class. It is my opinion that such
'single use' classes can hinder newbies learning the joys of classes,
OOP and all that in VBA.

Don't get me wrong, I'm not antiestablishment (well, maybe a little
<g). Tom and Chip (and others) have helped me grasp the basics but I
never stopped thinking and discovering things for myself. Tom is kind
enough to frequently link to/re-post my code and even Chip has
previously revised his aforementioned page on EventClass after I gave
him some feedback. Yes, they do listen to the likes of us.

As I said, I agree with you but it will take a lot of effort to
challenge this established approach.

Jamie.

--

I will try to go with something like this:

for documentation and some explanation on Application Level Events, you
might want to read Chip Pearson's site
http://www.cpearson.com/excel/appevent.htm

this reflects the method presented in VBA help, but see this simpler, more
logical method suggested by KeepitCool and Further endorsed by Jamie Collins
and which uses only the Thisworkbook class module (eliminating the need to
instantiate the class and which keeps everything in one place)

[KeepitCool, otherwise known as Jurgen Volkerink @ www.XLsupport.com ]

Some discussion:
http://groups.google.co.uk/groups?se....microsoft.com
the thread:
http://groups.google.co.uk/groups?th....microsoft.com

Source of article below:
http://groups.google.co.uk/groups?se....microsoft.com

From: keepITcool )
Subject: Using Events with the Application Object & XL 2003


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2005-01-23 18:47:05 PST



What Chip doesn't mention and what I find a lot easier...

you dont need a separate class module..
Thisworkbook IS a class module so you can keep all your code
(and the withevents application variable) there.

'thisworkbook code module...
Option Explicit

Private WithEvents XlsEvents As Application

Private Sub Workbook_Open()
Set XlsEvents = Application
End Sub

Private Sub XlsEvents_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "You just opened " & Wb.Name
End Sub

--
Regards,
Tom Ogilvy



"Jamie Collins" wrote in message
ups.com...

keepITcool wrote:
Tom,

You keep referring to Chip's page.

My preferred method is to use ThisWorkbook's codemodule for this and
dispense with the class alltogether (as you may know).

I never had any problems, but are there any advantages to using a
separate class module (Chip's method), that I'm not aware of?
I just can't see the point.

Tom,
You keep referring to Chip's page

It's the established way of doing things <g. It takes a lot to work to
successfully challenge deeply held views.

FWIW I agree with you that ThisWorkbook is a fine place to host the
WithEvents Excel.Application object and it too is my preferred
approach. One of the regulars accused me of having bad taste for
suggesting this so seems an emotive subject. IIRC Chip reserves the
ThisWorkbook code module solely for event handlers of the Workbook
events, so at least he is consistent with his methodologies <g.

Personally, I use ThisWorkbook for all properties and methods that need
to be available globally to the workbook (and I'm not talking Public, I
mean Friends). Again, this is not the establishment's way; the crowd
prefer standard modules.

It is my opinion that using a class module to host the WithEvents
Excel.Application object is a bit counterintuitive. Is it encapsulation
for the sake of it? How many instances of this EventClass will there be
in a single project? Always and only one, I fancy. Let's face it: the
only reason it's in a class module at is because the WithEvents keyword
is only supported for class modules. If they could get away with
putting it in a standard module, they would <g.

For many people, this EventClass is their first and only class module
but it isn't really a 'proper' class. It is my opinion that such
'single use' classes can hinder newbies learning the joys of classes,
OOP and all that in VBA.

Don't get me wrong, I'm not antiestablishment (well, maybe a little
<g). Tom and Chip (and others) have helped me grasp the basics but I
never stopped thinking and discovering things for myself. Tom is kind
enough to frequently link to/re-post my code and even Chip has
previously revised his aforementioned page on EventClass after I gave
him some feedback. Yes, they do listen to the likes of us.

As I said, I agree with you but it will take a lot of effort to
challenge this established approach.

Jamie.

--

Tom Ogilvy wrote:
I will try to go with something like this:

for documentation and some explanation on Application Level Events,
you
might want to read Chip Pearson's site
http://www.cpearson.com/excel/appevent.htm

this reflects the method presented in VBA help, but see this simpler,
more
logical method suggested by KeepitCool and Further endorsed by Jamie
Collins
and which uses only the Thisworkbook class module (eliminating the
need to
instantiate the class and which keeps everything in one place)
<<snip

LOL! You could drop the further endorsement - it may put some people
off <g.

Jamie.

--

beware of parrying with Tom.
he has a mean riposte.

<G

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

I will try to go with something like this:

for documentation and some explanation on Application Level Events,
you might want to read Chip Pearson's site
http://www.cpearson.com/excel/appevent.htm

this reflects the method presented in VBA help, but see this simpler,
more logical method suggested by KeepitCool and Further endorsed by
Jamie Collins and which uses only the Thisworkbook class module
(eliminating the need to instantiate the class and which keeps
everything in one place)

[KeepitCool, otherwise known as Jurgen Volkerink @ www.XLsupport.com ]