Consulting

Results 1 to 2 of 2

Thread: VBA Script locks the excel sheet

  1. #1

    Post VBA Script locks the excel sheet

    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"


  2. #2
    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"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •