I want Help for passing parameter to function from shortcut menu (1 Viewer)

[Abo Abd_Allah]

Hi All
I use this code to display a shortcut menu of reports when I click the right mouse button
But I want to pass the name of the current report to this routine so that I can export the report to Excel

    Dim cmbRightClick As Office.CommandBar
    Dim cmbControl As Office.CommandBarControl
On Error Resume Next
       ' Create the shortcut menu.
    Set cmbRightClick = Application.CommandBars.Add("MyRepRightClkMenu", msoBarPopup, False, True)

    With cmbRightClick
           set cmbControl=.controls.add
           With cmbControl
                  .Caption = "Export to Excel"
                  .FaceId = 11723
                 .OnAction = "=ExportExcelSb(me.name)"
          End with
   End with
'this sub for Export
'============
Public Sub ExportExcelSb(ByVal repname As String)
Dim savPas As String
savPas = calFilDilog(2, "Expor To Excel")
DoCmd.OutputTo acOutputReport, repname, "Excel97-Excel2003Workbook(*.xls)", savPas, True, "", , acExportQualityPrint
End Sub

 

[arnelgp]

can you use Screen.ActiveReport.Name?

    Dim cmbRightClick As Office.CommandBar
    Dim cmbControl As Office.CommandBarControl
On Error Resume Next
       ' Create the shortcut menu.
    Set cmbRightClick = Application.CommandBars.Add("MyRepRightClkMenu", msoBarPopup, False, True)

    With cmbRightClick
           Set cmbControl = .Controls.Add
           With cmbControl
                  .Caption = "Export to Excel"
                  .FaceId = 11723
                 .OnAction = "=ExportExcelSb()"
          End With
   End With
'this sub for Export
'============
Public Sub ExportExcelSb()
Dim savPas As String
savPas = calFilDilog(2, "Expor To Excel")
DoCmd.OutputTo acOutputReport, Screen.ActiveReport.Name, "Excel97-Excel2003Workbook(*.xls)", savPas, True, "", , acExportQualityPrint
End Sub

 

[Abo Abd_Allah]

thanks alot
i think This code is also valid

Application.CurrentObjectName

I have another problem
i receive this error message when export to excel

 

[arnelgp]

remove the ".xls" from repname.
repname should be the "name" of the report you have.

use a Function instead of Sub:

Public Function ExportExcelSb()
Dim savPas As String
Dim repname As String
savPas = calFilDilog(2, "Expor To Excel")
repname = Screen.ActiveReport.Name
savPas = savPas & repname & ".xls"
DoCmd.OutputTo acOutputReport, repname, acFormatXLS, savPas, True, "", , acExportQualityPrint
End Function

 

[Abo Abd_Allah]

remove the ".xls" from repname.

i don't put this extension to report name, but to path save path, if you print the code to immediate window will see the different.
thanks alot for your reply

 

[arnelgp]

what is the error msg? you did not Declare "repname".

 

[Abo Abd_Allah]

what is the error msg? you did not Declare "repname".

thanks for your reply again, the problem is fixed. with god goodnees, i type code as this

DoCmd.OutputTo acOutputReport, repname, acFormatXLS, savPas, True

and all things work good now!
thanks again.