SAP CRM <-> VBA Excel Macro, No data return

[Mirna]

Dear experts,

I've been looking into some basic scripting when it comes to SAP and Excel. My legs are still unstable and I would see myself being in the start of a wonderful learning experience.

I've managed to establish a connection between MS Excel VB and SAP CRM Sandbox system using various forum posts and example codes from sdn. My next step would be to call a certain Function Module, pass some input parameters and then present the Export variables in a Excel sheet.

By looking at examples of this i've establish a theory but alas it does not work, hence I am asking for guidance on what I am doing wrong and how I could continue my newly started adventure.

As of now my code looks like this:

Sub CallFunctionModule()

'Declare the objects and variables
Dim functionCtrl As Object 'Function Control (Collective object)
Dim sapConnection As Object 'Connection object
Dim theFunc As Object 'Function object
Dim Returnvalue As Boolean 'Used to check if data is returned
'Create a function object

Set functionCtrl = CreateObject("SAP.Functions")
Set sapConnection = functionCtrl.Connection

'**********************************************
'Create Server object and Setup the connection (The connection seems to be establish in a correct manner)
'**********************************************
sapConnection.Client = "000"
sapConnection.user = "USERID"
sapConnection.Language = "EN"
sapConnection.SystemNumber = "00"
sapConnection.Destination = "DEST"
sapConnection.System = "SYSTEM"
If sapConnection.logon(0, False) True Then
MsgBox "No connection to R/3!"
Exit Sub 'End program
End If

'************************************************* ****
'Call function moduole
'************************************************* ****
'Reference to a function object
Set theFunc = functionCtrl.Add("FUNC_MOD_NAME")

'Determine the import parameters for the function call
theFunc.exports("IMPORT1") = "1234"
theFunc.exports("IMPORT2") = "456"
Returnvalue = theFunc.Call

If Returnvalue = True Then
MsgBox "SAP Data Found"
Else
MsgBox theFunc.Exception
End If

End Sub

As of now I seemingly establish a connection and I am able to call the function module. But every time ReturnValue = false.
If I run the function module in SAP transaction SE37 with same parameters it executes successfully and export is created.
If I change the data for functionCtrl.Add("FUNC_MOD_NAME") or theFunc.exports("IMPORT1") i'll get error messages which to me would indicate that I am finding the function module and correctly refeering to the import parameters.

What am I missing here? Why is theFunc.Call always returning false? Does this mean I am not getting data or is there some other way of determining if data is correct? If I check declarations of Import parameters in the function modules I see they are declared as NUMC (Character string with only digits) which should accept String as valid input?

Any help on this is appriciated since i'm very much enjoying this new found playground.

Regards,
Mirna

[Portal]

Hi Mirna,

A question: What shows thefunc.Exception?

Regards,
Portal

[Mirna]

Hi Portal, thanks for the quick response.

The exception actually seems to be blank, all I get is a msgbox pop-up showing nothing. So I guess that's not very helpful, could it have to do anything with my authorities inside the SAP instance? As I mentioned I am able to execute the function module in SE37 and get correct values. But do I need extra authorities to be able to receive the data to excel? But I would hope there would be some kind of "missing authority" exception in that case.

When I debug the code i notice that

theFunc.exports("IMPORT1") = "123"
theFunc.exports("IMPORT2") = "456"

doesn't seem to get a value, if I hold my cursor over any other instanciated variable it shows the current value it holds, but hoovering over these two declarations doesn't indicate that they are being set.

Any helpful tips on troubleshooting / solving this would be appriciated.

Thanks,
Mirna