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.
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.
Here is my current code I have that works well.
Code:
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.