on 04-07-2011 10:49 AM
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
Hi Ernst, Welcome to the forum.
A question: What shows thefunc.Exception?
Regards,
ScriptMan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.