PDA

View Full Version : VBA Script locks the excel sheet



keepintouch
04-20-2018, 08:12 AM
While running the VB script which I'm using in SAP, whenever i run this script the excel sheet gets locked and the error message i get is "unable to write read-only property" and i see the property of excel sheet it says "Read Only". Please help where I'm going wrong. Thanks in advance.


If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject application, "on"
End If
session.findById("wnd[0]").maximize
Dim objExcel, objWorkbook, objSheet, i
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\TSK\Business_Area5.xlsx")
Set objSheet = objWorkbook.Sheets("Sheet1")
For i = 2 to objSheet.UsedRange.Rows.Count


Business_Area = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
Name = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2


session.findById("wnd[0]/tbar[0]/okcd").text = "ox03"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[5]").press
session.findById("wnd[0]/usr/tblSAPL0ORGCORETCTRL_V_TGSB/txtV_TGSB-GSBER[0,0]").text = Business_Area
session.findById("wnd[0]/usr/tblSAPL0ORGCORETCTRL_V_TGSB/txtV_TGSB-GTEXT[0,0]").text = Name
session.findById("wnd[0]/usr/tblSAPL0ORGCORETCTRL_V_TGSB/txtV_TGSB-GTEXT[0,0]").setFocus
session.findById("wnd[0]/usr/tblSAPL0ORGCORETCTRL_V_TGSB/txtV_TGSB-GTEXT[0,0]").caretPosition = 12
session.findById("wnd[0]/tbar[0]/btn[11]").press
next
msgbox "Created Business Areas"

keepintouch
04-20-2018, 08:17 AM
The irony is the below code doesn't lock the excel when running the VB script but the above one does.


If Not IsObject(application) Then Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject application, "on"
End If
session.findById("wnd[0]").maximize


Dim objExcel, objWorkbook, objSheet, i
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\TSK\User_Reset.xlsx")
Set objSheet = objWorkbook.Sheets("Sheet1")
For i = 2 to objSheet.UsedRange.Rows.Count


USERID = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
PWD = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2


session.findById("wnd[0]/tbar[0]/okcd").text = "su01"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtUSR02-BNAME").text = USERID
session.findById("wnd[0]/usr/ctxtUSR02-BNAME").caretPosition = 4
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[20]").press
session.findById("wnd[1]/usr/pwdG_PASSWORD1").text = PWD
session.findById("wnd[1]/usr/pwdG_PASSWORD2").text = PWD
session.findById("wnd[1]/usr/pwdG_PASSWORD2").setFocus
session.findById("wnd[1]/usr/pwdG_PASSWORD2").caretPosition = 11
session.findById("wnd[1]/tbar[0]/btn[0]").press


next


msgbox "Password reset completed"