Author: mas
August 20, 2005

Download BDC processor Excel Add-in (V1.0)

This Excel Add-In will help to do mass processing of certain set of transactions. Like in case you wanted to update bunch of SAP data, this will help to do that without writing code. You don't need to write an ABAP program just for updating SAP data if it will not happen regularly.

This tool will help you with following 3 ways.
A. Save BDC as text file.
B. Export BDC file to SAP.
C. Execute BDC directly through RFC Call transaction. 

A. Save BDC as text file.
This is expected to be used where you need to generate BDC sessions for the system you don't have access. By passing the generated file to the person who has access to the system, you can achieve what you need to do.

B. Export BDC file to SAP.
This can be used for the case you have access to SM35 but not for business transactions. After you have exported the file and created the BDC session, you can ask the authorized person to execute the BDC session.

B. Execute BDC directly through RFC Call transaction.
If you have authorizations to execute both SM35 and target business transaction, you may be able to use this.

To execute this macro, you need MicrosoftR ExcelR95 or higher version. In addition, this requires SAP GUI to be installed your PC with librfc32.dll.
So far, this macro function is tested only under MicrosoftR ExcelR XP, SAP GUI 6.20 and SAP 4.6D.
In addition, as this macro accesses to SAP, you will need to have SAP access with good enough authorization to execute targeted transactions.

The sets of BDCs that the Add-in can process is restricted to only simple one. Most of the case, you will be able to break into simple BDC and utilize this Add-in, but some may not.

Open this Excel Add-in with enabling macro. As this is just an Add-in, will not contain any work sheet.
When you open the Add-in, you will see the new menu 'Process BDC' added on the right edge of the menu bar.
 

a) You will see the menu below.

You select menu Process BDC>New... then it will ask you if 'Add BDC worksheets'. Then hit YES, subsequently it will ask session name which will be a part of work sheet name. Enter appropriate session name in an Inputbox appeared. Worksheets named 'BDC-xxx' and 'Field-xxx' will be created. (xxx will be the session name.)

a) Select menu

b) Popup window checks with you to add sheets.

c) Input session name (Suffix for the worksheets).

d) Worksheets will be added to active work book. (If there is no active workbook, create new one.)

BDC worksheet exists for keeping the BDC data. First line is reserved for header and this line will be ignored during processing. And 2nd line is for keeping transaction name to be executed. 3rd line and rest will have a set of BDC records.

a) BDC worksheet will have header as like below.

The 'Field' worksheet is for keeping data to process. Same as 'BDC', first line is reserved for header record. The header name needs to be corresponding field that is specified in 'BDC'. During process of BDC, the macro will replace the value starting with '&' in BDC sheet and process. For example, '&RC29N-MATNR' will be replaced by 'TEST1' for 1st record.

a) Initial Field worksheet will have this header. It should be replaced later.

Activate 'BDC-xxx' sheet and select menu Process BDC>Record... then it will alert you on overwriting the spread sheet. Hit yes, then ask you the transaction name to record.
*Note, when recording, you need to ensure that you are selecting the BDC type of sheet. It checks the sheet has correct header to record.
Following the question, SAP system selection menu will pop up. Select appropriate system to record the transaction and fill user name and etc., then it will logged on to the system and displays the start of the transaction. Process the records as you need, and escape from the transaction once you complete the process. Then the recorded BDC will be transferred to the current worksheet. *Note, this sheet will work one transaction for one record. No multiple transaction can be processed at one time.

a) Select menu.

b) Popup window asks you whether proceed.

c) If Yes, Inpubox shows up and ask you transaction to record.

d) SAP logon window will come up. Select (then client, user and password will be asked) and proceed.

e) SAP window will be appeared, so process the transaction accordingly.

f) After you complete the transaction and quit it, will ask you whether to keep BDC_SUBSCR and BDC_CURSOR (they are usually not important in BDC processing). Select yes or no with your preference.

g) Recorded transaction will be pasted on to the BDC worksheet.

After creation of BDC sheet, move to Field sheet and change header line as the variant added in BDC sheet. (Note: remove '&' sign in Field sheet.) For example, if you wanted to change 'TESTMAT' in recorded BDC sheet, change it as '&MATNR' and move 'TESTMAT' to 2nd line of Field sheet. Then switch the header line of Field sheet as 'MATNR'.

a) Replace the value that you want to make it as variants which start with '&'. (&MATNR in cell F5 in the case below). If you require multiple values to be replaced, you can change all of them into variants.

b) Corresponding variant name should be kept in Field sheet. (Remove '&' for this worksheet.). Then, during the BDC session creation, &MATNR will be replaced with 'TESTMAT'. If you want to process multiple records, you just simply fill values row 2,3..n. BDC session will be created until it encounters blank line.

You select 'Create BDC and save as text file'. Then macro will create BDC file and upon completion of the creation, it will ask you to specify a file name to save. The file is created text file, you can change or add anything as you like. After that, from Utilities menu in SM35, you can upload the text file as BDC session if you like.

a) Select 'Create BDC and save as text file' in the screen below.

You select 'Create BDC and export as BDC session'. Then the macro will create BDC session and export it to SAP. To export SAP, of course you need to log on SAP. If the macro cannot find the active link to SAP, it will ask you to logon to SAP. If the active link is found, macro will ask you if you want to use current one or establish new link. After the link is established, it goes to SM35 and import the session in background. After the completion of export, it notifies you by popping up the Message. For executing the BDC, you can go to SM35 screen and execute the session.

a) Select 'Create BDC and export as BDC session' in the screen below.

Select 'Do RFC directly' and hit 'OK' button. If the macro cannot find the connection to SAP, SAP logon screen will show up and logon to SAP system that you want to update on. Excel does call transaction via RFC and update data directly from Excel. The result of the update will be filled just next to the right edge of column in 'Field' sheet. *Please note, in some case, SAP cannot return appropriate system message. And some may be in Germany. You have to make sure what the message you get will represent.

a) Select 'Do RFC directly' in the screen below.

b) Result of update will be copied next to the line executed. In this case, strange message is copied but actually this means the record is updated correctly.

To open the macro every time when the Excel is opened automatically, you can register this as Add-in. Save this macro somewhere appropriate to you temporarily first. Then go to Tools>Add-ins and select Browse in the popped up screen. Select the file you just saved and hit OK. From the next time you execute Excel, you can see the Process BDC together with standard excel menu.

a) From Tools>Add-Ins, select the menu.

b) Click Browse and select the file, then go back. Bdcprocessor will be added as available Add-Ins.