Skip to Content

Dear abapers and warriors of the humankind,

I’m proposing you a solution to print Product Orders through Excel instead of smartforms or others stuff. This is valid from Excel 2007 and up.

It depends on your needs but I think sapscripts and smartforms have a very low adaptable level to the company.

Standard sapscript is PSFC_STD_LAYOUT. Go and see it if you want 🙂

I would like to get a feedback too, to prove if this solution could exist or it has to be thrown from the window

This is a brief guide throughout the “final solution”.

Let’s start from the beginning.

If you’re looking for smartforms: no way 🙂   try to design a print on a smartform with boxes , lines, different width, thickness,  colors, etc..

Hell would be better I suppose  🙂 Try and let me know.

Instead you can try to do the following.

– Transaction OPK8 (mantain print control production orders)

From there you define your Lists, spool parameters for each user, print programs for each list, etc.

Start from an order type and a list. Example : LK03 Component-related list 03

Assign a ZPRINTORDR program and go on.

– Print Report ZPRINTORDR

Get order data using data from  CO_PRINT_IMPORT_DATA ( read logic inside this function module).

Create 2 or more tables with data you need from Header, Items and other Stuff like Long Text;

This tables will be passed to the excel sheet to feed header data, positions data and others.


– Create an Excel template file

Design an Excel template file with all the data you need. Think about worksheets you need for Data Retrieval. For example:

ScreenHunter_73 Jul. 14 17.12.jpg

As you can see you’ll have HeaderData , ItemData. In these two worksheets you’ll have the same columns of your internal table used in ZPRINTORDR.

There you’ll pass data to feed Excel Testata Sheet (the final sheet that will be printed)

Define a Print Area in excel for all sheets you’ll want to be printed.

This template will be upload in SAP through Business Document Service (BDS) .

Pay attention that this template will have inside VB code. I’ll put some Instructions after…

– Transaction OAOR

Business Document Navigator.  There you can upload a template you can reference in all your programs.

In our case we’ll use Class name : SOFFICEINTEGRATION and Class type OT.

We don’t need to create a new one. Search on internet for How to upload in OAOR a template excel.

Detail of this template excel will be used in program ZPRINTORDR. You’ll need:

     sbdst_classname VALUE ‘SOFFICEINTEGRATION’;

     sbdst_classtype VALUE ‘OT’;

     sbdst_object_key VALUE ‘SOFFICEINTEGRATION’,

     description_char255 VALUE ‘‘YOUR_TEMPLATE_NAME’


Pay attention when you download, upload template into Business Document Repository cause you’ll have to mantain versions on local computer or directly on Business Document Service.


– Custom Function for Excel data manage;

     My suggest is to create a custom function to be launched in a New Task, with no interference with standard process of saving/registering print order from CO01/CO02/CO03.

     This function module will receive:

          CLSNAME Business Document Service: class name;

          CLSTYPE  Business Document Service: class type;

          OBJKEY    Business Document Service: object key;

          DESCR      BDS description;

          PRINTER    Printer output name;

          VISIBLE     If you want to see or not see excel working in background;

     There will be 2 tables:

          TAB_HEADER_DATA;

          TAB_ITEM_DATA.

    

     Code section.

     Three phases of: open_document, feed_table_data, print_document.

     Define some structure you’ll need:

     ScreenHunter_74 Jul. 14 17.36.jpg

    

     Phase Open Document

     To manage data in Excel you’ll have to create a container control. You can manage messages too, in case you’ll want to inform user of every error.


* Create container control

        CALL METHOD c_oi_container_control_creator=>get_container_control

          IMPORTING

            control = gr_control

            retcode = gwf_retcode.

        IF gwf_retcode NE c_oi_errors=>ret_ok.

          PERFORM store_messages USING ‘E’ syuname ‘ ‘ ‘ ‘ ‘ ‘ ‘011’.

          EXIT.

        ENDIF.

* Initialize Custom Control

   CREATE OBJECT gr_container

     EXPORTING

       container_name = ‘CONTROL’. “Custom Control Name

   IF sysubrc NE 0.

     PERFORM store_messages USING ‘E’ syuname ‘ ‘ ‘ ‘ ‘ ‘ ‘011’.

     EXIT.

   ENDIF.

* Set container visible or not visible (look function module parameter )

CALL METHOD gr_container->SET_VISIBLE

     EXPORTING

       visible = gr_visible_container.   “X for visible

    

* Init control

CALL METHOD gr_control->init_control

     EXPORTING

       r3_application_name      = ‘ANYNAME_EXCEL’

       inplace_enabled          = GR_INPLACE “External or Internal SAP ” abap_false IF visible

       inplace_scroll_documents = GR_INPLACE “External or Internal SAP ” abap_false IF visible

       parent                        = gr_container

       NO_FLUSH                 = C_X “constant

       REGISTER_ON_CLOSE_EVENT  = C_X

       REGISTER_ON_CUSTOM_EVENT = C_X

     IMPORTING

       retcode                  = gwf_retcode.

   IF gwf_retcode NE c_oi_errors=>ret_ok.

     PERFORM store_messages USING ‘E’ syuname ‘ ‘ ‘ ‘ ‘ ‘ ‘011’.

     EXIT.

   ENDIF.



