Skip to Content
author's profile photo Former Member
Former Member

Auto Macro For Excel Inplace

Hello,

After many days of development I was able to create a template for a standard SAP report using the Excel Inplace function. However, we have already developed a macro to perform some complex calculations for this report, and I was able to attach this Macro to the Excel template. At this point the end user executes the variant and then appears the excel template with the correct data. Now the user (in excel inplace) selects view and runs the Macro to generate the final report. I would like to cut this step out if possible where the Macro is executed automatically after the variant is executed. I have added the Private Sub Auto_Open() to the template and it works fine when opening outside of SAP. However the Macro will not run automatically when the variant is executed and the data is extracted (only manually). I basically want to cut one step for the end user so they do not have to manually execute the Macro Is this possible?

Thank You Kindly,

sifter

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 15, 2011 at 10:47 AM

    Hi ScriptMan,

    First off thank you for your interest and support. The macro is in Visual Basic, a language I am not proficient in, but was able to assist in the creation of the macro. I have added the Auto_open () function to the module of the template and if I attempt to open the template outside of the SAP GUI the Macro runs automatically as it should (without data). However with the Excel inplace it seems to ignore this and the Macro will only run if the user guis in and selects it manually. I would like it to run automatically if possible because there will be a couple of variations of these macros and would like to develop a layout for each so the user does not have to manually execute each macro. I have tried adjusting the Excel security settings but this does not seem to be the problem. I am not familiar either with the inplace or what is happening in the background when an Excel inplace runs.

    Thanks,

    sifter

    Edited by: sifter on Aug 15, 2011 12:48 PM

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi sifter,

      I'm sorry, but I could not reply sooner. Meanwhile, I have dealt with the theory to Excel inplace. I found out that the macro Auto_Open can not be executed automatically. The solution to the problem is in the application of Workbook_Open event.

      There are at least 2 ways to gain access to the private module of the Workbook Object (ThisWorkbook)

      1) While in Excel proper, right click on the Excel icon, top left next to File and choose View Code.

      2) While in the VBA double click the Module called ThisWorkbook, seen in the Project Explorer (View>Project Explorer (Ctrl+R))

      Once here, you can select "Workbook" from the Object drop down list, located in the top left of the module pane. After you have selected "Workbook" Excel will default to;

      Please add the following command :

      Private Sub Workbook_Open()
      Application.OnTime Now() + TimeValue("00:00:05"), "Auto_Open"
      End Sub
      

      Workbook_Open exists in the same template where Auto_Open exists. The delay by 5 seconds befor start a Auto_Open is just a simple example.One could also develop within Auto_Open different queues.

      For Example:

      Sub Auto_Open()
      do while Range("A1").Value = ""
      DoEvents
      Loop
      . . .
      'Here follows the rest of the macro.
      End Sub
      

      But remember that when manually opening the template, the macro is run twice Auto_Open. You could rename for example the macro in No_Auto_Open. I hope this helps you further.

      Regards,

      ScriptMan

  • Posted on Aug 15, 2011 at 07:40 AM

    Hi sifter,

    You can connect a standard SAP report with an Excel macro. Unfortunately I do not work with Excel Inplace.

    The only problem is that you have to run the macro Auto_Open () in the same Excel session as the SAP standard report. What programming language used Excel Inplace?

    Regards,

    ScriptMan

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 14, 2011 at 03:12 AM

    YOU THE MAN ScriptMan !!!

    I simply added the VB code in the 'workbook' template as you suggested whilst keeping the 'Private Sub Auto_Open()' module as is and the code executed. It did run an infinite loop, but I think this is a result of the original code and I will have to check this on the version of Excel the macro was originally made on, I am currently trying this on a newer version of Excel. Thats interesting though adding the delay perhaps, leaving time for the data to populate or Excel\SAP interface script to run. This is a tremendous help,

    My sincerest thanks, gratitude, and appreciation,

    sifter

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.