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.