I'm going to assume that you mean by VBA ie an Excel macro.
You must first install and register the various ocx's of the SAPGUI's Controls directory onto your machine. Then in Excel, start your new project and firstly include the following in your 'References' in Excel: (I feel another Abappers Knowledge Corner item coming on....)
Select the following OCX's from the Controls directory: wdtaocx.ocx (SAP Table factory), Wdtlog.ocx (Sap Logon control), and librfc32.dll as a start. Librfc32.dll should already be registered.
Having done that you can then communicate directly with SAP using RFC's.
First write a function module that does what you want. Make sure that function module is RFC enabled (in Attributes, SE37). Here's a simple one to return a customer list:
Code:
FUNCTION Z_SO_CUSTOMER_LIST.
*"----------------------------------------------------------------------
*"*"Local interface:
*" TABLES
*" T_CUST_LIST TYPE ZSO_CUST_LIST_TABLE
*"----------------------------------------------------------------------
Select kna1~Kunnr kna1~Name1
into table t_Cust_List
from Kna1
inner join knb1 on knb1~Kunnr = kna1~kunnr and
knb1~bukrs = '1000' and
knb1~Sperr = '' and
knb1~Loevm = ''.
ENDFUNCTION.
You then write some excel code (script) that gets you a logon object from SAP. (Don't forget the other half - Log off....)
Code:
'***************************************************************************
'*
'* Function: Logon_To_SAP as Object
'*
'* Purpose: Logs onto an SAP system using the provided criteria and returns
'* a connection object to the caller
'*
'* Entry: System name to log on to as string
'* Client of system as string
'* User name as string
'* Password as String
'*
'* Global setting - specify_system - allows users to specify the logon system
'*
'* Exit: SAP Connection object
'* If specify_system is true,
'* specify_system set to false after successful login
'* system and client set to respective values
'*
'* Calls:
'*
'***************************************************************************
'*
Function Logon_To_Sap(System As String, Client As String, User As String, Password As String) As Object
Dim Sap As Object
'*
Call Push_Status("Connecting to SAP. Please Wait")
'*
'* Need to get user details ?
'*
Set Sap = CreateObject("SAP.Functions")
'*
'* If specify System is set, allow the user to specify the logon system
'*
If Not Specify_System() Then
Sap.Connection.ApplicationServer = System
Sap.Connection.Client = Client
End If
Sap.Connection.SystemNumber = "00"
Sap.Connection.User = User
Sap.Connection.Password = Password
Sap.Connection.Language = "E"
'*
'* The 'True' argument forces a silent logon. Changing this
'* to false will display the logon dialogs.
'*
If Sap.Connection.Logon(0, Not Specify_System()) = False Then
Set Sap = Nothing
Else
Call Set_Specify_System(False)
Call Set_System(Sap.Connection.ApplicationServer)
Call Set_Client(Sap.Connection.Client)
End If
Call Pop_Status
Set Logon_To_Sap = Sap
End Function
Function Log_Off_Sap(Sap As Object) As Object
Sap.Connection.LogOff
Set Sap = Nothing
End Function
And then you can call that function module from Excel:
Code:
'***************************************************************************
'*
'* Sub: Load_Cust_Details
'*
'* Purpose: Loads the Cust_Details combo box with the customer names and
'* numbers
'*
'* Entry: Combo Box to load
'*
'* Exit: Combo Box loaded dependant on SAP being available
'*
'* Calls:
'*
'***************************************************************************
Sub Load_Cust_Details(ComboBox As ComboBox)
Dim R3 As Object
Dim Table_Factory As Object
Dim T_Cust_List As Object
Dim Exception As Object
Dim Line_Count As Integer
'*
Set Table_Factory = CreateObject("Sap.TableFactory.1")
Set T_Cust_List = Table_Factory.NewTable
'*
'* Create the tables....
'*
If User_Name = "" Then
User_Details.Show vbModal
End If
Set R3 = Logon_To_Sap(System(), Client(), User_Name(), Password())
If Not R3 Is Nothing Then
If T_Cust_List.CreateFromR3Repository(R3.Connection, "ZSO_CUST_LIST", "T_CUST_LIST") = True Then
If R3.Z_SO_Customer_List(Exception, T_Cust_List:=T_Cust_List) Then
For Line_Count = 1 To T_Cust_List.Rows.Count
ComboBox.AddItem T_Cust_List(Line_Count, 2) & " - " & T_Cust_List(Line_Count, 1)
Next Line_Count
Else
Call Error("Cannot get customer list from SAP")
End If
Else
Call Error("Table factory error - cannot create customer list")
End If
End If
End Sub
The other thing to look at are the various BAPI's - they are all RFC enabled, but if you want to do your own thing, then you end up writing your own code.
The above code is basically one way - from SAP to Excel. Here's another example that was written before BAPI's arrived. This one sends data to SAP and receives an answer back:
Code:
'*
'***************************************************************************
'*
'* Sub: Go_Sap
'*
'* Purpose: Contact SAP with the current user logon and enter the order
'* via VA01
'*
'* Entry: Global variables: g_User_Name: SAP User name
'* Entry: Global variables: g_Password: SAP Password
'* Entry: Global variables: g_VkOrg: Sales Organisation
'* Entry: Global variables: g_VtWeg: Distribution Channel
'* Entry: Global variables: g_Spart: Division
'* Entry: Global variables: g_VkBur: Sales Office
'* Entry: Global variables: g_VkGrp: Sales Group
'* Entry: Spreadsheet fully complete
'*
'* Exit:
'*
'* Calls:
'*
'***************************************************************************
'*
Sub Go_Sap()
Dim R3 As Object
Dim Exception As Variant
Dim Table_Factory As Object
Dim Org_Details As String
Dim Order_Header As String
Dim Del_Addr1 As String
Dim Del_Addr2 As String
Dim Order_Table As Object
Dim Order_Line As Object
'*
Dim Last_Row As Integer
Dim Current_Row As Integer
Dim Current_Col As String
Dim Current_Cell As String
Dim Vbeln As String
Dim Error_Msg As String
Dim Matnr As String
Dim Char_List As String
Dim Current_Chars As String
Dim Char_Name As String
Dim Char_Pos As Integer
'*
On Local Error GoTo gs_err
'*
'* Sap Available ?
'*
If Sap_Available() Then
Call Push_Active_Cell
'*
'* Create Server object and Setup the connection
'*
Set R3 = Logon_To_Sap(g_system, g_Client, g_User_Name, g_Password)
If R3 Is Nothing Then
Call Error(c_Sap_not_there_msg)
Else
'*
'* Get the order into the order header and the order lines
'* into an internal table.
'*
Call Lock_fields(IBM_Blue)
Call Change_Sheet
Call Push_Status("Sending Order To SAP")
Org_Details = Build_Org_Details
Order_Header = Build_Order_Header
Del_Addr1 = Build_Delivery_Address(1)
Del_Addr2 = Build_Delivery_Address(2)
'*
'* Create the order lines table
'*
Set Table_Factory = CreateObject("Sap.TableFactory.1")
Set Order_Table = Table_Factory.NewTable
If Order_Table.CreateFromR3Repository(R3.Connection, "ZORDER_LINE", "T_ORDER_LINES") = True Then
'*
'* Build up the different order lines
'*
'* This is carried out by creating an array and then moving the
'* array to the table object
'*
Last_Row = Get_Last_Row()
Order_Table.Refresh
For Current_Row = c_Detail_Row To Last_Row
'*
'* Get the characteristic names for this product
'*
Call Set_Current_Product(Current_Row)
If Matnr <> This_product Then
Matnr = This_product()
Char_List = Get_Characteristic_Names(Matnr)
End If
Current_Chars = Char_List
'*
'* If this line is a line immediately following a title line then
'* ignore it.
'*
If Not Is_Title_Line(Current_Row) Then
'*
'* Add a row for each characteristic along with the name
'*
Set Order_Line = Order_Table.Rows.Add
Current_Col = c_Start_col
Current_Cell = Current_Col & Current_Row
'*
'* Material type
'*
Order_Line("POSNR") = Get_Posnr(Current_Row - c_Detail_Row)
Order_Line("ATNAM") = "MATNR"
Order_Line("ATWRT") = Matnr
While Range(Current_Cell).Validation.InputMessage <> ""
Set Order_Line = Order_Table.Rows.Add
Order_Line("POSNR") = Get_Posnr(Current_Row - c_Detail_Row)
'*
'* Get the characteristic Name
'*
Char_Pos = InStr(Current_Chars, c_tlist_delimiter)
If Char_Pos <> 0 Then
Order_Line("ATNAM") = Left$(Current_Chars, Char_Pos - 1)
Current_Chars = Mid$(Current_Chars, Char_Pos + 1)
Else
Order_Line("ATNAM") = Current_Chars
Current_Chars = ""
End If
Order_Line("ATWRT") = Range(Current_Cell).Value
Current_Col = Next_Column(Current_Cell)
Current_Cell = Current_Col & Current_Row
Wend
End If
gs_cont: Next Current_Row
'*
'* Set up the RFC Table Parameter
'*
If R3.Z_VA01_CREATE_SALES_ORDER(Exception, _
I_Org_Details:=Org_Details, _
I_Order_Header:=Order_Header, _
I_Del_Addr1:=Del_Addr1, _
I_Del_Addr2:=Del_Addr2, _
T_Order_Lines:=Order_Table, _
E_Vbeln:=Vbeln, _
E_Message:=Error_Msg) Then
'*
'* Error Message or Order Number ?
'*
If Error_Msg <> "" Then
Call Error(Error_Msg)
Else
Range(c_net_value_sap).Value = Get_Order_Price(R3, Vbeln)
Call Check_Costing_Status(R3, Vbeln)
Call Check_Availability(R3)
MsgBox "Sales order " & Vbeln & " created", vbInformation, "Sales Order Created"
Call Clear_Order
End If
Else
Call Error("Z_VA01_CREATE_SALES_ORDER - failed:" & Exception)
End If
'*
'* Free up the objects
'*
Order_Table.DeleteTable
Set Order_Table = Nothing
Set Table_Factory = Nothing
Else
Call Error("Could not create internal table for order lines")
End If
Set R3 = Log_Off_Sap(R3)
Call Pop_Status
Call Lock_Sheet
Call Lock_fields(White)
End If
Call Pop_Active_Cell
End If
Exit Sub
'*
gs_err:
Select Case Err.Number
'*
'* End of a data line?
'*
Case 1004
Resume gs_cont
Case Else
On Local Error GoTo 0
Resume
End Select
End Sub
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