* Retrieve template from Business Document Service

* Create object for cl_bds_document_set

   CREATE OBJECT gr_document.

   IF sysubrc NE 0.

     PERFORM store_messages USING ‘E’ syuname ‘ ‘ ‘ ‘ ‘ ‘ ‘011’.

     EXIT.

   ENDIF.

* Get Document with URL

   locwa_signatureprop_name  = ‘DESCRIPTION’.


* Description of the table template in OAOR

   locwa_signatureprop_value = p_descr. “Function Input parameter

   APPEND locwa_signature TO locint_signature.


   CALL METHOD gr_document->get_with_url

     EXPORTING

       classname       = P_CLSNAM “Function input parameter

       classtype       = P_CLSTYP    “Function input parameter

       object_key      = P_OBJKEY   “Function input parameter

     CHANGING

       uris            = locint_uris

       signature       = locint_signature

     EXCEPTIONS

       nothing_found   = 1

       error_kpro      = 2

       internal_error  = 3

       parameter_error = 4

       not_authorized  = 5

       not_allowed     = 6.

   IF sysubrc NE 0.

     PERFORM store_messages USING ‘E’ P_CLSNAM P_CLSTYP P_OBJKEY ‘ ‘ ‘012’.

     EXIT.

   ENDIF.



* Get Proxy to manage Excel application

     READ TABLE locint_uris INTO locwa_uris INDEX 1.

        CALL METHOD gr_control->get_document_proxy

          EXPORTING

            document_type      = ‘Excel.Sheet’

            NO_FLUSH           = C_X

          IMPORTING

            document_proxy     = gr_proxy

            retcode            = gwf_retcode.

        IF gwf_retcode NE c_oi_errors=>ret_ok.

          PERFORM store_messages USING ‘E’ syuname ‘ ‘ ‘ ‘ ‘ ‘ ‘011’.

          EXIT.

        ENDIF.

** Open Document


     CALL METHOD gr_proxy->open_document

          EXPORTING

            document_title   = ‘ANYNAME_EXCEL’

            document_url     = locwa_urisuri

            open_inplace     = GR_INPLACE “Open document inside or outside SAP

            protect_document = abap_false “Protect Document initially

            NO_FLUSH         = C_X

*          STARTUP_MACRO    = gr_macro “<- if you need any particular macro to be launched on startup

          IMPORTING

            retcode          = gwf_retcode.

        IF gwf_retcode NE c_oi_errors=>ret_ok.

          PERFORM store_messages USING ‘E’ syuname ‘ ‘ ‘ ‘ ‘ ‘ ‘011’.

          EXIT.

        ENDIF.

**

* Get Excel Interface THIS is important to get Excel sheet_interface

        CALL METHOD gr_proxy->get_spreadsheet_interface

          IMPORTING

            sheet_interface = gr_excel

            retcode         = gwf_retcode.

        IF gwf_retcode NE c_oi_errors=>ret_ok.

          PERFORM store_messages USING ‘E’ syuname ‘ ‘ ‘ ‘ ‘ ‘ ‘011’.

          EXIT.

        ENDIF.

    

    


Phase Feed_table_data


     CALL METHOD gr_excel->SELECT_SHEET

          EXPORTING

            NAME = ‘HeaderData’.

        IF sysubrc NE 0.

          f_error = C_X.

        ELSE.

” Go on -> “It’s important to register exceptions

       ENDIF.

REFRESH locint_fields.

* Get field attributes of the table to be displayed

      CALL FUNCTION ‘DP_GET_FIELDS_FROM_TABLE’

            TABLES

              data             = gt_list_xlshead “<= Function input table for header data

              fields           = locint_fields       “<= here this function module will put info on fields (columns to fill excel sheet)

            EXCEPTIONS

              dp_invalid_table = 1

              OTHERS           = 2.

     IF sysubrc <> 0.

       f_error = C_X.

     ENDIF.


* Define range on excel sheet -> create a form for this, will be useful

* Select area for entries to be displayed


l_top, l_left represent where to start:

example

l_top = 2; l_left = 1 means start from 2nd row 1st column of excel

That’s important to define area where data will be put on excel

l_row, l_column represent the extreme range for data.

l_row can be obtained with a DESCRIBE TABLE LINES of gt_list_xlshead

l_col can be obtained with a DESCRIBE TABLE LINES  of locint_fields

l_range must be an ID for methods of class gr_excel to identify this area

Put for example HEAD for header and COMPN for items

   CALL METHOD gr_excel->set_selection

     EXPORTING

       top     = l_top

       left    = l_left

       rows    = l_row

       columns = l_column.

* Define Range

   CALL METHOD gr_excel->insert_range

     EXPORTING

       name    = l_range

       rows    = l_row

       columns = l_column

     IMPORTING

       error   = gr_error.

   IF gr_error->has_failed = abap_true.

     CALL METHOD gr_error->raise_message

       EXPORTING

         type = ‘E’.

   ENDIF.

