What am I missing?

I've just installed Excel 2003 and I'm trying to write a custom add-in to
format RS reports exported to Excel.

I'm using Workbook_Open to accomplish the formatting and I've saved my work
book as an Excel Add-in (.xla) in the Addin directory. The problem I'm
having is that when I make changes/additions to the code in VBA when I go to
Excel and open one of the reports with the current instance of Excel nothing
happens, it's as if Excel isn't even seeing the add-in. However, it I go out
and open up a new instance of Excel the code will execute.

I've got the lowest level of security set in Excel and I have the add-in
selected in Tools Addins.

I don't remember it being this difficult in Excel 2000 - I seem to remember
that I wrote my code and was able to test it with the same instance of
Excel.

Like I said at the top - What am I missing?

TIA,
Bill Youngman

Hi Bill

The addin's Workbook_open event runs when the /addin/ is opened, not when
anything else is opened. It has always been like this. Consider a
toolbarbutton, a menu item or something similar as an action starter.

HTH. Best wishes Harald

"Bill Youngman" skrev i melding
...
What am I missing?

I've just installed Excel 2003 and I'm trying to write a custom add-in to
format RS reports exported to Excel.

I'm using Workbook_Open to accomplish the formatting and I've saved my
work
book as an Excel Add-in (.xla) in the Addin directory. The problem I'm
having is that when I make changes/additions to the code in VBA when I go
to
Excel and open one of the reports with the current instance of Excel
nothing
happens, it's as if Excel isn't even seeing the add-in. However, it I go
out
and open up a new instance of Excel the code will execute.

I've got the lowest level of security set in Excel and I have the add-in
selected in Tools Addins.

I don't remember it being this difficult in Excel 2000 - I seem to
remember
that I wrote my code and was able to test it with the same instance of
Excel.

Like I said at the top - What am I missing?

TIA,
Bill Youngman

Earlier in the year I had written an addin that when the user opened a
report that they had exported to Excel from Reporting Services it performed
the following -

- it first verified that the file was a report by checking the file name of
the file being opened (that way the subsequent formatting wouldn't be
performed on another workbook or try and format a new workbook).

- unmerge all merged cells and then reformat the display of the worksheet.

Unfortunately when I got my new laptop from my company that addin was blown
away and now for the life of me I can't remember what I did.

I haven't had too much experience with Excel VBA programming so any help to
get me pointed in the right direction would be greatly appreciated.

Thanks

"Harald Staff" wrote in message
...
Hi Bill

The addin's Workbook_open event runs when the /addin/ is opened, not when
anything else is opened. It has always been like this. Consider a
toolbarbutton, a menu item or something similar as an action starter.

HTH. Best wishes Harald

"Bill Youngman" skrev i melding
...
What am I missing?

I've just installed Excel 2003 and I'm trying to write a custom add-in to
format RS reports exported to Excel.

I'm using Workbook_Open to accomplish the formatting and I've saved my
work
book as an Excel Add-in (.xla) in the Addin directory. The problem I'm
having is that when I make changes/additions to the code in VBA when I go
to
Excel and open one of the reports with the current instance of Excel
nothing
happens, it's as if Excel isn't even seeing the add-in. However, it I go
out
and open up a new instance of Excel the code will execute.

I've got the lowest level of security set in Excel and I have the add-in
selected in Tools Addins.

I don't remember it being this difficult in Excel 2000 - I seem to
remember
that I wrote my code and was able to test it with the same instance of
Excel.

Like I said at the top - What am I missing?

TIA,
Bill Youngman

Hi Bill

It's late evening here in Norway. I'll compose something for you tomorrow,
stay tuned.

Best wishes Harald

"Bill Youngman" skrev i melding
...
Earlier in the year I had written an addin that when the user opened a
report that they had exported to Excel from Reporting Services it
performed
the following -

- it first verified that the file was a report by checking the file name
of
the file being opened (that way the subsequent formatting wouldn't be
performed on another workbook or try and format a new workbook).

- unmerge all merged cells and then reformat the display of the worksheet.

Unfortunately when I got my new laptop from my company that addin was
blown
away and now for the life of me I can't remember what I did.

I haven't had too much experience with Excel VBA programming so any help
to
get me pointed in the right direction would be greatly appreciated.

Thanks

"Harald Staff" wrote in message
...
Hi Bill

