I have the following code which I attained from site in part:
Sub AddControl()
Dim ctls As CmdBarControls
Dim button As CmdBarControl
Set ctls = Application.CmdBars("Standard").Controls
Set button = ctls.Add(boControlButton)
'define the button
button.OnAction = "Tools.rea!mdlAddInTabSave.frmMain.show"
button.Caption = "&Report Tab Addin..."
button.DescriptionText = "Report Tab Addin"
button.TooltipText = "Show addin form"
Application.Clipboard.SetData frmMain.Image1.Picture
button.PasteFace
Set ctls = Nothing
Set button = Nothing
End Sub
However, it generates a button that is greyed out and disabled. I think I am missing something here. Any help apprecaited.
It could be the onaction event is wrong, but I also tried application.activedocument.refresh and it was still greyed out. In any case, the form did open up when I stepped through the code.
The .OnAction property is indeed your problem. It needs to refer to a procedure name, not be formatted as a line of code. Put your code in a procedure, and set the .OnAction property to the name of that procedure.
Depends on what you are trying to accomplish. If you want the toolbar to be built when the document opens, the code needs to be in (or called from) the open event of the ThisDocument module:
I have added the sub to the code and am calling it from the event open_document. however I still can not see the button in the tool bar in business objects?
do i need to use addins? something I have read on various websites but never used?
Was that a typo, or is your procedure really named open_document? It needs to be named Document_Open. The best way to create the procedure is to use the drop-down boxes in the VB Editor.
'Public GetDownLoad As DownLoadToExcelClass
Private Sub Document_Activate()
Call AddControl
End Sub
Private Sub Document_AfterRefresh()
Dim GetDownLoad As DownLoadToExcelClass
Set GetDownLoad = New DownLoadToExcelClass
If GetDownLoad.Process_Report() = True Then
Call GetDownLoad.ExpToExcel(GetDownLoad.TabName, GetDownLoad.Path)
End If
End Sub
Private Sub Document_Open()
Call AddControl
End Sub
Private Sub Document_BeforeClose(Cancel As Boolean)
Dim
'before closing, tidy up BO HTML files
If GetDownLoad.TidyUp = True Then
End If
End Sub
Sub AddControl()
Dim ctls As CmdBarControls
Dim button As CmdBarControl
Set ctls = Application.CmdBars("Standard").Controls
Set button = ctls.Add(boControlButton)
'define the button
button.OnAction = "Tools.rea!mdlAddInTabSave.frmMain.show"
button.Caption = "&Report Tab Addin..."
button.DescriptionText = "Report Tab Addin"
button.TooltipText = "Show addin form"
'Application.Clipboard.SetData frmMain.Image1.Picture
'button.PasteFace
button.Caption = "Generate Report"
Set ctls = Nothing
Set button = Nothing
End Sub
here is my class module "DownLoadToExcelClass"
Public GetDownLoad As New DownLoadToExcelClass
Public MyDoc As busobj.Document
'Dim strTabName As String
'Dim strDocumentName As String
'Dim strPath As String
Function Process_Report() As Boolean
'If the user has the basedata.htm file open then the export will not update
'and the previous run will be exported to excel.
Call MyDoc.ExportSheetsAsHtml(GetDownLoad.Path & "\" & GetDownLoad.DocumentName, GetDownLoad.TabName, 1, 1, 1, 1, 1, 1, 1, 0, False, 0)
Process_Report = True
Exit Function
Errhandler:
If Err <> 0 Then
MyDoc.Close
Exit Function
End If
End Function
Function TidyUp() As Boolean
'incomplete
strTidyUpFile As String
strTidyUpFile = GetDownLoad.Path & "\" & MyDoc.Name & "\" & strTabName & "\" & strTabName & ".htm"
End Function
Function ExpToExcel(strTabName As String, strPath As String) As Boolean
'This function can be re-run without closing down excel. Each time it is run a new
'instance of Excel will be opened. No handling to close down existing open apps incase
'the user has other spreadsheets open.
Dim ExcelApp As Object
Dim strExcelFile As String
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
ExcelApp.Interactive = True
ExcelApp.Workbooks.Open (strPath & "\" & MyDoc.Name & "\" & strTabName & "\" & strTabName & ".htm")
'Commented out functionality to autosave is not required.
strExcelFile = strPath & "\" & strTabName & ".xls"
ExcelApp.ActiveWorkBook.SaveAs (strExcelFile)
ExcelApp.Quit
Set ExcelApp = Nothing
ExpToExcel = True
End Function
Private Sub Class_Initialize()
'Set the variables
Set MyDoc = busobj.ActiveDocument
End Sub
Private Sub Class_Terminate()
End Sub
Property Get Path() As String
Path = MyDoc.Path
End Property
Property Get DocumentName() As String
DocumentName = MyDoc.Name
End Property
Property Get TabName() As String
TabName = "BaseData"
End Property
You’ve commented out the lines that put a “face” on the button. I think it is getting added at the end of the toolbar, but since it has a blank “face” it doesn’t look like it’s there.
That technique uses a copy / paste of a bitmap from a userform to set the “face” of the button. Alternatively, you can set the .FaceID property to a value that represents one of the internal buttons.
How do you close this tool bar once, the active document is closed. I mean, even after I exit from that report, I still see the tool bar. This means, if I run the report again, I will get one more tool bar!
You will need to do that checking in the code itself. Look at the code in this utility. It demonstrates one technique for preventing duplicates like you are describing. In other utilities, I have written a “looping” function to go through the toolbars to perform a similar check. You can consider that as well.
(BOB member since 2002-09-19)
.
(BOB member since 2004-05-03)