How to read an APO table from excel in place using Visual Basic ?

[Former Member]

We have enhanced some APO reports using layouts that use excel in place with custom visual basic (VB) code. For example, the VB code can append the data with additional columns that perform specific calculations and then proceed to create pivot table reports in excel..That is working fine.

The challenge is to see if that VB code could read specific APO tables (e.g. product master tables) to bring additional elements into excel and thus expand the report content. We know that additional fields can be added to the reports, but having the flexibility to read tables from VB could be more flexible.

Can that be done?. If so, would it be possible using exclusively the VB code attached to the layout, i.e. no ABAP?

[Former Member · Accepted solution]

Hello Anselmo,

I am assuming you are doing this either using BeX analyzer or by reading the planning book using the RFC ActiveX component and a BAPI.

I think it's possible to retrieve the data from the product master table. Here are my thoughts on how can this be done:

1) You can create a generic datasource (transaction RSO2) for the product master table, connect that to a BW remotecube and use a BeX query to retrieve that values. You can then join the queries using VBA. Alternatively, you can build a muti-provider that connects your current InfoCube to the new RemoteCube and build a query from there. The multi-provider, in this case, joins the data (no more VBA).

2) If you have already loaded your product master data into master data carrying InfoObjects then you can try the same technique as #1 but this time there is no need to create the generic datasource. You can use the InfoObjects for your master data as the InfoProviders for your query.

3) You can try the ActiveX component from SAP which can connect to any emote enabled function in SAP system. SAP already provided some BAPIs (check transaction BAPI for a list) which might be able to provide the data. You can also create your own function modules (be sure to make it remote enabled) using ABAP. (transaction SE37).

Hope this helps

[Former Member · Accepted solution]

Emmanuel,

Thanks for the quick answer. It might indeed get us on the right track.

However the solution we are looking for does not rely on the BeX analyzer, which we use for other tasks. In this particular case, we use the Operation Lists from the Order & Resource reports, which are standard APO reports available in the PP/DS module. These reports use layouts and one of the options provided in the layouts is to use an excel template where an "exit" is available to include some custom VB code to manipulate the data extracted by the report, and thus help to automate certain task. The question is then to check if that VB has the capability to read APO tables (like the location-product master one) and populate additional columns in the excel output, which might then enhance the report capabilities.

thanks,

Anselmo

[Former Member · Accepted solution]

Hello Anselmo,

Yes, you can use the ActiveX component from SAP which can connect to a remote enabled function module. The name of the ActiveX component is "SAP Remote Function Call" (wdtfuncs.ocx in the SAPGUI program files directory). You can add that activeX control in the list of components in the toolbox and then use it I tried the component about 6 months ago and found it working.

So the steps left for you is:

1) Create a remote enabled function module in ABAP (SE37) which reads the master data tables

2) Call that function module in VB.

Check out this blog:

/people/sergio.locatelli2/blog/2006/12/14/read-any-sap-tables-from-excel

Hope this helps.

[Former Member · Accepted solution]

Emmanuel,

This is excellent!... You have definitely guided us on the right track!.

regards,

Anselmo