The addin's Workbook_open event runs when the /addin/ is opened, not
when
anything else is opened. It has always been like this. Consider a
toolbarbutton, a menu item or something similar as an action starter.

HTH. Best wishes Harald

"Bill Youngman" skrev i melding
...
What am I missing?

I've just installed Excel 2003 and I'm trying to write a custom add-in
to
format RS reports exported to Excel.

I'm using Workbook_Open to accomplish the formatting and I've saved my
work
book as an Excel Add-in (.xla) in the Addin directory. The problem I'm
having is that when I make changes/additions to the code in VBA when I
go
to
Excel and open one of the reports with the current instance of Excel
nothing
happens, it's as if Excel isn't even seeing the add-in. However, it I
go
out
and open up a new instance of Excel the code will execute.

I've got the lowest level of security set in Excel and I have the
add-in
selected in Tools Addins.

I don't remember it being this difficult in Excel 2000 - I seem to
remember
that I wrote my code and was able to test it with the same instance of
Excel.

Like I said at the top - What am I missing?

TIA,
Bill Youngman

Good morning Bill

(Lurkers: This is a very useful little piece of code. Save and test if you
are learning Excel VBA)

This code places a custom entry "Open Report" in the file menu. Place this
code in the ThisWorkbook module of your addin:

' ***************** top of block **********************
Option Explicit

Private Sub Workbook_Open()
Dim cmbFile As CommandBarPopup
Dim cmbCustom As CommandBarButton
Call DeleteCmb
Set cmbFile = Application.CommandBars(1).FindControl(, 30002)
Set cmbCustom = cmbFile.Controls.Add(msoControlButton, _
Befo=3, Temporary:=True)
With cmbCustom
.Caption = "Open &report..."
.OnAction = "'" & ThisWorkbook.Name & "'!ReportCode"
End With
Set cmbCustom = Nothing
Set cmbFile = Nothing
End Sub

Private Sub DeleteCmb()
Dim cmbFile As CommandBarPopup
On Error Resume Next
Set cmbFile = Application.CommandBars(1).FindControl(, 30002)
cmbFile.Controls("Open &report...").Delete
Set cmbFile = Nothing
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteCmb
End Sub

' ***************** end of block **********************

Put this code in a standard module (menu Insert Module) in your addin.
This is the code that runs when you click the custom menu item, so this is
where you put all the code regarding the reports:

' ***************** top of block **********************

Sub ReportCode()
Dim V As Variant
'change file suffix to fit:
V = Application.GetOpenFilename("Reports (*.xls), *.xls", , _
"Pick a report:")
If V = False Then Exit Sub

'add filename validation or whatever here, befo
Workbooks.Open (CStr(V))
DoEvents

'action here, instead of
MsgBox "Place formatting code and everything eler here"
End Sub

' ***************** end of block **********************

OK ?
I will not lecture you on backups ;-)

HTH. Best wishes Harald




"Bill Youngman" skrev i melding
...
Earlier in the year I had written an addin that when the user opened a
report that they had exported to Excel from Reporting Services it
performed the following -

- it first verified that the file was a report by checking the file name
of the file being opened (that way the subsequent formatting wouldn't be
performed on another workbook or try and format a new workbook).

- unmerge all merged cells and then reformat the display of the worksheet.

Unfortunately when I got my new laptop from my company that addin was
blown away and now for the life of me I can't remember what I did.

I haven't had too much experience with Excel VBA programming so any help
to get me pointed in the right direction would be greatly appreciated.

Thanks

To wrap this up - I figured it out and went out and got myself a book on
Excel programming so in the future I won't have to give myself a brain cramp
trying to remember how to do this.

"Bill Youngman" wrote in message
...
What am I missing?

I've just installed Excel 2003 and I'm trying to write a custom add-in to
format RS reports exported to Excel.

I'm using Workbook_Open to accomplish the formatting and I've saved my
work book as an Excel Add-in (.xla) in the Addin directory. The problem
I'm having is that when I make changes/additions to the code in VBA when I
go to Excel and open one of the reports with the current instance of Excel
nothing happens, it's as if Excel isn't even seeing the add-in. However,
it I go out and open up a new instance of Excel the code will execute.

I've got the lowest level of security set in Excel and I have the add-in
selected in Tools Addins.

I don't remember it being this difficult in Excel 2000 - I seem to
remember that I wrote my code and was able to test it with the same
instance of Excel.

Like I said at the top - What am I missing?

TIA,
Bill Youngman