Connect a third-party application (MS Excel) with SAP R/3

Question

I'm working out a solution for communicate MS Excel (a VBA macro) with SAP. Sometimes is too hard to reach SAP support people at any company you are working for, so it is better to figure out your own solution.

About that reason, I started with the basic: "connect my own User profile with SAP R/3". Doing some Google research, I found a good paper with the connectivity and here is the key part of the code:

Dim objBAPIControl As Object 'Function Control (Collective object)
Dim sapConnection As Object 'Connection object
Set objBAPIControl = CreateObject("SAP.Functions")
Set sapConnection = objBAPIControl.Connection

sapConnection.client = "32"
sapConnection.user = "myUser"
sapConnection.Language = "EN"
sapConnection.hostname = "qwerty.example.com"
sapConnection.Password = "myPass"

(and more user profile values........)

Later on, I did the second step which was reading some table (in this case, the Users Table)

If sapConnection.logon(1, True) <> True Then
MsgBox "No connection to R/3!"
Exit Sub 'End program
End If
Set objUserList = objBAPIControl.Add("BAPI_USER_GETLIST")
Set objUserDetail = objBAPIControl.Add("BAPI_USER_GET_DETAIL")

returnFunc = objUserList.Call
If returnFunc = True Then
Dim objTable As Object
Set objTable = objUserList.Tables("USERLIST")
ActiveSheet.Cells(1, 1) = "User count :" & objTable.RowCount

But now, here is my question: How to run some transaction (LM02, LS26, LX03, or etc) fired from VBA ?

Thank you guys!

PS. I'm using MS Office 2007 and Windows 7.

-- EDITED:

Hey I change the way I was attacking the challenge ( I will let the OP above for helping some other guy)

This must be add to the first

Dim RfcCallTransaction As Object
Dim Messages As Object
Dim BdcTable As Object

The connection to SAP is the same, but once you're logged in:

If objBAPIControl.Connection.Logon(0, False) <> True Then
    Exit Sub
End If

Set RfcCallTransaction = objBAPIControl.Add("RFC_CALL_TRANSACTION_USING") 

here you can find the "new" arguments for the RFC_CALL_TRANSACTION old function.

    RfcCallTransaction.exports("tcode") = "SE16"
    RfcCallTransaction.exports("mode") = "N"
Set BdcTable = RfcCallTransaction.Tables("bt_data")

Until this part I'm sure of three things:

1) It does connect to SAP R/3

2) It Runs the "SE16" transaction

3) It could receive the Batch input from a BdcTable

The missing part is, how to "format" the BdcTable (I think it must be in ABAP language) to upload the exact Data (table) that I want to be run at SE16.

EDITED: I found an example of the "formatting" table I'm looking for:

add_bdcdata BdcTable, "SAPLSETB", "230", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "DATABROWSE-TABLENAME"
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "=ANZE"
add_bdcdata BdcTable, "", "", "", "DATABROWSE-TABLENAME", "KNA1"
add_bdcdata BdcTable, "/1BCDWB/DBKNA1", "1000", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "MAX_SEL"
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "=ONLI"
add_bdcdata BdcTable, "", "", "", "LIST_BRE", "250"
add_bdcdata BdcTable, "", "", "", "MAX_SEL", "5"
add_bdcdata BdcTable, "SAPMSSY0", "120", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "01/02/2012"
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "=%EX"
add_bdcdata BdcTable, "/1BCDWB/DBKNA1", "1000", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "/EE"
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "I1-LOW"
add_bdcdata BdcTable, "SAPLSETB", "230", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "/EBACK"
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "DATABROWSE-TABLENAME"

The above code still being part of the VBA macro. But I don't know what do all those fills mean.

After this task is complete, the second challenge is to save the data automatically into a MS Excel sheet.

(Could you tell me if the all editing thing is working or should I just paste the new code and erase the history?)

EDITED: I asked the same question as a summary in SCN platform. You can review it here.

EDITED: My objective in this challenge is to run a transaction ( the TCODE I'm looking for is LM02, a bin to bin transaction for warehouse) via a VBA Macro, upload some date extracted from a MS Excel file and Execute the transaction.

-- last edit: It seems like this task can't be done without the help of SAP IT support. 19/sep/12 | still researching about this topic. 25/09/12 | 02/OCT/12 --> I will try AutoIT for logging, run a basic Tcode and display the report. I'd Comment my results after I double checked it.

Answer 1

Note: This is an alternative suggestion to @fabiopagoti's answer, a completely different approach.

If - as you suggested in another comment - you want to import data into R/3 applications, you'd better not try to bypass the company IT. Instead, you could try to talk to them and get access to the Legacy System Migration Workbench (LSMW). This is a powerful toolkit that allows you to record dialog steps (under certain conditions), import some data (for example from CSV files) and then combine the recording and the data to "replay" the same steps with different data - all using standard functions and without bypassing any security checks.

Answer 2

Well, let me quote the best answer that I got. I'm pasting it here because I think somebody else will have the same question that I did.

The step one is to understand if there' s a RFC or BAPI for your transaction, but you don't have the permission for ABAP transactions, so I don't know how you can find out it.

Theorically you can try to looking for it in WEB or in SAP documentation, but then (if it exists) you need to check the interface where the the import/export parameters are defined.....so you should have the authorization for SE37.

The next step depends on if you can or can't find out a RFC function for LM02:

if there's a function, you need to understand how to run it

if there isn't a function. you need to understand if you or an abaper can develop a custom one.

Max

Thank you for your research effort.

Answer 3

I'm sorry my friend but you won't get much further than this.

What you are doing in your code is calling a BAPI (basically a RFC enable function). In other words, SAP provide standard functions which call be called from different systems. You can think this as an old fashioned API. It's also possible to create custom BAPIs, but in practice almost nobody does this.

The good news is that usually, I said usually BAPIs brings the same functionality of standard transactions. So in theory what you can do via transaction code you can do via BAPI call.

BAPI relation with transaction codes is not one to one. For example, you may use a single transaction which enables you inserting, searching, deleting and editing a particular kind of document but to achieve this using BAPI calls you will need many of them (one to create a document, one to delete, one to retrieve etc).

There's a transaction called "BAPI" (maybe the only tx code with a decent name) which contains all BAPIs in the system, its parameters and (some poor) documentation.