Auto Macro For Excel Inplace

[Former Member]

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

[Former Member · Accepted solution]

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

[script_man · Accepted solution]

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

[Former Member]

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

[script_man]

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