Command Button

system · November 1, 2002, 1:49pm

Would anyone know hos to create a command button on a report which would execute a macro?
The idea behind it is that first time users would only have to hit the button and everything would be done for them. (The macro creates the documentation template and refreshes the document).

Olivier Masse (BOB member since 2002-08-29)

system · November 1, 2002, 1:54pm

Does this help? If you go to View/Toolbars and select Visual Basic. Then if you go to Tools/Options/Macros, you can use this dialogue to attach your macro to one of the buttons on the VBA toolbar.

Nick Daniels (BOB member since 2002-08-15)

system · November 1, 2002, 2:12pm

That probably wouldn’t do because it would mean having to go and install this on every potential Users PC. And then attach the macro as well.
It was why I thought of a command button like in MS Access.

Olivier Masse (BOB member since 2002-08-29)

system · November 1, 2002, 2:24pm

Okay I’ll move this into the VBA forum then. Bear in mind you could probably distribute a fix for my first suggestion via a registry key and add in an email…

Nick Daniels (BOB member since 2002-08-15)

system · November 1, 2002, 2:27pm

I think this is beyond my capabilities at the moment…
I wouldn’t know how to do this.

Olivier Masse (BOB member since 2002-08-29)

system · November 1, 2002, 2:39pm

With Bob nothing is beyond your capabilities

Save your macro as an Add In - nothing too painful there. Then what you would do for the registry part is basically setup your PC as I described. Then after a bit of searching around in the registry you will find the relevant key which says put macro X from location Y behind VBA macro button 1. You can then export this key from the registry - it just creates a file e.g. Oliviers Macro Fix.reg. Add the .reg file and .rea file to an email with some basic instructions about save the .rea file here and double click to launch the .reg file and the jobs a good 'un. I realise this is a little sketchy on detail but its do-able. You don’t even have to distribute the .rea file if you make it available on a common network drive

Nick Daniels (BOB member since 2002-08-15)

system · November 1, 2002, 2:46pm

I’ll keep that in mind for another use…
Our IT Department, which is outsourced, owns the rights to our registry.
We cannot change anything…
So it is back to the command button I am afraid.
But thanks for the explanation, it is useful.

Olivier Masse (BOB member since 2002-08-29)

system · November 1, 2002, 2:48pm

Oh, unlucky. But bear in mind you are using BO to change the registry. Or rather, the changes you make in BO are saved in the registry. So all you would have to do would be identify the relevant key and ask IT to export it for you…I’ll shut up now!

Nick Daniels (BOB member since 2002-08-15)

system · November 1, 2002, 3:01pm

I wish… Our IT Department is in Manchester, and we are based in Bristol…
They will not allow anything… But I am sure I can put this to use in a future…

Olivier Masse (BOB member since 2002-08-29)

system · November 1, 2002, 3:08pm

I’m really dragging this out and I’m sorry for that - incidentally I’m about to go and work for a competitor of yours so all this information could be complete rubbish ( )

Anyway, I’m just wondering how well they have hidden regedit. For example, when you click on start do you get a Run option - if so you can just type in regedit - and away you go. No Run option? Welll, if you go into Word and go to help and about then select System Info - if this hasn’t been disabled you can do regedit from there. There are a few more options but I’ll leave it at that. Good luck

Nick Daniels (BOB member since 2002-08-15)

system · November 1, 2002, 3:19pm

Just tried it… It is disable…
I hate to say this but they know how to protect themselves…

Olivier Masse (BOB member since 2002-08-29)

system · November 1, 2002, 3:30pm

Try this! Start Word. Go to Tools/Macro/Macros. Create a new macro. In between the macro header and footer type in

shell("regedit")

.

I never told you this see if it opens the registry on the task bar - it won’t open it up as the main window - useful to know anyway…

Nick Daniels (BOB member since 2002-08-15)

system · November 1, 2002, 3:34pm

I like the trick, but unfortunately I get the usual message: “Registry Editing has been disabled by your administrator”…

Olivier Masse (BOB member since 2002-08-29)

system · November 4, 2002, 3:51pm

Is it possible we’ve wandered from the original question? This code is from my “Data Provider Utilities” add-in (used to copy a data provider) posted here recently. It goes in the ThisDocument module, and creates a custom menu item (or new toolbar if the preferred menu location doesn’t exist) whenever the document is opened. The OnAction property defines the subroutine that is run when the button is pressed by the user.

Private Sub Document_Open()

    Dim Toolbar As CmdBar
    Dim Button As CmdBarControl

    On Error Resume Next    'disable errors temporarily
    Set Button = Application.CmdBars(2).Controls("&amp;Data").Controls("&amp;Copy Data Provider...")
    If Err.Number = 0 Then  'button already exists
        Button.Delete       'delete it so that it can be re-defined
    Else
        Err.Clear           'clear the error so other testing can take place
    End If

    'try to add the new menu item in a desired location
    Set Button = Application.CmdBars(2).Controls("&amp;Data").Controls("&amp;New Data Provider...")
    If Err.Number = 0 Then      'if no errors, then the desired location exists
        'add a button in the desired location
        Set Button = Application.CmdBars(2).Controls("&amp;Data").Controls.Add(boControlButton, , Button.Index)
    Else                        'desired location doesn't exist
        'create a new toolbar and button
        Set Toolbar = Application.CmdBars.Add("Copy Data Provider", boBarTop)
        Toolbar.Visible = True
        Toolbar.Enabled = True
        Set Button = Toolbar.Controls.Add(boControlButton)
    End If

    'define the button
    Button.OnAction = Filename(ThisDocument) &amp; "!CopyDataProvider"
    Button.Caption = "&amp;Copy Data Provider..."
    Button.DescriptionText = "Copy Data Provider"
    Button.TooltipText = "Copy Data Provider"
    Application.Clipboard.SetData LoadPicture(ThisDocument.Path &amp; "\CopyDP.bmp")
    Button.PasteFace
    
