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

[Former Member]

Dear experts and everyone else that feels in the mood to contribute,

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.

Best Regards,

Ernst

[script_man · Accepted solution]

Hi Ernst, Welcome to the forum.

A question: What shows thefunc.Exception?

Regards,

ScriptMan

[Former Member · Accepted solution]

Hello ScriptMan, thanks for the welcome and I hope your weekend have been nice.

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.

Many thanks,

Ernst.

Edited by: Zeblion on Apr 11, 2011 2:36 PM

Edited by: Zeblion on Apr 11, 2011 2:37 PM

[script_man · Accepted solution]

Hi Ernst,

Probably should be specifically mentioned, the function module, so that we can test it. I can see so far no errors in your code.

Regards,

ScriptMan

[Former Member · Accepted solution]

Hello again ScriptMan!

I've been looking into this code again whenever I get some spare time. A few noteworthy things I can add is that the RFC I try to use does only have IMPORT parameters and EXPORT parameters. The main idea after I get this working is calling the RFC using specified IMPORT parameters and then nicely present the EXPORT parameters after the RFC has executed. I can see alot of code examples that uses tables when they are presenting results which I can't really seem to apply to my example since I only want to catch the EXPORT parameters from the RFC, I might have misunderstood how the process works though.

As mentioned above I do not get any Exception after theFunc.call returns false though i've found two error messages that might give some clues. If I use sapConnection.LastError I can actually see what seems to be the failure, first I add it infront of my theFunc.Call:

sapConnection.LastError

Returnvalue = theFunc.Call

And I get the following detailed error message:

Error group

RFC_ERROR_APPLICATION_EXCEPTION

Message

EXCEPTION TABLE_NOT_ACTIVE RAISED

If I then add .LastError after the call following error message arises:

Returnvalue = theFunc.Call

sapConnection.LastError

Error Group

RFC_ERROR_SYSTEM_FAILURE

Message

EXCEPTION SYSTEM_FAILURE RAISED

I've looked so that the RFC is Remote Enabled in SE37 so that's not it.. Any help here would be appriciated because I feel i'm stretching my own knowledge to the limit here:)

Best Regards,

Ernst

Edit: Do you have any suggestions on a standard Remote Enabled RFC to use? I'm running a sandbox environment for SAP CRM 5.2.

Edited by: Zeblion on May 6, 2011 10:17 AM

[script_man · Accepted solution]

Hi Zeblion,

You have seen many examples of RFC requests in the web. I will also present another one. Maybe you can solve your problem based on this example:

Sub CallFunctionModule()

Dim sap As Object
Dim conn As Object
Dim fb As Object
Dim tOptions As Object
Dim tFields As Object
Dim tData As Object
Dim RowData$()
Dim j&, i&, k&

Set sap = CreateObject("SAP.Functions")
Set conn = sap.Connection
conn.System = "SAP-SYSTEM"
conn.client = "123"
conn.user = "USERNAME"
conn.Password = "USERPW"
conn.Language = "DE"


If conn.logon(0, True) <> True Then
    MsgBox "No connection to R/3!", vbOKOnly, "comment"
Else
    Set fb = sap.Add("RFC_READ_TABLE")
    With fb
        .exports("QUERY_TABLE") = "LFBW"
        .exports("DELIMITER") = "|"
    End With
    Set tOptions = fb.tables("OPTIONS")
    Set tFields = fb.tables("FIELDS")
    Set tData = fb.tables("DATA")
   
    tOptions.Rows.Add
    tOptions(1, "TEXT") = "BUKRS = '1000' "
    tOptions.Rows.Add
    tOptions(2, "TEXT") = "AND WT_EXDT GE '20110101' "
    tOptions.Rows.Add
    tOptions(3, "TEXT") = "AND WT_EXDT LE '20111231' "
   
    tFields.Rows.Add
    tFields(1, "FIELDNAME") = "BUKRS"
    tFields.Rows.Add
    tFields(2, "FIELDNAME") = "LIFNR"
    tFields.Rows.Add
    tFields(3, "FIELDNAME") = "WT_SUBJCT"
    tFields.Rows.Add
    tFields(4, "FIELDNAME") = "WT_WTSTCD"
    tFields.Rows.Add
    tFields(5, "FIELDNAME") = "WT_WITHCD"
    tFields.Rows.Add
    tFields(6, "FIELDNAME") = "WT_EXNR"
    tFields.Rows.Add
    tFields(7, "FIELDNAME") = "WT_EXRT"
    tFields.Rows.Add
    tFields(8, "FIELDNAME") = "WT_EXDF"
    tFields.Rows.Add
    tFields(9, "FIELDNAME") = "WT_EXDT"
    
    If fb.call Then
        j = tData.RowCount
        If j Then
           For i = 1 To j
                RowData = Split(tData(i, "WA"), "|")
                For k = 1 To 9
               
                    Tabelle1.Cells(i, k).Value = RowData(k - 1)
                 
                Next
           Next
        End If
    else
     msgbox fb.Exception , vbOKonly, "comment"
    End If

    Set tFields = Nothing
    Set tData = Nothing
    Set tOptions = Nothing
    Set fb = Nothing
    conn.logoff
End If

Set conn = Nothing
Set sap = Nothing

End Sub

Regards,

ScriptMan

[Former Member · Accepted solution]

Hello again ScriptMan!

Excellent example I must say! I've edited the code so that it would fit a table in our system alongside with correct FIELD and OPTION values and got the data perfectly! . This clearly demonstrates how I can call the RFC_READ_TABLE function module and get the data to Excel. I'm going to analyze this and try to apply it to my specific example.

Many thanks ScriptMan i'll come back for more questions for sure!

Best Regards,

Ernst.

[Former Member · Accepted solution]

ScriptMan,

Wondering if you have an excel macro code to open SAP R/3 400 and open transaction IW37, to display work schedule for the current months date, to work centre 554FT, excluding CRTD, CNF, TECO order status. Then to sort report results by order and export to the original excel file. My current code only opens SAP not loggin on, openning transactions and running reports, which is what is desired. You help would much appreciated.

[script_man · Accepted solution]

Hi SAPDyl,

It would be nice if you could show us in a new thread your current example. We could then make concrete proposals for solutions.

Regards,

ScriptMan

[Former Member · Accepted solution]

Hello,

I am facing an error on following line

Set sap = CreateObject("SAP.Functions")

pls help me

[script_man · Accepted solution]

Hi sachinekbote,

Check out especially the last post of this thread:

Regards,

ScriptMan

[sankarganesh_thirumalaisa · Accepted solution]

Hello ScriptMan,

Your logic works fine.

Could you please explain what is the meaning of "WA" in tData(i, "WA").

[holger_khn · Accepted solution]

Hello.

"WA" is the workarea from table DATA of functionmodule RFC_READ_TABLE. Check this functionmodule in SE37. There you can see it.

Greetings,

Holger

[sankarganesh_thirumalaisa · Accepted solution]

Hello Holger,

Yes,Now i checked it.

Thank you so much for your information.

[Former Member]

Hi,

We are planning to integrate SAP CRM Web UI with Excel. Users will click on a button and they will get an Excel file where they will make modifications and save and accordingly data will be saved in sap crm.

Can you provide me some study materials or links regarding this?

Regards,

Sayan