CALLing SAP wiht VB script

[Micro]

Hi all,

I am calling SAP from excel and for that i m using VB script . I am able to connect to the sap and getting data .
the problem is that from excel i am calling RFCs for 4-5 times and i dont want that user should be prompt everytime for the userid and password for the connection to SAP.

How can i avoid that?
Please suggest something as its needed urgently.
Here is the code i m using to connect to sap.

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

sapConnection.Client = "400"
sapConnection.Applicationserver = "172.25.8.122"
sapConnection.Hostname = "172.25.8.122"
sapConnection.Language = "EN"
sapConnection.systemid = "SRM 4.0"
sapConnection.usesaplogonini = "false"
If sapConnection.logon(1, False) True Then

MsgBox "No connection to R/3!"

Exit Sub 'End program
Else

' call function module to get data
Call get_scinfo
End If

Thanks for any idea to solve this,
Micro

[Rima]

Hi Micro,

This code in MSExcel works for me:

Dim conn As Object, functions As Object, oLogonCtrl As Object, getmaterialdata, reservationgetdetail As Object Public Sub ConectarExcelSAP() Set functions = CreateObject("SAP.Functions") 'Dim funcions As New SAPFunctions Set oLogonCtrl = CreateObject("SAP.Logoncontrol.1") 'Dim oLogonCtrl As New SAPLogonControl Set conn = oLogonCtrl.NewConnection Rem conn.GroupName = "PRD_sie2000" Load Conexion Conexion.Show conn.User = Conexion.Txtusuario.Value conn.Password = Conexion.Txtclave.Value conn.Client = Conexion.Txtmandante.Value conn.HostName = Conexion.Txtservidor.Value conn.SystemNumber = Conexion.Txtsistema.Value Unload Conexion If conn.Logon(0, True) True Then MsgBox ("Error en la conexión...."): End End If Set functions.Connection = conn MsgBox ("Está conectado a SAP....") Application.StatusBar = "Estado: Conectado a SAP" End Sub Public Sub desconectarExcelSAP() conn.Logoff Set conn = Nothing Set oLogonCtrl = Nothing Set functions = Nothing Application.StatusBar = "Estado: Sin conexión a SAP" MsgBox ("Ha sido desconectado de SAP....") Application.StatusBar = "" End Sub Private Function getmaterialdatano(codigo As Variant, no As Variant) As Variant Set getmaterialdata = functions.Add("RFC_GET_MATERIAL_DATA") getmaterialdata.Exports("I_MATERIAL") = codigo Set Tableobj = getmaterialdata.tables.Item("SAP_FIELD_DATA") If getmaterialdata.Call True Then MsgBox ("Error en la función"): End Else getmaterialdatano = Tableobj.Value(no, "FIELDVALUE") End If Set Tableobj = Nothing Set getmaterialdata = Nothing End Function Public Function Descripcion(codigo As Variant) As Variant Descripcion = getmaterialdatano(codigo, 152) End Function Public Function Un(codigo As Variant) As Variant Un = getmaterialdatano(codigo, 14) End Function Public Function Tipomaterial(codigo As Variant) As Variant Tipomaterial = getmaterialdatano(codigo, 10) End Function Public Function Ramo(codigo As Variant) As Variant Ramo = getmaterialdatano(codigo, 11) End Function Public Function Grupo(codigo As Variant) As Variant Grupo = getmaterialdatano(codigo, 12) End Function Public Function Noantiguo(codigo As Variant) As Variant Noantiguo = getmaterialdatano(codigo, 13) End Function Public Function Sector(codigo As Variant) As Variant Sector = getmaterialdatano(codigo, 41) End Function Public Sub visualizarreserva() Set reservationgetdetail = functions.Add("BAPI_RESERVATION_GETDETAIL") reservationgetdetail.Exports("RESERVATION") = ActiveCell.Value Set Tableobj = reservationgetdetail.tables.Item("RESERVATION_ITEMS") If reservationgetdetail.Call True Then MsgBox ("Error en la función"): End Else For i = 1 To Tableobj.RowCount For j = 1 To Tableobj.ColumnCount ActiveCell.Offset(i, j) = Tableobj.Value(i, j) Next j Next i End If Set Tableobj = Nothing Set reservationgetdetail = Nothing End Sub Public Sub test() Set readtext = functions.Add("RFC_READ_TEXT") Set Tableobj = readtext.tables.Item("TEXT_LINES") On Error GoTo nxt: Set ObjRow = Tableobj.Rows.Add ObjRow(1) = "300" ObjRow(2) = "VBBP" Rem pedido 70 y posición 10 ObjRow(3) = "0000000070000010" ObjRow(4) = "0001" Rem ObjRow(5) = "ES" If readtext.Call True Then MsgBox ("Error en la función"): GoTo nxt Rem Set Tableobj = readtext.tables.Item("MESSAGES") MsgBox Tableobj.Value(1, 8) GoTo nx nxt: MsgBox "error" nx: Set ObjRow = Nothing Set Tableobj = Nothing Set readtext = Nothing End Sub Public Sub listafacturas() Set listafact = functions.Add("BAPI_BILLINGDOC_GETLIST") Set StructureObj = listafact.Exports("REFDOCRANGE") Set Tableobj = listafact.tables.Item("BILLINGDOCUMENTDETAIL") On Error GoTo nxt: StructureObj.Value("SIGN") = "E" StructureObj.Value("OPTION") = "EQ" StructureObj.Value("REF_DOC_LOW") = "0" StructureObj.Value("REF_DOC_HIGH") = "9999999999" If listafact.Call True Then MsgBox ("Error en la función"): GoTo nxt Else For i = 1 To Tableobj.RowCount For j = 1 To Tableobj.ColumnCount ActiveCell.Offset(i, j) = Tableobj.Value(i, j) Next j Next i End If GoTo nx nxt: MsgBox "error " & Err.Description nx: Set Tableobj = Nothing Set StructureObj = Nothing Set listafact = Nothing End Sub Public Sub simularpedido() Set simpe = functions.Add("BAPI_SALESORDER_SIMULATE") Set StructureObj1 = simpe.Exports("ORDER_HEADER_IN") Set Tableobj1 = simpe.tables.Item("ORDER_ITEMS_IN") Set Tableobj2 = simpe.tables.Item("ORDER_PARTNERS") Set Tableobj3 = simpe.tables.Item("ORDER_CONDITION_EX") Rem Set Tableobj3 = simpe.tables.Item("ORDER_ITEMS_IN") On Error GoTo nxt: For i = 1 To 1000 Set ObjRow = Tableobj1.Rows.Add ObjRow(4) = "000000000000040408" 'Material ObjRow(14) = "0000000000001" 'Cantidad Next i Set ObjRow = Tableobj2.Rows.Add ObjRow(1) = "AG" 'Función interlocutor solicitante ObjRow(2) = "0000242255" 'Solicitante StructureObj1.Value("DOC_TYPE") = "ZP11" StructureObj1.Value("SALES_ORG") = "OV01" StructureObj1.Value("DISTR_CHAN") = "C1" StructureObj1.Value("DIVISION") = "Z1" Debug.Print "98I " & Time If simpe.Call True Then MsgBox ("Error en la función"): GoTo nxt Else Debug.Print "98I " & Time Set StructureObj2 = simpe.Imports("RETURN") MsgBox (StructureObj2.Value("MESSAGE")) Rem GoTo nx For i = 1 To Tableobj3.RowCount For j = 1 To Tableobj3.ColumnCount ActiveCell.Offset(i, j) = Tableobj3.Value(i, j) Next j Next i End If GoTo nx nxt: MsgBox "error " & Err.Description nx: Set ObjRow = Nothing Set Tableobj1 = Nothing Set Tableobj2 = Nothing Set Tableobj3 = Nothing Set StructureObj1 = Nothing Set StructureObj2 = Nothing Set simpe = Nothing End Sub Public Function Leertexto(nombretexto As Variant, objetotexto As Variant, idtexto As Variant) As Variant Set readtext = functions.Add("RFC_READ_TEXT") Set Tableobj = readtext.tables.Item("TEXT_LINES") On Error GoTo nxt: Set ObjRow = Tableobj.Rows.Add Rem mandante Rem ObjRow(1) = "300" ObjRow(2) = objetotexto Rem pedido 70 y posición 10 ObjRow(3) = nombretexto ObjRow(4) = idtexto Rem ObjRow(5) = "ES" texto = "" If readtext.Call True Then Leertexto = "Error en la función" Else Rem Set Tableobj = readtext.tables.Item("MESSAGES") For i = 1 To Tableobj.RowCount texto = texto & " " & Tableobj.Value(i, 8) Next i Leertexto = texto End If nxt: Rem C:\Archivos de programa\SAP\FrontEnd\Controls\wdtfuncs.ocx functions.RemoveAll Set ObjRow = Nothing Set Tableobj = Nothing Set readtext = Nothing End Function

