Bridging the Gap: Enhancing Excel-based Applications with SAP .NET Connector
This article will help you put together a concrete solution in order to satisfy your client’s specific needs.
What is SAP .NET Connector, or SAP NCo?
When creating Excel-based applications for SAP clients, there are cases in which developers wish for an easy way to interact with the SAP back-end from Excel. Over the past few years, SAP has greatly simplified front-end implementations by integrating OLAP source analysis from SAP BW into Excel through the Analysis for Office add-in. Apart from that, however, they have not provided a mechanism that will allow one to perform various other tasks in the underlying system directly from Excel. For example, it’s not possible to perform simple tasks such as triggering process chains let alone something more complex, such as executing custom ABAP logic remotely based on user input. From a technical consultant’s perspective, this limitation could be quite frustrating and certainly demands a more flexible solution. Luckily, SAP has provided a tool that will allow developers to bridge this gap on their own in a very versatile way.
To fill in the void, SAP has offered technical users a .NET API called the “SAP .NET Connector” (“SAP NCo” for short). This is an interface that allows .NET applications to use BAPIs and call remote-enabled function modules. It also supports the reverse scenario and provides a way for ABAP applications to interact with .NET components from outside the SAP realm. It’s important to note that the connector is not a prepackaged, standalone module that can be referenced and used by .NET applications out of the box. It’s an interface that is meant to be used in custom implementations that take advantage of the .NET Framework, the Component Object Model and the interoperability between them. Since there are practically countless use cases for the connector interface in all the diverse Excel-based applications out there, the purpose of this article will be to solely explain the concept behind the implementation and exploitation of an SAP NCo-enriched module. The reader will then have the freedom to put together a concrete solution based on this article’s content in order to satisfy their client’s specific needs.
Taking advantage of COM and the .NET Framework
Before getting into the details of the implementation, it’s important to understand the main technologies involved and how they interact in order to provide all the necessary tools to make the solution possible. The first core concept is called language interoperability. Since the early 90s, there has been a demand for cross-language operability in applications. Initially, the Component Object Model (COM) was the solution used to provide that. Its purpose is to expose objects to various components and modules that may be written in different languages. Nowadays, .NET provides similar functionality through its intermediate language translation and allows objects written in different languages to be used together in building applications. Besides that, the .NET Framework provides various other features as well, and as a result, .NET assemblies are now the preferred solution over COM Object implementations. However, applications that have been developed before .NET were designed to operate under COM and do not have native support for .NET assemblies. To make COM-dependent applications, like Excel, compatible with .NET, Microsoft designed the framework to provide COM interoperability to its assemblies on demand. This means that the .NET Framework is capable of decorating assemblies to make them COM-compliant and it’s up to the developer to decide whether to make an assembly COM-interoperable. The interoperability between COM and .NET allows developers to support and enhance COM applications without losing out on all the benefits provided by the .NET framework. It’s important to note that these technologies are the driving force behind the mechanism that allows SAP NCo functionality to be exposed to Excel applications.
The strategy is to write a COM-enabled, .NET assembly that will wrap the desired functionality of SAP NCo and can then be referenced as an external resource in Excel. Once this newly created .NET object is instantiated in Excel, it will allow the application to interface with the SAP BW backend. The following steps describe the implementation process in further detail.
1. Creating a C# Class Library
The first thing that needs to be done is to create a C# Class Library in Visual Studio. This file will contain the implementation of the .NET assembly.
2. Setting the instruction set architecture
Once the project is created, it’s important to configure the architecture the add-in is going to use. The selected architecture should match the architecture of both, Excel and the .NET Connector distribution.
3. Add the COM-enabled and COM-interop settings
The next important thing is to add the settings that will turn the assembly into a COM object and allow the .NET framework to operate with it.
4.Reference the SAP NCo libraries
Once the COM and interoperability settings are in place, the focus can be shifted onto the implementation of the assembly. The first step is to reference the SAP .NET Connector libraries inside the project.
Add the following statement at the top of the class file.
5. Exposing SAP NCo functionality inside the C# class library
In order to be able to make manipulations to a BW system through SAP NCo, one must first define the necessary parameters required to establish a connection to that system. This is done using the connector’s API. The screenshot below outlines all mandatory fields required in order to create an RFC destination for the desired BW instance.
Once the method to establish the connection has been defined, developers are free to use SAP NCo in any way suitable. To make this example meaningful, SAP NCo will be used to call a function module and pass its output back to Excel. The first step is to define a function module object and set its import parameters.
The next step is to execute the function module using the RFC destination defined earlier.
To end the method definition, the function module output is retrieved and prepared for export to Excel. Since Excel has problems dealing with objects returned by methods, the result object (an ArrayList in this case) is passed by reference and is manipulated during the method’s execution.
6. Building the C# class library assembly
When the developer has completed the class library implementation, the next step is to build an assembly in order to be able to reference the C# library module in Excel.
Note: Visual Studio should be opened as Administrator in order to be able to build an assembly.
7. Adding the Type Library file as a reference add-in to Excel
Once the build is complete, it’s time to shift the focus away from Visual Studio. To reference the newly created module in Excel, one needs to open the workbook where they want to take advantage of SAP NCo’s functionality, enter the developer’s backend, and add a reference to the Type Library file generated by the build.
8. Calling the Function Module from Excel
At this point, the Excel application is enriched with the functionality from the C# assembly. One can use their custom implementation by instantiating the class in the workbook’s VBA script.
As stated earlier, the first thing the connector requires is an RFC Destination. The username and password are supplied as user input directly inside the application.
Once the connection is established, it’s finally time to put this custom add-in to use and trigger the RFC from Excel to get the top products by net sales.
9. Processing the function module’s output
When the RFC is complete, the output is available in the ArrayList object that was passed in. Its content can then be processed and displayed in an Excel worksheet.
10. Displaying the function module’s output in Excel
Finally, one can see the outcome of this entire process! The VBA script is implemented in subroutines that are then assigned to buttons in the front-end. This exposes the connector’s functionality to the user and puts the application in a more complete state.
In conclusion, this article outlines an approach to expose SAP .NET Connector functionality to Excel. It may seem like a lengthy process, but it can enrich one’s Excel applications with great capabilities that are otherwise unavailable out of the box. The connector greatly enhances the interaction between Excel and SAP BW and bridges the functionality gap left behind by SAP Analysis. It empowers developers to implement Excel applications with expanded functionality and makes room for more creativity during the design phase. Due to the openness of the solution and the extreme diversity of project requirements, it makes no sense to bound this approach to a set of use cases. It’s important to remember that it’s entirely up to the developer to decide how to take advantage of the functionality available through SAP NCo in order to meet their clients’ needs.
DAHLBEER ensures your business objectives are achieved. Our vast experience and technical expertise in SAP planning tools enables efficient implementation in short cycles. Dynamic and Reliable – It’s how we are known to our clients.
Phone: +49 (0) 1515 629 66 08
Impressum & Datenschutz
Dahlbeer Unternehmensberatung GmbH
Landshuter Allee 8-10
Tel: +49 (0) 89 / 998 20 84 80
Fax: +49 (0) 89 / 998 20 84 89
Geschäftsführer: Christoph Dahl, Philipp Beer
Sitz der Gesellschaft: München
Registergericht: AG München HRB 201525
Umsatzsteueridentifikationsnummer (UID): DE285620916
Inhaltlich verantwortlich gemäß § 55 Abs. 2 RStV: Christoph Dahl, Philipp Beer