+ Reply to Thread
Results 1 to 6 of 6

Excel 2003 & add-in programming

  1. #1
    Bill Youngman
    Guest

    Excel 2003 & add-in programming

    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



  2. #2
    Harald Staff
    Guest

    Re: Excel 2003 & add-in programming

    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" <[email protected]> skrev i melding
    news:[email protected]...
    > 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
    >
    >




  3. #3
    Bill Youngman
    Guest

    Re: Excel 2003 & add-in programming

    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" <[email protected]> wrote in message
    news:%[email protected]...
    > 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" <[email protected]> skrev i melding
    > news:[email protected]...
    >> 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
    >>
    >>

    >
    >




  4. #4
    Harald Staff
    Guest

    Re: Excel 2003 & add-in programming

    Hi Bill

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

    Best wishes Harald

    "Bill Youngman" <[email protected]> skrev i melding
    news:%[email protected]...
    > 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" <[email protected]> wrote in message
    > news:%[email protected]...
    > > 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" <[email protected]> skrev i melding
    > > news:[email protected]...
    > >> 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
    > >>
    > >>

    > >
    > >

    >
    >




  5. #5
    Harald Staff
    Guest

    Re: Excel 2003 & add-in programming

    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, _
    Before:=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, before:
    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" <[email protected]> skrev i melding
    news:%[email protected]...
    > 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




  6. #6
    Bill Youngman
    Guest

    Re: Excel 2003 & add-in programming

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1