Topics Included
1. Preface
2. VBs Hands-on Tutorial
3. Access SAP-BAPI from Excel-VBscript.
1. Preface:
Sometime it is require to work with single transaction or with a single functionality in SAP for a long time for novice at very early stage of work. Many companies deny giving direct SAP access to those in most cases therefor IT Managers or Administrators are enforced to think about some indirect way to work with SAP system. They may prefer to work on other technologies for data prepare and make communication with SAP using BAPIs for data passing. Here let's take one example I need to create sales inquiry from remote location where I would not like to give direct SAP access to end users rather they have to fill inquiry detail in predefined MS Excel format and directly upload it to SAP server via Excel-VBs code. At this time ABAPer have only knowledge of SAP and relevant technologies (in some cases). As per my experience learning VBs code is as easy as learning ABC. In this document I will show you how to develop a skill to write VBscript from scratch and make a connection with SAP with inquiry creation demo.
2. VBs Hands-on Tutorial:
The main aim of learning VBscript in this document is to learn it without any professional help/document, we are going to learn this language without any help it seems puzzling but that is true. Now ask me how, the answer is very simple first we record macro then we check the code and modify it accordingly. We can cover limited topics in this document but after having some knowledge how it works then after you can also do it for any kind of scripting operations as we do in regular working in MS Excel file so let's do it practically.
We will learn in this sequence:
- Cell Selection & Range Selection
- Writing Operation
- Cell Formatting
- Clear Cell Content
- Working With Form Control - Button
- Declare a variable
2.1 Practical
I am working on MS Office 2010 Standard Edition. I assume that you well aware with MS Excel various features and know how to record macro.
Begin with open MS Excel here you check Developer tab is available as shown below screen.
(Img.1)
If you are not able to see Developer tab then you need to customize ribbon from File->Options->customized ribbon and select and ok from the given list as shown below.
( Img.2)
Once you successfully get Developer tab then select it and click on Macros button to check existing recorded macro. You will be navigated to shown below screen. If any previously macros are exist then it will automatically shows in macro list from there you can directly run macro if you required to check its functionality by pressing Run button. You can edit macro by pressing edit button and delete it by pressing delete button.
(Img.3)
Here in above screen you may notice that not any macro recorded found then press cancel button to exit from that dialog box. Now you need to record a new macro then you can see Record Macro button from same Developer tab that you can record a new macro as shown below screen.
(Img.4)
Ok, here you can change relevant macro name and press ok button to start the recording, on thing you should notice is that the button of Record Macro is converted to something like this
( Img.5)
Now select cell A1 and type abc into it something like this
(Img.6)
When you feel that now your recording is finished then press stop recording button and your macro is successfully recorded. Now again press Macros button to view recorded macro here you will find 1 entry of your recorded macro Press Edit button to view VB Script recording code as shown in below screen.
(Img.7)
As shown all above process is how to record a macro and view its code. Now it's easy to recognize the written code even if you don't know the VB Script code.
2.1.1 Cell Selection & Range Selection
Any type of action on particular cell should be perform only after a cell sections therefore your recording goes as below and analyze its code.
(Img.8)
And its codes something like this
(Img.9)
Same as for range selection whenever you need to select multiple cells then your recording should be like as below screen.
(Img.10)
And its codes something like this.
(Img.11)
2.1.2 Writing Operation
I am recording my name inside cell A1 and my surname inside cell A2 then recording and its code something like this.
(Img.12)
And its code something like this.
(Img.13)
You can analyze that I have put additional code to select cell A1 because whenever I open an excel file I am clicking at any cell randomly very often by intention or not if I could not put VBs code to select specific cell then randomly selected cell is set as default selection and will have my first name "Avirat".
2.1.3 Cell Formatting
Here I am doing some formatting activity like cell foreground color, background color, font bold, font italic etc... Just analyze how it works.
(Img.14)
- 2.1.4 Clear Cell Content
After completion of work you need to clear worksheet's content either in single cell or a cell range below is an example to prototype sheet and how to clear it using VBs.
(Img.15)
Here you can find that cell A1 contains some string data and cell ranges A4 to B7 needs to be clear (i.e. A4:B7) the output of code will be.
(Img.16)
This code explains that I am copying cell B1 format(using format painter) then select cell A1 and perform paste special operation (no need to go syntax explanation) everything is fine till now but still cell A1 content is not clear it just remove formatting only. To remove cell content the actual code is "selection.clearcontents" selection is what which you have selected.
For Range operation the thing would be little change here you select "Range("A4:B7").Select" means you are selecting range and again "selection.clearcontents" makes clear data from the cell.
2.1.5 Working With Form Control - Button
All codes are not executing until you give some instruction to execute it by any means here I am talking about well-known button control. It's visible to user says press me and I can do some work for you. You can add button control by this way Developer->Insert->Button. After insert button control immediately It will ask for assign macro dialog box, here you need to give some meaningful name or set it as by default and press New button if you are creating new macro else you can select any of them from list. If you press new than it will leads you to VBs editor with form subroutine name of given macro name.
(Img.17)
Here in this example you can put your VBs code between sub...end sub. Due to this whenever you press Button it will gets executed Button1_click() code block. You can put all VBs code like we have discussed earlier in button code block. Here, I show how this button works with simple example to show message box to user.
(Img.18)
And its output is looks like this.
(Img.19)
2.1.6 Declare a variable
A variable is a data holder which can be used to perform various mathematical or string operation we can define variable in VBs using Dim statement. Here is code sample for that.
(Img.20)
As shown in above screen I am declaring total 4 variables. 3 are for mathematical operation and 1 is for a string holder. Then I am assigning some values to it and display answer variable using message box. Same as for string variable I am assign name to variable and again it display using message box statement.
3. Access SAP-BAPI from Excel-VBscript.
- Identify & Analyze BAPI
- Required Fields Of BAPI
- Prepare Excel File
- Writing & Understanding Code
3.1 Identify & Analyze BAPI
Our intention is to crate sales inquiry for that we need to find suitable BAPI from SAP. Transaction code to find BAPI is "BAPI"-BAPI Explorer. Here I have found one BAPI: BAPI_INQUIRY_CREATEFROMDATA.To get understanding of the BAPI you need to check its various input/output parameters so you need to use SAP transaction code SE37 and add this BAPI in to it - FM documentation is also helps. Here you need to check and analyze its various tabs that are Attribute, Import, Changing, Tables, Exception, and Source Code to have an idea about its requirement to provide data and get output. If you are still confuse that what data to provide then you should do one more thing that first get one inquiry number that is display through transaction code VA13 and check its data from table VBAK/VBAP by specific document number along with field VBTYP (A-Inquiry, B-Quotation, C-Sales Order). From here you will have an idea what data to pass to FM. One more concept you need to know is that header and item details both is required to pass into function module. In our example we have to pass header items as a structure and items are in form of table. Please check below structure field and table field in screen preview.
Header structure as import.
(Img.21)
Item table as Table.
(Img.22)
3.2 Required Fields Of BAPI
As per previous discussion I have gone through FM and gathered require fields.
Technical Name | Description | Type | Type |
|
DOC_TYPE | Inquiry Type | Header | Structure | BAPISDHEAD |
SALES_ORG | Sales Org | Header | Structure | BAPISDHEAD |
DISTR_CHAN | Dist channel | Header | Structure | BAPISDHEAD |
DIVISION | Division | Header | Structure | BAPISDHEAD |
SALES_OFF | Sales office | Header | Structure | BAPISDHEAD |
SALES_GRP | Sales Group | Header | Structure | BAPISDHEAD |
PURCH_NO | PO | Header | Structure | BAPISDHEAD |
PURCH_DATE | PO Date | Header | Structure | BAPISDHEAD |
QT_VALID_F | Valid From | Header | Structure | BAPISDHEAD |
QT_VALID_T | Valid To | Header | Structure | BAPISDHEAD |
REQ_DATE_H | Req.Date | Header | Structure | BAPISDHEAD |
PO_ITM | Item no | Item | Table | BAPIITEMIN |
MATERIAL | Material | Item | Table | BAPIITEMIN |
REQ_QTY | Qty | Item | Table | BAPIITEMIN |
ITEM_CATEG | Item Category | Item | Table | BAPIITEMIN |
PARTN_ROLE | Partner | Item | Table | BAPIPARTNR |
PARTN_NUMB | Partner number | Item | Table | BAPIPARTNR |
3.3 Prepare Excel File
Now we have a list of fields that will be going to fill into excel file and upload it to sap via RFC FM. before preparing file you should not be confuse how to format excel sheet because we have different concept of header and item data in SAP. There may be various ways to format Excel file based on your convenience I am preparing it some simplest way so that you can easily grasp it. My file format is look like below screen preview.
(Img.23)
(Img.24)
You can also make changes as per your own format. You can check apart from material and qty all the fields are getting duplicated, I could make a single cell input for all those duplicated fields but I kept it as it is because how it is going to act in background and sent to FM is clear to you in foreground.
3.4 Writing & Understanding Code
Before we code we have make sure to clear all path in our mind or on paper I would suggest to do paper work before you write a code it would reduce your maximum code time if you have clear idea on paper. If we saw simplest steps then we need to go in this way.
- Declare required variable & Connect To SAP
- BAPI Assignment & Data Preparation
- Call BAPI, Return & Finalize
3.4.1 Declare required variable & Connect To SAP
As you can see in below screen preview.
(Img.25)
I wrote all code inside button click event routine; first of all I have to ask user on button click event if he wants to create inquiry yes/no on the basis of that I need to declare msg variable type VbMsgBoxResult which contains all types of VB messages after that and based on user decision I compare variable msg with predefined vb messages to decide code to execute or not.
Next declaration some variables as per above screen here you need to create one BAPI control, one SAP connection control and some object type of variable to pass value to SAP RFC either it is structure type or a table type or normal parameters to import/export data and some variable for logic fulfillment in VBs as per required.
Next using SAP connection objects you need to assign required connection parameters i.e. client number, user id, language, hostname, password, system number and system. If you may not have above details then you can get it from property of SAP GUI shortcut. As per below screen.
(Img.26)
After successfully assign all required details you just need to try to login to SAP system if you will get message "no connection to R/3" then you need to check and verify your login data once and retry to login to system. There could be various reason to disconnection either your given connection data are invalid or credentials might be wrong, To ensure connectivity you just try to use SAP GUI login shortcut to login to SAP.
3.4.1 BAPI Assignment & Data Preparation
After declaring required variables and making successful connection to SAP system you need BAPI name and its parameters to our declared object as shown in below screen.
(Img.27)
As per above declaration, I am assigning two BAPI one is for commit statement and other one is actual BAPI which will be using to create inquiry in SAP system. Next two declarations are to declare table parameters here both carry data line items and transfer to SAP system. Next is export parameter something like structure in SAP this carries single header line to SAP.
So Let's assign data to variable I am showing you starting from to declare header structure.
(Img.28)
As you can see in above screen I am getting excel sheet cell data into string type of variable and again assign that variable to "hdr" structure mentioning field name. Same as we can assign data to table parameter something like below screen. I have made a provision to accommodate max 20 lines of inquiry so I have to work under loop section which iterate 20 times. Here I have made 23 times of loop because of to skip first 3 rows from excel sheet which contains heading and descriptions.
(Img.29)
Most of the BAPI provides internal conversation exit with this you can directly use them they mostly named as "CONVERT" and as import parameter you have to set it as 'X' to get an effect after this you should not make any logic to make data into internal sap format.
"pritem.Rows.Add" initially it allocates initial row to VBs table then it assign values into it. Another point is that you can see some zeros which is appended before cell content because while you are working with BAPI it only accepts the whole data length it won't support internal conversation exit you must code for that.
3.4.1 Call BAPI, Return & Finalize
(Img.30)
As you can see above code I am calling BAPI via "theFunc.call" statement "theFunc" object is already contains the BAPI information and using ".call" method you can invoke SAP BAPI from excel once you call BAPI and it called successfully then return will be true else false. Then after you must issue commit statement this statement confirms submission of your BAPI at SAP end.
Up till now you have done all steps for data submission to generate BAPI for Inquiry creation now it's time to get result back from SAP system either it would be errors messages or Inquiry document number with some messages. For that you need to analyze BAPI from SAP and check for exporting parameters which provide the results. Here you must know that SAP's export will become Excel's import and vice versa. After getting details you may give acknowledge to the user as per below screen. Once your inquiry is created you may check it in SAP system for further verification.
And at the end you should make clear excel sheet especially entered inquiry data then save it for next time usage. When you open the sheet you will get fresh and blank sheet, after every inquiry creation you should make logout from SAP using "sapConnection.logoff" method.
-Avirat
- SAP Managed Tags:
6 Comments
