RFC Call from Access to SAP 1

[jbivin]

I am just wondering if anyone has any experience with RFC calls into SAP from access.

Here is my current code I have that works well.

Dim LogonControl
Dim conn
Dim funcControl
Dim TableFactoryCtrl
Dim RFC_READ_TABLE
Dim eQUERY_TAB
Dim tblOptions
Dim tblData
Dim tblFields
Dim retcd
Dim strExport1
Dim strExport2
Dim intRow As Integer
Dim StrMaterial As String
Dim objFileSystemObject As Object
Dim filOutput As Object

Public Sub LogOn()

    Set LogonControl =         VBA.CreateObject("SAP.LogonControl.1")
    Set funcControl = VBA.CreateObject("SAP.Functions")
    Set conn = LogonControl.NewConnection
    
    'Code To Connect To SAP
    
    conn.System = "PR1" ' System ID
    conn.Client = "005" ' Client
    conn.Language = "EN" ' Language
    'conn.User = "" ' User ID
    'conn.Password = "" ' Password
    retcd = conn.LogOn(0, False)
      
    Select Case retcd
        
        Case False

            VBA.MsgBox "Cannot log on!", vbInformation + vbOKOnly, "Logon Failed"
            Set LogonControl = Nothing
            Set funcControl = Nothing
            Set conn = Nothing
            Exit Sub
    
        Case True

            funcControl.Connection = conn
            
                
                    Call GetBins
            
            Set LogonControl = Nothing
            Set funcControl = Nothing
            Set conn = Nothing
    
    End Select
    
End Sub

'Code To LogOff SAP

Private Sub LogOff()

    conn.LogOff

End Sub

'Code for RFC Call

Private Sub GetBins()

    Set objFileSystemObject = VBA.CreateObject("Scripting.FileSystemObject")
    Set filOutput = objFileSystemObject.CreateTextFile("F:\Operations Improvement\Manufacturing Improvement\Interplant Material Delivery System\Warehouse\LQUA.CSV", True)

    Set RFC_READ_TABLE = funcControl.Add("RFC_READ_TABLE")
    Set strExport1 = RFC_READ_TABLE.Exports("QUERY_TABLE")
    Set strExport2 = RFC_READ_TABLE.Exports("DELIMITER")
    Set tblOptions = RFC_READ_TABLE.Tables("OPTIONS")
    Set tblData = RFC_READ_TABLE.Tables("DATA")
    Set tblFields = RFC_READ_TABLE.Tables("FIELDS")
    
    strExport1.Value = "LQUA"
    strExport2.Value = ","
    
    StrMaterial = InputBox("Please Input Part Number")
    
    tblOptions.AppendRow
    tblOptions(1, "TEXT") = "MATNR = '" & StrMaterial & "'" & "AND WERKS = '6104'"
    
    tblFields.AppendRow
    tblFields(1, "FIELDNAME") = "MATNR"
    
    tblFields.AppendRow
    tblFields(2, "FIELDNAME") = "WERKS"
    
    tblFields.AppendRow
    tblFields(3, "FIELDNAME") = "LGPLA"
    
    tblFields.AppendRow
    tblFields(4, "FIELDNAME") = "VERME"
    
    On Error Resume Next
    Application.VBE.MainWindow.Visible = False
    If VBA.Err.Number > 0 Then VBA.Err.Clear
    On Error GoTo 0

    If RFC_READ_TABLE.Call = True Then
    
        If tblData.RowCount > 0 Then
    
    
            ' Change Next line to write a different header row
        
        
            filOutput.WriteLine "Material Number, Plant, Location, Available Quantity"
        
        
            For intRow = 1 To tblData.RowCount
        
        
                filOutput.WriteLine tblData(intRow, "WA")
        
            Next
    
    
                MsgBox "COMPLETED SUCCESSFULLY"
                
                FollowHyperlink ("F:\Operations Improvement\Manufacturing Improvement\Interplant Material Delivery System\Warehouse\LQUA.CSV")
            
        Else
        
        
            MsgBox "No records returned"
    
    
        End If



    Else


        MsgBox "ERROR CALLING SAP REMOTE FUNCTION CALL"


    End If


    Set objFileSystemObject = Nothing
    Set filOutput = Nothing

    Set RFC_READ_TABLE = Nothing
    Set strExport1 = Nothing
    Set strExport2 = Nothing
    Set tblOptions = Nothing
    Set tblData = Nothing
    Set tblFields = Nothing
    
    Call LogOff

End Sub

As this code stands, I can do one part number at a time and I have to log on every time I want to do a different part number.

I need a way to do multiple part numbers at one time and output them in an excel CSV like I am currently doing and only have to log into SAP one time.

Thanks in advance for any help.

 

[PHV]

Does RFC support this syntax ?
tblOptions(1, "TEXT") = "MATNR IN ('mat1','mat2','mat3') AND WERKS='6104'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jbivin]

I am not sure but I will try that. I am not real familiar with the RFC commands and have just started experimenting with them.

 

[jbivin]

No that did not work and I tried several variations. I know that when I go into SAP I can copy and paste up to 75 different part numbers and it will pull the information, but I do not know how to replicate that in and RFC call.

 

[PHV]

The IN operator should work:

http://help.sap.com/saphelp_apo/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jbivin]

PHV,

Thanks for the reply. To get it to work I played around with it and you have to do it like the VALUES in SQL. Here is the code I used that worked wonderful.

"MATNR IN ('" & StrMaterial1 & "', '" & StrMaterial2 & "', '" & StrMaterial3 & "') AND WERKS = '6104'"

Thanks for your help.