Reply from amjadmahmood on Feb 18 at 11:50 PM Thanks for your query.
You need to create a xlsm macro-enable workbook.
name of sheet1 is menu
name of sheet2 is FBL5N
name of sheet3 is Cust_list
name of sheet4 is "Check Tables" a camophlogue name for storing user name,
password, server IP address etc.
In the menu sheet you insert a drawing object to which you assign a macro
so that when you click over that drawing object the tool starts the process.
Also make sure that in the menu sheet cell address S2 specifies the file
path: in my case it is C:\Program Files
(x86)\SAP\FrontEnd\SAPGui\saplogon.exe
you also need to set the references to SAP runtime libraries etc, you do
this in VBA explorer window.
In the module1 just copy paste the following function:
Function SessionExists(oSession As Object) As Boolean
SessionExists = False
Dim SAP As Object, SAPGui As Object, SAPConnections As Object
Dim cntConnection As Long, i As Long, SAPConnection As Object
Dim Sessions As Object, cntSession As Long, j As Long, Session As Object
Set SAP = GetObject("SAPGUI")
If Not IsObject(SAP) Then
Exit Function
End If
Set SAPGui = SAP.GetScriptingEngine
If Not IsObject(SAPGui) Then
Exit Function
End If
Set SAPConnections = SAPGui.Connections()
If Not IsObject(SAPConnections) Then
Exit Function
End If
cntConnection = SAPConnections.Count()
For i = 0 To cntConnection - 1
Set SAPConnection = SAPGui.Connections(CLng(i))
If IsObject(SAPConnection) Then
Set Sessions = SAPConnection.Sessions()
If IsObject(Sessions) Then
cntSession = Sessions.Count()
For j = 0 To cntSession - 1
Set Session = SAPConnection.Sessions(CLng(j))
If IsObject(Session) Then
If Session.ID = ooSession.ID And Session.Name =
ooSession.Name Then
SessionExists = True
Exit Function
End If
End If
Next j
End If
End If
Next i
End Function
I suggest you leave this code intact...better you leave module1 intact and
isolated from rest of the modules.
You will never need to do any change in module 1.
Now let's add a new module (Module 2)\
paste the following code there.
Public SAP As Object
Public SAPGui As Object
Public SAPCon As Object
Public SAPSession As Object
'run saplogon if it is not running
Sub OpenSAP()
If Dir(Sheets("Menu").Range("S2") ) <> "" Then
Sheets("Check Tables").Range("A5") = Sheets("Menu").Range("S2")
Else
MsgBox "File/Path error..." & vbCr & Sheets("Menu").Range("S2")
End
End If
Dim SAPTaskID As Double
Dim SAPLogonPad As String
SAPLogonPad = Sheets("Check Tables").Range("A5") '"C:\Program
Files\SAP\FrontEnd\SAPgui\saplogon.exe"
On Error Resume Next
AppActivate "SAP Logon 710"
If Err <> 0 Then
Err = 0
SAPTaskID = Shell(SAPLogonPad, vbMinimizedNoFocus)
If Err <> 0 Then
MsgBox "Cannot start SAPLOGON", vbCritical, "SAPLOGON FAILED"
End
Else
'MsgBox "SAP Logon activated " & SAPTaskID, vbInformation, "SAP
Running"
End If
End If
End Sub
Sub SAPLogon()
Dim doc
Dim dbPath As String
Dim dbConnectStr As String
Dim mybook As String
Dim cnt As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Mylist As Object
Dim dt As String
Dim rx As Long
dbPath = ActiveWorkbook.Path & "\sap-db.accdb"
dbConnectStr = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
dbPath & ";Persist Security Info=False;Mode=Share Deny None;Jet
OLEDB:Database Locking Mode=1;"
mybook = ActiveWorkbook.Name
Set cnt = New ADODB.Connection
'cnt.Open dbConnectStr
mybook = ActiveWorkbook.Name
Dim lr
Call OpenSAP
Static sapID As String
Static sapPassword As String
sapID = Sheets("Check Tables").Range("A1")
sapPassword = Sheets("Check Tables").Range("A2")
If SAPGui Is Nothing Then
Set SAP = GetObject("sapgui")
Set SAPGui = SAP.GetScriptingEngine
End If
If SAPCon Is Nothing Then
Set SAPCon = SAPGui.OpenConnectionByConnectionString(Sheets(&qu ot;Check
Tables").Range("A4"), True)
End If
If SAPSession Is Nothing Then
Set SAPSession = SAPCon.Children(0)
With SAPSession
.findById("wnd[0]/usr/txtRSYST-MANDT").Text = Sheets("Check
Tables").Range("A3")
.findById("wnd[0]/usr/txtRSYST-BNAME").Text = Sheets("Check
Tables").Range("A1")
.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = Sheets("Check
Tables").Range("A2")
.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
.findById("wnd[0]").sendVKey 0
End With
If SAPSession.Children.Count > 1 Then
SAPSession.findById("wnd[1]/usr/radMULTI_LOGO N_OPT2").Select
SAPSession.findById("wnd[1]/usr/radMULTI_LOGO N_OPT2").SetFocus
SAPSession.findById("wnd[1]/tbar[0]/btn[0]&qu ot;).press
End If
End If
Set SAPSession = SAPCon.Children(0)
With SAPSession ' put a break point here before executing
' here u need to use macro recorder and determine the ID of the
PO number field
' adjustments required in this code Using macro recorder
scripts
End With
If MsgBox("FBL5n...?", vbYesNo) = vbYes Then
Sheets("FBL5N").Select
lr = Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
cust = Range("Z" & Cells.Rows.Count).End(xlUp)
If cust = "" Then
rw = 1
Else
rw = 1
Do While Sheets("Cust_list").Range("A" & rw) <> cust
rw = rw + 1
Loop
rw = rw + 1
End If
Do While Sheets("Cust_list").Range("A" & rw) <> ""
cust = Sheets("Cust_list").Range("A" & rw)
SAPSession.findById("wnd[0]/tbar[0]/okcd" ;).Text = "fbl5n"
SAPSession.findById("wnd[0]").sendVKey 0
SAPSession.findById("wnd[0]/usr/ctxtDD_KUNNR-LOW").Text = ""
SAPSession.findById("wnd[0]/tbar[1]/btn[17]&q uot;).press
SAPSession.findById("wnd[1]/usr/cntlALV_CONTA INER_1/shellcont/shell").setCurrentCell
1, "TEXT"
SAPSession.findById("wnd[1]/usr/cntlALV_CONTA INER_1/shellcont/shell").selectedRows
= "1"
SAPSession.findById("wnd[1]/usr/cntlALV_CONTA INER_1/shellcont/shell").doubleClickCurrentCell
SAPSession.findById("wnd[0]").sendVKey 2
SAPSession.findById("wnd[1]/usr/cntlOPTION_CO NTAINER/shellcont/shell").clearSelection
SAPSession.findById("wnd[1]").Close
SAPSession.findById("wnd[0]/usr/ctxtDD_KUNNR-LOW").Text = cust
SAPSession.findById("wnd[0]/usr/ctxtDD_KUNNR-LOW").caretPosition = 6
SAPSession.findById("wnd[0]").sendVKey 0
SAPSession.findById("wnd[0]/usr/radX_AISEL&qu ot;).Select
SAPSession.findById("wnd[0]/usr/ctxtSO_BUDAT-HIGH").Text = "30112013"
SAPSession.findById("wnd[0]/usr/ctxtSO_BUDAT-HIGH").SetFocus
SAPSession.findById("wnd[0]/usr/ctxtSO_BUDAT-HIGH").caretPosition = 8
SAPSession.findById("wnd[0]/usr/chkX_NORM&quo t;).Selected = True
SAPSession.findById("wnd[0]/usr/chkX_SHBV&quo t;).Selected = True
SAPSession.findById("wnd[0]").sendVKey 0
SAPSession.findById("wnd[0]/tbar[1]/btn[8]&qu ot;).press
SAPSession.findById("wnd[0]/tbar[1]/btn[33]&q uot;).press
SAPSession.findById("wnd[1]/usr").vertic alScrollbar.Position = 1
SAPSession.findById("wnd[1]/usr").vertic alScrollbar.Position = 2
SAPSession.findById("wnd[1]/usr/lbl[1,19]&quo t;).SetFocus
SAPSession.findById("wnd[1]/usr/lbl[1,19]&quo t;).caretPosition = 4
SAPSession.findById("wnd[1]").sendVKey 2
If SAPSession.findById("wnd[0]/sbar").Text = "Layout applied" Then
'SAPSession.findById("wnd[0]/tbar[0]/btn[0]&qu ot;).press
'SAPSession.findById("wnd[1]/usr/subSUBSCREEN_ STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
'SAPSession.findById("wnd[1]/usr/subSUBSCREEN_ STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").SetFocus
'SAPSession.findById("wnd[1]/tbar[0]/btn[0]&qu ot;).press
'SAPSession.findById("wnd[1]/tbar[0]/btn[0]&qu ot;).press
SAPSession.findById("wnd[0]/mbar/menu[0]/menu [3]/menu[2]").Select
SAPSession.findById("wnd[1]/usr/subSUBSCREEN_ STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").Select
SAPSession.findById("wnd[1]/usr/subSUBSCREEN_ STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").SetFocus
SAPSession.findById("wnd[1]/tbar[0]/btn[0]&qu ot;).press
Sheets("FBL5n").Select
Range("A" & lr).Select
ActiveSheet.Paste
If Selection.Columns.Count = 1 Then
Text_2_Columns
End If
lr = ActiveCell.Offset(Selection.Rows.Count).Row
Range("Z" & ActiveCell.Row & ":Z" & lr - 1) = cust
SAPSession.findById("wnd[0]/tbar[0]/btn[3]&qu ot;).press
SAPSession.findById("wnd[0]/tbar[0]/btn[3]&qu ot;).press
'SAPSession.createsession
SAPSession.findById("wnd[0]/tbar[0]/okcd" ;).Text = "f-32"
SAPSession.findById("wnd[0]").sendVKey 0
SAPSession.findById("wnd[0]").maximize
SAPSession.findById("wnd[0]/usr/ctxtRF05A-AGKON").Text = cust
SAPSession.findById("wnd[0]/usr/ctxtBKPF-BUDAT").Text = "30112013"
SAPSession.findById("wnd[0]/usr/txtBKPF-MONAT").Text = ""
SAPSession.findById("wnd[0]/usr/ctxtBKPF-BUKRS").Text = "PBLP"
SAPSession.findById("wnd[0]/usr/ctxtRF05A-AGUMS").Text = "m"
SAPSession.findById("wnd[0]/usr/ctxtRF05A-AGUMS").SetFocus
SAPSession.findById("wnd[0]/usr/ctxtRF05A-AGUMS").caretPosition = 1
SAPSession.findById("wnd[0]/usr/ctxtRF05A-AGKON").caretPosition = 6
SAPSession.findById("wnd[0]").sendVKey 0
If SAPSession.Children.Count = 2 Then
SAPSession.findById("wnd[1]/tbar[0]/btn[0]&qu ot;).press
End If
'Set Table =
SAPSession.findById("wnd[0]/usr/cntlGRID1/she llcont/shell")
amt = 0
v = ActiveCell.Row
stop_row = ActiveCell.Offset(Selection.Rows.Count, 0).Row
'Set Table =
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF=05X:6102/tblSAPDF05XTC_6102/txtDF0 5B-PSBET")
Do While v < stop_row
If Trim(Range("K" & v)) = "Initial Balance Upload as on
30.11.2013" Then
amt = Range("I" & v)
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/txtDF05 B-PSBET[6,1]").SetFocus
'SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/txtRFOP S_DK-BLART[6,1]").SetFocus
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/txtRFOP S_DK-BLART[2,0]").SetFocus
xV = 1
Do
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/txtRF05A-ABPOS").Text
= xV
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/txtRF05A-ABPOS").SetFocus
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/txtRF05A-ABPOS").caretPosition
= 2
SAPSession.findById("wnd[0]").sendVKey 0
pkr =
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/txtDF05 B-PSBET[6,0]").Text
Range("AD" & v) = IIf(Right(pkr, 1) = "-", Left(pkr,
Len(pkr) - 1) * -1, pkr)
pkr = Range("AD" & v)
If Len(amt) = 1 Then
amt = amt * 1
Else
amt = IIf(Right(Trim(amt), 1) = "-",
Left(Trim(amt), Len(Trim(amt)) - 1) * -1, Trim(amt)) * 1
End If
If pkr = amt Then
dz =
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/txtRFOP S_DK-BLART[2,0]").Text
pd1 =
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/ctxtRFO PS_DK-BUDAT[4,0]").Text
pd2 =
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/ctxtRFO PS_DK-BLDAT[5,0]").Text
Else
pkr = 0
Range("AD" & v) = pkr
End If
bal =
Trim(SAPSession.findById("wnd[0]/usr/tabsTS/t abpMA IN/ssubPAGE:SAPDF05X:6102/txtRF05A-NETTO").Text)
* 1
If dz = "DZ" And amt = pkr And pd1 = "30.11.2013" And
pd2 = "30.11.2013" Then
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/txtDF05 B-PSBET[6,0]").SetFocus
SAPSession.findById("wnd[0]").sendVKey 2
Range("AA" & v) = Application.UserName
Range("AB" & v) = "'" & Format(Now, "dd-mmm-yyyy
hh:mm AM/PM")
Range("AC" & v) =
SAPSession.findById("wnd[0]/sbar").Text
Exit Do
End If
xV = xV + 1
Loop
'SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/txtDF05 B-PSBET[6,"
& xV & "]").SetFocus
'SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/txtRFOP S_DK-BLART[2,0]").caretPosition
= 1
'amt =
SAPSession.findById("wnd[0]/usr/tabsTS/tabpMA IN/ssubPAGE:SAPDF05X:6102/tblSAPDF05XTC_6102/txtDF05 B-PSBET[6,0]").Text
End If
bal =
Trim(SAPSession.findById("wnd[0]/usr/tabsTS/t abpMA IN/ssubPAGE:SAPDF05X:6102/txtRF05A-NETTO").Text)
* 1
If bal = 0 Then Exit Do
v = v + 1
Loop
bal =
Trim(SAPSession.findById("wnd[0]/usr/tabsTS/t abpMA IN/ssubPAGE:SAPDF05X:6102/txtRF05A-NETTO").Text)
* 1
If bal = 0 Then
SAPSession.findById("wnd[0]/tbar[0]/btn[11]&q uot;).press ' for
saving
'v = v - 1
Range("AC" & v) = SAPSession.findById("wnd[0]/sbar").Text
Sheets("Cust_list").Range("B" & rw) =
SAPSession.findById("wnd[0]/sbar").Text
If Trim(Range("AC" & v)) = "Enter line items first of all
or choose open items" Or Trim(Range("AC" & v)) = "The difference is too
large for clearing" Or Right(Trim(Range("AC" & v)), 23) = "is flagged for
deletion" Then
SAPSession.findById("wnd[0]/tbar[0]/btn[15]&q uot;).press
SAPSession.findById("wnd[1]/usr/btnSPOP-OPTION1").press
'SAPSession.findById("wnd[0]").maximize
'SAPSession.findById("wnd[0]/tbar[0]/okcd" ;).Text =
"f-32"
'SAPSession.findById("wnd[0]").sendVKey 0
'Exit Do
Else
SAPSession.findById("wnd[0]/tbar[0]/btn[3]&qu ot;).press
SAPSession.findById("wnd[0]/tbar[0]/btn[3]&qu ot;).press
End If
Else
If SAPSession.findById("wnd[0]").Text = "Clear Customer
Process open items" Then
SAPSession.findById("wnd[0]/tbar[0]/btn[15]&q uot;).press
SAPSession.findById("wnd[1]/usr/btnSPOP-OPTION1").press
Sheets("Cust_list").Range("B" & rw) = "ABANDONED Due to
Non-Zero Balance=" & bal
End If
End If
'End If
Else
MsgBox "?"
Stop
End If
Sheets("FBL5N").Select
rw = 1
Do While Sheets("Cust_List").Range("A" & rw) <> Range("Z" & lr - 1)
rw = rw + 1
Loop
If Sheets("Cust_List").Range("B" & rw) <> "" Then
Range("A" & lr).Select
rw = rw + 1
Else
Stop
End If
Sheets("FBL5N").Select
Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Select
Loop
Exit Sub
End If
Exit Sub
Err_Trap:
Range("E" & rx) = 1
Resume Next
End Sub
hope this helps...
Kind regards
Amjad
| | | ---------------Original Message---------------
From: Rodolfo Roberto
Sent: Tuesday, February 18, 2014 5:59 PM
Subject: In F-32 Can We Freeze Screen
Good morning!
I have been on the look-out for just this tool and hope that you can share
with me what you have.
Thank you in advance and warmest regards,
Odi
Rodolfo L. Roberto
SAP FICO Consultant |
| Reply to this email to post your response. __.____._ |   _.____.__ |