Regards,
Rima

[Pepa]

Hi Rima,

This is my code in FoxPro, can you guide me where I am doing wrong?

I want to create reservation same as CN22. for said material.
have done it with code.
It add componets to network, but only problem is item added in network by this method donts shows in TC-YPSYM08.
How can I do that?

SET DEFAULT TO E:\sap_add_comp_network
SET EXCLUSIVE OFF
CLOSE ALL

CLOSE ALL
SET EXCL OFF
SET STRICTDATE TO 0
SET DELE ON
CLOSE DATA

Functions = CREATEOBJECT("SAP.Functions")
Functions.CONNECTION.ApplicationServer ='10.25.0.66'
Functions.CONNECTION.SYSTEM = '00'
Functions.CONNECTION.tracelevel=6
Functions.CONNECTION.client = '400'
Functions.CONNECTION.USER = 'P10021331'
Functions.CONNECTION.PASSWORD = 'P@ssword4'
Functions.CONNECTION.language = 'EN'
IF Functions.CONNECTION.Logon(0,.f.)=.f. Then
RETURN
ENDIF

RfcCallTransaction = Functions.ADD("BAPI_NETWORK_COMP_ADD")
RfcCallTransaction.Exports("number")="500000000954"

RfcCallTransaction.tables.Item("I_COMPONENTS_ADD")
objtable1=RfcCallTransaction.tables("I_COMPONENTS_ADD")
readtext1 = functions.Add("RFC_READ_TEXT")
Tableobj1 = readtext1.tables.Item("TEXT_LINES")

USE mattab
FOR i=1 TO RECCOUNT()
objtable1.appendrow
FOR j=1 TO FCOUNT()
GOTO i
mm2="a"+ALLTRIM(STR(j))
?&mm2
objtable1.CELL(1,j)=&mm2
endfor
endfor

RfcCallTransaction.call

?objtable1.rowcount,objtable1.ColumnCount

RfcCallTransaction.tables.Item("E_MESSAGE_TABLE")
objtable=RfcCallTransaction.tables("E_MESSAGE_TABLE")
readtext = functions.Add("RFC_READ_TEXT")
Tableobj = READtext.tables.Item("TEXT_LINES")

RfcCallTransaction = Functions.ADD("BAPI_TRANSACTION_COMMIT")
RfcCallTransaction.call

Create table ('E:\sap_add_comp_network\E_MESSAGE_TABLE') ; &&Change as required
(a1 C(100),a2 C(100),a3 c(100),a4 c(100),a5 c(100),a6 c(100),a7 c(100),a8 char(100),;
a9 C(100),a10 C(100),a11 c(100),a12 c(100),a13 c(100),a14 char(100),a15 t,a16 char(100),;
a17 C(100),a18 C(100),a19 c(100),a20 c(100),a21 c(100),a22 c(100),a23 c(100),a24 char(100),;
a25 C(100),a26 C(100),a27 c(100),a28 c(100),a29 c(100),a30 c(100),a31 c(100),a32 char(100),;
a33 C(100),a34 C(100),a35 c(100),a36 c(100),a37 c(100),a38 c(100),a39 c(100),a40 char(100),;
a41 C(100),a42 C(100),a43 c(100),a44 c(100),a45 c(100),a46 c(100),a47 c(100),a48 char(100),;
a49 C(100),a50 C(100),a51 c(100),a52 c(100),a53 c(100),a54 c(100),a55 c(100),a56 char(100),;
a57 C(100),a58 C(100),a59 c(100),a60 c(100),a61 c(100),a62 c(100),a63 c(100),a64 c(100),a65 c(100),;
a66 C(100),a67 C(100),a68 c(100),a69 c(100),a70 c(100),a71 c(100),a72 c(100),a73 c(100),a74 c(100),;
a75 C(100),a76 C(100),a77 c(100),a78 c(100),a79 c(100),a80 c(100),a81 c(100),a82 c(100),a83 c(100),a84 c(100),;
a85 c(100),a86 c(100),a87 c(100))
CLOSE TABLES
IF RfcCallTransaction.call=.t.
USE E_MESSAGE_TABLE
FOR i1 = 1 TO objtable.rowcount
APPEND BLANK
FOR j1=1 TO objtable.ColumnCount
mm1="a"+ALLTRIM(STR(j1))
chck=objtable.cell(i1,j1)

?chck,"CELL",MM1,"CO_CODE","ROW:",I1,"COLUMN",J1
replace &mm1 WITH objtable.cell(i1,j1)
ENDFOR
ENDFOR
ENDIF

CLOSE ALL

Thanks,
Pepa