SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Excel via script to SAP



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1639

PostPosted: Thu Oct 04, 2007 10:09 am    Post subject: Excel via script to SAP Reply with quote

Author: Richard Harper
Original: http://www.sapfans.com/forums/viewtopic.php?t=188241

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....)

Tools->Macro->Visual Basic Editor->Tools References

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
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.