End Sub

Sounds like this may be closer to what you were trying to accomplish. Good luck!

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · December 23, 2002, 6:07pm

I get an error with Filename(ThisDocument). Searching the SDK using the ObjectBrowser Search utility did not find it. Is is a function of your own design.

I was able to get the following line to work in an add-in:
MSRButton.OnAction = “EIS.rea!ThisDocument.OpenExcel” where OpenExcel is.

I wanted to make it run my OpenExcel function in the .rep macro so I tried:
MSRButton.OnAction = FileName(ThisDocument) & “!OpenExcel”

Can you please suggest how to code the OnAction to run functions within the current macro, or can I see your FileName function. THanks. Here is my complete code:

Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) _
  As Long


Public Sub OpenExcel()
 
    On Error Resume Next
    
    Dim Buffer As String * 100
    Dim BuffLen As Long
    Dim FileName, sTemp As String
    
    BuffLen = 100
    GetUserName Buffer, BuffLen
    UserName = Left(Buffer, BuffLen - 1)
    
    FileName = "c:\" &amp; UserName &amp; "_BO_Temp.txt"
    HtmlFile = "c:\" &amp; UserName &amp; "_" &amp; busobj.ActiveDocument.ActiveReport.Name &amp; ".htm"
    
    If Dir(FileName) <> "" Then
        Kill (FileName)
    End If
    
    busobj.ActiveDocument.ActiveReport.ExportAsText (FileName)
'    Call rep.ExportAsHtml(HtmlFile, 1, 1, 1, 1, 1, 1, 0, 0)
    
    CreateExcelObj = False
    'Attempt to Point an Occurrence of Excel to the Object Variable
    Set gobjExcel = GetObject(, "Excel.Application")
    If Err.Number Then   'Test to See if an Error Occurred
        'If an Error Occurs, Use CreateObject to Create an Instance of Excel
        Set gobjExcel = CreateObject("Excel.Application")
        If gobjExcel Is Nothing Then
            gbExcelRunning = False
            CreateExcelObj = True
            MsgBox "VBA: Could Not Create Excel Object. Email BUSINESS.OBJECTS.COMPASS@Cingular.com"
        Else
            gbExcelRunning = False
            CreateExcelObj = True
        End If
    Else
        gbExcelRunning = True
        CreateExcelObj = True
    End If
    
    gobjExcel.Workbooks.Open (FileName)
    
    gobjExcel.Range("A1").Select
    gobjExcel.Visible = True
    

'    Dim oWS As Object
'    Set oWS = gobjExcel.ActiveSheet
    
'Sheet1.Range("A1:Z200").PasteSpecial xlPasteValues
    
'    Dim k, l As Long
'    k = 0
'    Open FileName For Input As #1
'    While Not EOF(1)
'        Line Input #1, sTemp
'        k = k + 1
'        l = 1
'        While Not k = 0
'
'            oWS.Cells(10, 4).Value = "000100440"
'    Wend

'Close #1

'    gobjExcel.Range("A3:A4").Select
'    gobjExcel.Selection.AutoFill gobjExcel.Range("A3:A33")
 
   ' MsgBox ("Wow")
End Sub


Public Sub CreateEISMenu()
    Dim MSRControls As busobj.CmdBarControls
    Dim MSRPopup As busobj.CmdBarControl  'BO EIS
    Dim MSRButton As busobj.CmdBarControl 'Calendar EIS
    Dim i As Integer
    
 
    Set MSRControls = Application.CmdBars(2).Controls 'Main menu when docs are open
    Dim BooleanExist As Boolean
    BooleanExist = False
    
    'Cycle through all command bars controls on the Main menu.
    'Check to see if "BO E&amp;IS" menu already exists.
    For i = 1 To MSRControls.Count
      If MSRControls.Item(MSRControls.Count - i + 1).Caption = "Add Ins" Then
        BooleanExist = True
        Exit For
      End If
    Next

    'if menu does not exist, then add it
    If BooleanExist = False Then
      Set MSRPopup = MSRControls.Add(boControlPopup)
      MSRPopup.Caption = "Add Ins"
      Set MSRButton = MSRPopup.Controls.Add(boControlButton)
      MSRButton.Caption = "Open Report In Excel"
      MSRButton.DescriptionText = "Click to Open the current report in MSExcel"
      MSRButton.TooltipText = "Save the sheet using Excel File Save As - Type Excel Workbook"
    'changed in B120 - used to need .rea extension before the !
      MSRButton.OnAction = FileName(ThisDocument) &amp; "!OpenExcel"
    End If
End Sub

Public Sub Document_AfterRefresh()
    Me.CreateEISMenu
End Sub

zing (BOB member since 2002-11-01)

system · December 23, 2002, 7:28pm

As I understood it, Olivier wanted a button in the body of the report not on the toolbar. Anyways, to answer your question. You should be able to call your function by using the following…

oConButton.OnAction = ThisDocument.Name &amp; ".rep!FrmOpen"

I have used this with add-ins but see no reason why they should not work in reports. Please see here for a detailed example using an add-in…

avaksi (BOB member since 2002-08-22)