* finally put data on sheet HeaderData

* Insert the table entries into Excel

     CALL METHOD gr_excel->insert_one_table

         EXPORTING

           fields_table = locint_fields[]  “Defn of fields

           data_table   = gt_list_xlshead[]  “Data

           rangename    = ‘HEAD’          “Range Name

         IMPORTING

           error        = gr_error

           retcode      = gwf_retcode.

       IF sysubrc NE 0.

         f_error = C_X.

       ENDIF.


**** => DO THE SAME FOR COMPN and Other sheet you’ll want to feed



* At this point you’ll have to manage a MACRO on excel. VB code is better than ABAP for this solution.

* Macro will do all the stuff silently on our sheet.



In this case I’ll call a MAIN macro on my template


CALL METHOD gr_proxy->EXECUTE_MACRO

       EXPORTING

         MACRO_STRING = ‘MainModule.Main’  “<= pay attention Full Name required <NameModule>.<NameMacro>

       IMPORTING

         ERROR        = gr_error

         RETCODE      = gwf_retcode.

*    CHANGING

*      ERROR_STRING =

*      RETVALUE     =.

   ELSE.

     PERFORM store_messages USING ‘E’ syuname ‘ ‘ ‘ ‘ ‘ ‘ ‘009’.

   ENDIF.



**** AT THIS POINT SAP pass control to EXCEL


ScreenHunter_75 Jul. 14 18.03.jpg

You must define RANGE NAMES in excel to let macro know where cell start and ends. For example to count records in a sheet or to find columns, data, etc..

Define then START_HEAD range for the first CELL VALUE in Header Data

ScreenHunter_76 Jul. 14 18.08.jpg

In macro you’ll can manage data loop to your needs. In ourcase we had to put some images (icons of dangerous, etc) and copya rows for items in Testata sheet.

ScreenHunter_77 Jul. 14 18.11.jpg

Do the same thing for components and just call a routine to copy data from a template_row to the others

ScreenHunter_79 Jul. 14 18.16.jpg

This will copy your template row already defined with Formulas in Testata sheet.

Data on your template_row will be already mantain for the FIRST row of ItemData sheet.

With a copy routine you’ll go put all others records.

Formula and other stuff will be copied

ScreenHunter_80 Jul. 14 18.18.jpg

Formula are useful for your main need (hide empty data, put in a correct output format, reference data between sheets, etc)

I’ve a lot of stuff to show, like routine to put breakpoints dinamically, or to adapting dinamically cell dimension for Long Text passed from SAP, dinamically set printer with all problems related,

but this document would be very long.

It took quite 1 month to define every detail and would be difficult to read all this stuff.

So furthers details can be defined later.

After defined Macro in Excel Template the final step is Print your document.

Final steps are from SAP if you want to hide sheets or other do following:

     CALL METHOD gr_excel->get_sheets

*    EXPORTING

*      NO_FLUSH = ‘ ‘

*      UPDATING = -1

            IMPORTING

              SHEETS   = loc_sheets

              ERROR    = gr_error

              RETCODE  gwf_retcode.

LOOP AT loc_sheets INTO loc_wa_sheets. <= you’ll have sheet names


With names you can pass a string with a list to a macro in excel and let print sheet from excel.


The print logic in VB is quite complicated. If you’ll need I’ll pass you.

Final closing instructions:

     CALL METHOD gr_proxy->close_document

*  EXPORTING

*    DO_SAVE     = ‘ ‘

*    NO_FLUSH    = ‘ ‘

        IMPORTING

*    ERROR       = ERROR

*    HAS_CHANGED = HAS_CHANGED

          retcode     = gwf_retcode.

   IF gwf_retcode NE c_oi_errors=>ret_ok.

     PERFORM store_messages USING ‘E’ syuname ‘ ‘ ‘ ‘ ‘ ‘ ‘013’.

   ELSE.

     CLEAR gwf_retcode.

     CALL METHOD gr_proxy->release_document

*  EXPORTING

*    NO_FLUSH = ‘ ‘

     IMPORTING

*    ERROR    = ERROR

       retcode  = gwf_retcode.

     IF gwf_retcode NE c_oi_errors=>ret_ok.

       PERFORM store_messages USING ‘E’ syuname ‘ ‘ ‘ ‘ ‘ ‘ ‘013’.

     ENDIF.

   ENDIF.

   FREE gr_excel.

   FREE gr_proxy.

   FREE gr_control.

   FREE gr_container.

   IF gr_error_set EQ C_X.

     PERFORM show_messages.

   ENDIF.

That’s all folks. This document want to be only a Starting point for the development of ideas.

It’s written quite bad but it’s free 😉   🙂

I hope you enjoy on how to comunicate between SAP and Excel / Office without spend a lot of money and time.

Free your code and you’ll free yourself oneday ,perhaps 🙂

Bye

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply