Skip to Content

EXCEL - SAP integration ... which way forward?

Hi folks,

A customer (having a SAP 4.6D system) would like to have an EXCEL sheet being populated with SAP data with some additional formatting (headers, subheading, totals, colouring cells based on certain values; etc ...) within the EXCEL sheet ...

Out of my head I could think of some possibities here:

- ALV view of table with download (but no extra formatting options here ... )

- ABAP OLE commands (not sure this is still being used)

- EXCEL integration via BDS ( I remember that you can have a both way integration here but how far does it go?)

- RFC communication to for example .NET RFC server via .NET connector ..

Anyone having any experience in this? What are the pro's and cons of any of the possibilies? Anyone any suggestion on what is the best way forward?

My customer is looking for a 'cheap' fast solution and is not willing to spend more then 2-3 days on it ...

Thx,

Steven

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Best Answer
    avatar image
    Former Member
    Jun 26, 2004 at 12:25 AM

    Hi Steven,

    One option you might want to consider is XXL. Some of the benefits are: easily manage/color headings, easily retain proper formatting when needed (example company code 0010 will not become 10, etc.).

    Sample programs XXLFTEST, XXLSTEST, XXLTTEST.

    Mike V.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Ioana,

      Thanks for the info! This class seems to be the most interesting one ...

      I had a quick look at the program and documentation and one thing was not directly clear for me: Do I actually need to have an (empty) excel document in the BDS to use this or can I start really from scratch ...

      Looked like you need to have a document proxy first which was linked to an existing BDS document ...

      thx,

      Steven

  • avatar image
    Former Member
    Jun 28, 2004 at 11:46 PM

    I would prefer using EXCEL with BDS option as this is the way SAP heading towards.. I have used it and it is faster and easy to control using MACROs.

    You can use OLE in fact of SAP uses OLE concept in their OO spread sheet interface class methods. But as it has been made easy using the class/interface and its methods better take the option of BDS with excel integration...

    RFC is not recommended as it is time consuming and will impact on performance a lot...

    Thanks

    Surgi

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 06, 2004 at 04:34 PM

    Hi folks,

    Thx to you all for the responses ... it gave me a good idea on the direction to choose ...

    Steven

    Add comment
    10|10000 characters needed characters exceeded

    • Thx again Ioana !

      Looks that I will need a predefined empty sheet after all as there are some printing properties (like header/footer/ etc... ) will need to be used on the sheet which I am generating so ...

      Steven

  • Jul 18, 2004 at 06:55 PM

    Hello Steven

    I prefere ALV Grid in combination with ALV templates stored in SAP (avaliable from 46c).

    I don't know all the methods from the other replys, but my experience is that OLE and use of excel macros with VBA script is a bad idea. Some years ago i made an interface like that based on (as far as i remember) excel 95 and R/3 31i. After upgrade to R/3 46c I had to change the ABAP program to make it work again. Two month later we changed to office 2000 and after that the macros didn't work anymore - I never fixed it - I changed to ALV grid.

    With the combination of ALV grid and excel templates you solve the problems with the tools that is best. You use ABAP to extract the data and you use excel for the formatting.

    I a privieus posting I listede som hints about this technique. I don't know how to link to an other posting, so I just cut/paste:

    1) An ABAP program extracts data from the database and presents the data with Excel Inplace.

    2) Instead of the SAP standard templates SAP_OM.XLS and SAP_MM.XLS the program uses a customised excel template created by the "business people" themselves. (Now they can't blame you for the layout).

    Where to find more info:

    Oss note 358644 and 548409

    You will have to build your own templates by creating modified copies of the SAP standard templates SAP_OM.XLS and SAP_MM.XLS. If you can find these see note 316728 to copy them from client 000.

    You will have to work with transaction OAOR to administrate the templates and program BC_BDS_UPLOAD to upload new templates.

    You will also find information in the ALV grid documentation on help.sap.com.

    Best regards

    Thomas Madsen Nielsen

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Serdar Simsekler

      Hi,

      Even i have been using OLE for Excel. But i am not sure but when the data is very huge , it takes time , so if time is not issue then nothing like OLE because you can do all the coding what you can do in Excel Macro and whatever format , font etc.

      But this thread was uqite informative for the people who are new in this and want to do some kind of integration with Excel

      Rajiv

  • avatar image
    Former Member
    Sep 29, 2005 at 12:47 PM

    Hi,

    here we have a simple program..

    RSDEMO01..

    instead of opening a plain excel,

    can I open an excel template which contains

    colors, lines, borders, and some graphics like fish

    bone diagram, etc...

    Regards,

    Sudhahar R.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 28, 2008 at 11:59 AM

    Hi,

    I am having similar requirement but the integration is required with Microsoft Word.

    The requirement will be like this. There will be set of letter templates in the Word document for quotation.

    I need to create dynamic documents with reference to thse templates kinda mail merge and present it to user.

    User might change the data in the document and the changed data shd be saved back.

    I am thinking of putting these document templates in BDS and retrieve/populate them using mail merge interface.

    I guess there are some methods as well to save the document data back to BDS.

    But i am not getting how shd I start with BDS. What kind of config is needed to start with BDS ? How do we store the template documents on BDS server ? I am looking for kind of functionality as that of SAPRDEMO_MAILMERGE_INTERFACE

    Technical help from BDS perspective will be helpful.

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • May 25, 2011 at 08:00 AM

    In the end I used a template stored in BDS which is fetched and then manipulated.

    That allowed me to have logo's, headings, etc already in place ...

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 03, 2014 at 03:10 PM

    Hi Steven,

    I have a similar requirement and i would like to know how you were able to resolve this.

    I have to create a BDS template which would need to be uploaded to OAAR t-code. Using ABAP, i have to open this BDS template (it has 2 sheets one the raw data sheet and the other one is the pivot table), populate this with raw data in the RawData sheet. and then refresh the pivot table in the template.

    Is there any way this can be achieved? Could you kindly suggest ideas?

    Regards

    Balaji

    Add comment
    10|10000 characters needed characters exceeded