J
Jim B
Hi,
I'm a little new to this so bear with me. My background is mainly data
warehouse design and development but have done some OOP development in the
past but it's been a while.
Anyway, I've been working on a shared add-in for Excel 2003 which will build
a custom reporting toolbar that changes in content (buttons) based on a users
logon credentials in a Windows 2003 server environment.
I started by building a separate class that will eventually retrieve the
user and build the tool bar based on the user's logon credentials in AD and
other SQL tables that will contain report definitions and access by user
group. I then compiled it as a dll for use as an add-in reference later. I
called it testclass.dll since I'm at the very beginning and am testing the
idea first. I then added it as a reference within the shared add-in project
I built. I included an imports statement with the class I need to build the
toolbar and it accesses it fine.
The following statements are what I used to import the namespace for the
dll, instantiate the class and launch the method within the class to build
the toolbar.
Imports testclass
Dim WithEvents cl1 As New BuildDAToolBar
OnStartupcomplete
cl1.AddDACommandBar()
The problem is when I compile the code and install the add-in it doesnt
launch the method. So I debugged the testclass by using a windows app
project that refrenced the testclass.dll as an add-in class. That worked
fine.
Could it be the way defined the class or instantiated it?
Here's the code for the test class:
-------------------------------------------------------------------------
Imports Microsoft.Office.Core
Imports System.Runtime.InteropServices
Imports xCel = Microsoft.Office.Interop.Excel
'ON ACTION WILL HAVE TO CALL THE METHOD FROM CLASS THAT RETRIEVES DATA
' AND BUILDS THE REPORT.
Public Class BuildDAToolBar
Dim cbDA As CommandBar
Dim curApp As xCel.Application
Public ReadOnly Property Userid() As String
Get
Return getUser()
End Get
End Property
Public Sub AddDACommandBar()
'add call from addin with user logon credentials
cbDA = curApp.CommandBars.Add("Data Analysis",
MsoBarPosition.msoBarTop, False, True)
' Set properties of command bar to be on top and protect from
moving, resizing, and
' customizing.
With cbDA
.Enabled = True
.Visible = True
.Protection = MsoBarProtection.msoBarNoMove + _
MsoBarProtection.msoBarNoMove + _
MsoBarProtection.msoBarNoChangeVisible + _
MsoBarProtection.msoBarNoChangeDock + _
MsoBarProtection.msoBarNoCustomize + _
MsoBarProtection.msoBarNoResize
End With
'Build command buttons for DA bar.
AddDACommandButtons()
End Sub
Private Sub AddDACommandButtons()
'
' Add controls to the data analysis toolbar.
'
Dim cbtDA As CommandBarButton
Dim cbtCBDA As CommandBarComboBox
cbDA = curApp.CommandBars("Data Analysis")
' Add Report list
' build list from SQL Call
cbtCBDA = cbDA.Controls.Add(MsoControlType.msoControlDropdown)
With cbtCBDA
.Style = MsoComboStyle.msoComboNormal
.TooltipText = "List of available reports."
.AddItem("Account Sales")
.AddItem("Country Sales")
.AddItem("Customer Invoice History Summary")
.AddItem("MTD Sales")
.Parameter = "RL"
.Tag = "RL"
.OnAction = "<Class1!TestContol()>"
.Visible = True
.Enabled = True
.ListIndex = 1
End With
' Add year for applicable report (namely MTD Sales) - pull as
distinct list from SQL view
cbtCBDA = cbDA.Controls.Add(MsoControlType.msoControlDropdown)
With cbtCBDA
.Style = MsoComboStyle.msoComboNormal
.TooltipText = "Calendar year for report to be generated."
.AddItem("2006")
.AddItem("2005")
.AddItem("2004")
.AddItem("2003")
.Parameter = "YR"
.Tag = "YR"
.OnAction = "<BuildDAToolBar!TestContol()>"
.Visible = True
.Enabled = True
.ListIndex = 1
.Enabled = False
End With
'Enter remaining icon with caption - Refresh, Copy, Print
cbtDA = cbDA.Controls.Add(MsoControlType.msoControlButton)
With cbtDA
.Style = MsoButtonStyle.msoButtonIconAndCaption
.TooltipText = "Reload report data."
.FaceId = 6068 '2608
.Caption = "Refresh"
.Parameter = "RF"
.Tag = "RF"
.OnAction = "<BuildDAToolBar!TestContol()>"
.Visible = True
.Enabled = True
End With
cbtDA = cbDA.Controls.Add(MsoControlType.msoControlButton)
With cbtDA
.Style = MsoButtonStyle.msoButtonIconAndCaption
.TooltipText = "Save a copy of report."
.FaceId = 2778
.Caption = "Copy"
.Parameter = "CY"
.Tag = "CY"
.OnAction = "<BuildDAToolBar!TestContol()>"
.Visible = True
.Enabled = True
End With
cbtDA = cbDA.Controls.Add(MsoControlType.msoControlButton, 4)
With cbtDA
.Style = MsoButtonStyle.msoButtonIconAndCaption
.TooltipText = "Print a copy of the report."
.FaceId = 4
.Caption = "Print"
.Parameter = "PR"
.Tag = "PR"
.Visible = True
.Enabled = True
End With
cbtDA = cbDA.Controls.Add(MsoControlType.msoControlButton)
With cbtDA
.Style = MsoButtonStyle.msoButtonIconAndCaption
.TooltipText = "Retrieve Current User."
.FaceId = 4
.Caption = "User Retrieve"
.Parameter = "RU"
.OnAction = "<BuildDAToolBar!TestContol()>"
.Tag = "PR"
.Visible = True
.Enabled = True
End With
End Sub
Private Sub TestControl()
With curApp.CommandBars
Select Case .ActionControl.Parameter
Case "RL"
If .FindControl(Tag:="RL").ListIndex = 4 Then
.FindControl(Tag:="YR").Enabled = True
Else
.FindControl(Tag:="YR").Enabled = True
.FindControl(Tag:="YR").ListIndex = 1
.FindControl(Tag:="YR").Enabled = False
End If
Case "CY"
curApp.ActiveWorkbook.SaveAs()
Case "RU"
getUser()
Case Else
MsgBox(.ActionControl.List(.ActionControl.ListIndex))
End Select
End With
End Sub
Private Function getUser() As String
Return GetUserDomain() + " : " + GetUserName()
End Function
Private Function GetUserDomain() As String
If TypeOf My.User.CurrentPrincipal Is _
Security.Principal.WindowsPrincipal Then
' My.User is using Windows authentication.
' The name format is DOMAIN\USERNAME.
Dim parts() As String = Split(My.User.Name, "\")
Dim domain As String = parts(0)
Return domain
Else
' My.User is using custom authentication.
Return ""
End If
End Function
Private Function GetUserName() As String
If TypeOf My.User.CurrentPrincipal Is _
Security.Principal.WindowsPrincipal Then
' The application is using Windows authentication.
' The name format is DOMAIN\USERNAME.
Dim parts() As String = Split(My.User.Name, "\")
Dim username As String = parts(1)
Return username
Else
' The application is using custom authentication.
Return My.User.Name
End If
End Function
End Class
----------------------------------------------------------------------------------------
Here's my code for the extensible shared add-in:
-------------------------------------------------------------------------
Imports Microsoft.Office.Core
Imports Extensibility
Imports testclass
Imports System.Runtime.InteropServices
<GuidAttribute("7B208F7B-3DEA-4BA1-BBEB-10D2D9070EDF"),
ProgIdAttribute("MyAddin1.Connect")> _
Public Class Connect
Implements Extensibility.IDTExtensibility2
Dim WithEvents MyButton As CommandBarButton
Dim WithEvents cl1 As New BuildDAToolBar
Dim applicationObject As Object
Dim addInInstance As Object
Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnBeginShutdown
MyButton.Delete()
MyButton = Nothing
End Sub
Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnAddInsUpdate
End Sub
Public Sub OnStartupComplete(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnStartupComplete
MyButton = applicationObject.CommandBars("Standard").Controls.Add(1)
MyButton.Caption = "My Button"
MyButton.Style = MsoButtonStyle.msoButtonCaption
MyButton.Tag = "My Button"
MyButton.OnAction = "!<MyAddin1.Connect>"
End Sub
Public Sub OnDisconnection(ByVal RemoveMode As
Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnDisconnection
End Sub
Public Sub OnConnection(ByVal application As Object, ByVal connectMode As
Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As
System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
applicationObject = application
addInInstance = addInInst
End Sub
Private Sub MyButton_Click(ByVal Ctrl As
Microsoft.Office.Core.CommandBarButton, _
ByRef CancelDefault As Boolean) Handles MyButton.Click
'test button to see if I can retrieve the user id and call the tool
bar add
MsgBox(cl1.Userid)
cl1.AddDACommandBar()
End Sub
End Class
-------------------------------------------------------------------------
The code is still in-progress since I'm testing an idea. It's not very
complex but I'm new to VS2005 and it's been a few years since I've done this
kind of development.
I will say this that the VS2005 development platform is pretty sophisticated
and great to work with.
Anyway, I really could use someone's development expertise with this.
I'm a little new to this so bear with me. My background is mainly data
warehouse design and development but have done some OOP development in the
past but it's been a while.
Anyway, I've been working on a shared add-in for Excel 2003 which will build
a custom reporting toolbar that changes in content (buttons) based on a users
logon credentials in a Windows 2003 server environment.
I started by building a separate class that will eventually retrieve the
user and build the tool bar based on the user's logon credentials in AD and
other SQL tables that will contain report definitions and access by user
group. I then compiled it as a dll for use as an add-in reference later. I
called it testclass.dll since I'm at the very beginning and am testing the
idea first. I then added it as a reference within the shared add-in project
I built. I included an imports statement with the class I need to build the
toolbar and it accesses it fine.
The following statements are what I used to import the namespace for the
dll, instantiate the class and launch the method within the class to build
the toolbar.
Imports testclass
Dim WithEvents cl1 As New BuildDAToolBar
OnStartupcomplete
cl1.AddDACommandBar()
The problem is when I compile the code and install the add-in it doesnt
launch the method. So I debugged the testclass by using a windows app
project that refrenced the testclass.dll as an add-in class. That worked
fine.
Could it be the way defined the class or instantiated it?
Here's the code for the test class:
-------------------------------------------------------------------------
Imports Microsoft.Office.Core
Imports System.Runtime.InteropServices
Imports xCel = Microsoft.Office.Interop.Excel
'ON ACTION WILL HAVE TO CALL THE METHOD FROM CLASS THAT RETRIEVES DATA
' AND BUILDS THE REPORT.
Public Class BuildDAToolBar
Dim cbDA As CommandBar
Dim curApp As xCel.Application
Public ReadOnly Property Userid() As String
Get
Return getUser()
End Get
End Property
Public Sub AddDACommandBar()
'add call from addin with user logon credentials
cbDA = curApp.CommandBars.Add("Data Analysis",
MsoBarPosition.msoBarTop, False, True)
' Set properties of command bar to be on top and protect from
moving, resizing, and
' customizing.
With cbDA
.Enabled = True
.Visible = True
.Protection = MsoBarProtection.msoBarNoMove + _
MsoBarProtection.msoBarNoMove + _
MsoBarProtection.msoBarNoChangeVisible + _
MsoBarProtection.msoBarNoChangeDock + _
MsoBarProtection.msoBarNoCustomize + _
MsoBarProtection.msoBarNoResize
End With
'Build command buttons for DA bar.
AddDACommandButtons()
End Sub
Private Sub AddDACommandButtons()
'
' Add controls to the data analysis toolbar.
'
Dim cbtDA As CommandBarButton
Dim cbtCBDA As CommandBarComboBox
cbDA = curApp.CommandBars("Data Analysis")
' Add Report list
' build list from SQL Call
cbtCBDA = cbDA.Controls.Add(MsoControlType.msoControlDropdown)
With cbtCBDA
.Style = MsoComboStyle.msoComboNormal
.TooltipText = "List of available reports."
.AddItem("Account Sales")
.AddItem("Country Sales")
.AddItem("Customer Invoice History Summary")
.AddItem("MTD Sales")
.Parameter = "RL"
.Tag = "RL"
.OnAction = "<Class1!TestContol()>"
.Visible = True
.Enabled = True
.ListIndex = 1
End With
' Add year for applicable report (namely MTD Sales) - pull as
distinct list from SQL view
cbtCBDA = cbDA.Controls.Add(MsoControlType.msoControlDropdown)
With cbtCBDA
.Style = MsoComboStyle.msoComboNormal
.TooltipText = "Calendar year for report to be generated."
.AddItem("2006")
.AddItem("2005")
.AddItem("2004")
.AddItem("2003")
.Parameter = "YR"
.Tag = "YR"
.OnAction = "<BuildDAToolBar!TestContol()>"
.Visible = True
.Enabled = True
.ListIndex = 1
.Enabled = False
End With
'Enter remaining icon with caption - Refresh, Copy, Print
cbtDA = cbDA.Controls.Add(MsoControlType.msoControlButton)
With cbtDA
.Style = MsoButtonStyle.msoButtonIconAndCaption
.TooltipText = "Reload report data."
.FaceId = 6068 '2608
.Caption = "Refresh"
.Parameter = "RF"
.Tag = "RF"
.OnAction = "<BuildDAToolBar!TestContol()>"
.Visible = True
.Enabled = True
End With
cbtDA = cbDA.Controls.Add(MsoControlType.msoControlButton)
With cbtDA
.Style = MsoButtonStyle.msoButtonIconAndCaption
.TooltipText = "Save a copy of report."
.FaceId = 2778
.Caption = "Copy"
.Parameter = "CY"
.Tag = "CY"
.OnAction = "<BuildDAToolBar!TestContol()>"
.Visible = True
.Enabled = True
End With
cbtDA = cbDA.Controls.Add(MsoControlType.msoControlButton, 4)
With cbtDA
.Style = MsoButtonStyle.msoButtonIconAndCaption
.TooltipText = "Print a copy of the report."
.FaceId = 4
.Caption = "Print"
.Parameter = "PR"
.Tag = "PR"
.Visible = True
.Enabled = True
End With
cbtDA = cbDA.Controls.Add(MsoControlType.msoControlButton)
With cbtDA
.Style = MsoButtonStyle.msoButtonIconAndCaption
.TooltipText = "Retrieve Current User."
.FaceId = 4
.Caption = "User Retrieve"
.Parameter = "RU"
.OnAction = "<BuildDAToolBar!TestContol()>"
.Tag = "PR"
.Visible = True
.Enabled = True
End With
End Sub
Private Sub TestControl()
With curApp.CommandBars
Select Case .ActionControl.Parameter
Case "RL"
If .FindControl(Tag:="RL").ListIndex = 4 Then
.FindControl(Tag:="YR").Enabled = True
Else
.FindControl(Tag:="YR").Enabled = True
.FindControl(Tag:="YR").ListIndex = 1
.FindControl(Tag:="YR").Enabled = False
End If
Case "CY"
curApp.ActiveWorkbook.SaveAs()
Case "RU"
getUser()
Case Else
MsgBox(.ActionControl.List(.ActionControl.ListIndex))
End Select
End With
End Sub
Private Function getUser() As String
Return GetUserDomain() + " : " + GetUserName()
End Function
Private Function GetUserDomain() As String
If TypeOf My.User.CurrentPrincipal Is _
Security.Principal.WindowsPrincipal Then
' My.User is using Windows authentication.
' The name format is DOMAIN\USERNAME.
Dim parts() As String = Split(My.User.Name, "\")
Dim domain As String = parts(0)
Return domain
Else
' My.User is using custom authentication.
Return ""
End If
End Function
Private Function GetUserName() As String
If TypeOf My.User.CurrentPrincipal Is _
Security.Principal.WindowsPrincipal Then
' The application is using Windows authentication.
' The name format is DOMAIN\USERNAME.
Dim parts() As String = Split(My.User.Name, "\")
Dim username As String = parts(1)
Return username
Else
' The application is using custom authentication.
Return My.User.Name
End If
End Function
End Class
----------------------------------------------------------------------------------------
Here's my code for the extensible shared add-in:
-------------------------------------------------------------------------
Imports Microsoft.Office.Core
Imports Extensibility
Imports testclass
Imports System.Runtime.InteropServices
<GuidAttribute("7B208F7B-3DEA-4BA1-BBEB-10D2D9070EDF"),
ProgIdAttribute("MyAddin1.Connect")> _
Public Class Connect
Implements Extensibility.IDTExtensibility2
Dim WithEvents MyButton As CommandBarButton
Dim WithEvents cl1 As New BuildDAToolBar
Dim applicationObject As Object
Dim addInInstance As Object
Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnBeginShutdown
MyButton.Delete()
MyButton = Nothing
End Sub
Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnAddInsUpdate
End Sub
Public Sub OnStartupComplete(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnStartupComplete
MyButton = applicationObject.CommandBars("Standard").Controls.Add(1)
MyButton.Caption = "My Button"
MyButton.Style = MsoButtonStyle.msoButtonCaption
MyButton.Tag = "My Button"
MyButton.OnAction = "!<MyAddin1.Connect>"
End Sub
Public Sub OnDisconnection(ByVal RemoveMode As
Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnDisconnection
End Sub
Public Sub OnConnection(ByVal application As Object, ByVal connectMode As
Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As
System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
applicationObject = application
addInInstance = addInInst
End Sub
Private Sub MyButton_Click(ByVal Ctrl As
Microsoft.Office.Core.CommandBarButton, _
ByRef CancelDefault As Boolean) Handles MyButton.Click
'test button to see if I can retrieve the user id and call the tool
bar add
MsgBox(cl1.Userid)
cl1.AddDACommandBar()
End Sub
End Class
-------------------------------------------------------------------------
The code is still in-progress since I'm testing an idea. It's not very
complex but I'm new to VS2005 and it's been a few years since I've done this
kind of development.
I will say this that the VS2005 development platform is pretty sophisticated
and great to work with.
Anyway, I really could use someone's development expertise with this.