+ Reply to Thread
Results 1 to 8 of 8

Can Method in VBA Code from ActiveX control causes "macro may not be available" exception

  1. #1
    Registered User
    Join Date
    09-15-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Can Method in VBA Code from ActiveX control causes "macro may not be available" exception

    I have an Active X control that calls an Excel Template and attempts to run a method called "RunReport".

    I have tried:
    1) Checked to make sure that the path I am using is correct and that the file name is correct
    2) I have made sure that the file name, module and methods do not have any underscores
    3) Enabling all macros and "Trust access to VBA Project model" in the Trust section.
    4) I have attempted to prefix the method name with its module name "Module1.RunReport" and tried "MyExcelTemplate.xltm!Module1.RunReport"
    5) I have tried Removing and re-adding the Method (Saving and trying the file after each step).

    Any help you can provide would be great.

    Code Snippet:
    Dim officeApp As Object = Nothing
    Dim templateDocument As Object = Nothing

    officeApp = CreateObject("Excel.Application")
    officeApp.Visible = False

    templateDocument = officeApp.Workbooks.Add(Template:=ReportParameters.ProcessTemplate)

    extendedMethodName = "Module1.RunReport"

    officeApp.Run(extendedMethodName, ReportParameters)

    templateDocument.Close(SaveChanges:=False)

    officeApp.Quit()

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Can Method in VBA Code from ActiveX control causes "macro may not be available" except

    this is vb.net? what is reportparameters?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    09-15-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Can Method in VBA Code from ActiveX control causes "macro may not be available" except

    This is VB.NET. ReportParameters is a class of properties (strings and booleans mostly). I have verified that ReportParameters exists and has valid values as well.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Can Method in VBA Code from ActiveX control causes "macro may not be available" except

    Normally in VBA when using Run to pass parameters to the sub you are calling you pass them as arguments of the Run method.

    For example to call a sub named 'Test' which takes 2 arguments it would be something like this.
    Please Login or Register  to view this content.
    What arguments/parameters does RunReport expect?
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    09-15-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Can Method in VBA Code from ActiveX control causes "macro may not be available" except

    Agreed. RunReport expects one parameter as an object which is the ReportParameters class -- The ReportParameters class is just some strings and booleans.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Can Method in VBA Code from ActiveX control causes "macro may not be available" except

    Can you post the header for the RunReport sub?

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Can Method in VBA Code from ActiveX control causes "macro may not be available" except

    does the workbook have a reference to your .Net library/app where the ReportParameters class is defined?

  8. #8
    Registered User
    Join Date
    09-15-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Can Method in VBA Code from ActiveX control causes "macro may not be available" except

    Norie,

    Here is the signature for the RunReport Method:

    Public Sub RunReport(ByRef ReportParameters As Object)

    -JosephP,

    The Workbook does not have a reference to the .NET library that is calling it that is passing the ReportParameters. However this does not seem to be a problem. Some of the reports seem to work just fine - no errors.

    From what I am reading in my continued research, there is some suggestion that the excel files might be corrupt or there might be an issue with the references used? I am looking into that now to see if that might be what is causing it.

    Any other ideas?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Update Macro to search column Bfor "Fail" and "Exception"
    By programct in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2014, 02:32 PM
  2. Replies: 1
    Last Post: 06-09-2014, 04:11 AM
  3. Replies: 4
    Last Post: 09-23-2013, 02:43 PM
  4. ActiveX Control with Dialog Causes "Lock"
    By pagates in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2006, 04:20 PM
  5. [SOLVED] How to control "Date Time Picker ActiveX Control"
    By Jafery in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2005, 10:05 AM

Tags for this Thread

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