Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

EXCEL - SAP integration ... which way forward?

Steven_UM
Contributor
0 Kudos

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

1 ACCEPTED SOLUTION

Former Member
0 Kudos

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.

17 REPLIES 17

Former Member
0 Kudos

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.

0 Kudos

Hi Steven,

You can also have a look at class i_oi_spreadsheet (in the SAP Library you can find the documentation under Desktop Office Integration). Under development class SOFFICEINTEGRATION you can find some example programs as well - like SAPRDEMO_SPREADSHEET_INTERFACE for instance.

Good luck,

Ioana

0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

Hello everybody,

Could anyone please explain what BDS is and where you can find it in SAP (transaction codes/programs/other)?

Thanks in advance,

Ioana

0 Kudos

Terminology:

BDS -> Business Document Services

BDN -> Business Document Navigator

Start exploring from transaction OAOR...

Steven_UM
Contributor
0 Kudos

Hi folks,

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

Steven

Former Member
0 Kudos

Hi Steven,

You can start from scratch.

Here's an example:

document_type = excel.

CALL METHOD CONTROL->GET_DOCUMENT_PROXY

EXPORTING DOCUMENT_TYPE = DOCUMENT_TYPE

  • REGISTER_CONTAINER = 'X'

IMPORTING DOCUMENT_PROXY = DOCUMENT

ERROR = ERROR.

and here it comes:

CALL METHOD DOCUMENT->CREATE_DOCUMENT

EXPORTING

  • CREATE_VIEW_DATA = ' '

  • DOCUMENT_TITLE = ' '

  • NO_FLUSH = ' '

OPEN_INPLACE = inplace

OPEN_READONLY = 'X'

  • STARTUP_MACRO = ''

  • ONSAVE_MACRO =

IMPORTING

ERROR = error

RETCODE = retcode

.

so instead of calling that excel template in the BDS you just make a new one...

Regards,

Ioana

0 Kudos

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

TMNielsen
Contributor
0 Kudos

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

0 Kudos

Hi Thomas,

Thanks for this interesting post !

I played around a little bit but to my surprise I could not find any macro's in the excell document (perhaps has something to do with it being Excell 2003 ) ...

I don't see how adding a custom tag in a custom excell document would be doing something usefull when the macro's lack ... Also the formatted list cells just seem to have plain values and are not referencing any other sheets so ...

I would also require specific font settings etc which probably are hard to get with the ALV Excell in place view ...

It would be most interesting to share some of your experiences ... Obviously this is a fast and easy way forward but I am doubting whether I can tweak the excell enough to get the result I need ...

Oh yeah, what would be the differnce between the 2 standard excell templates (they look the same to me ... )

Thanks,

Steven

0 Kudos

Hi to all

I use OLE automation and I am happy with that. It is sometimes difficult to find methods but generally OK.

However, more systematical approaches would suit better if the context requires so.

*--Serdar

Former Member
0 Kudos

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

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

Steven_UM
Contributor
0 Kudos

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 ...

Former Member
0 Kudos

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