Consulting

Results 1 to 3 of 3

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"

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,257
    Location
    Sorry, old thread but it was placed into the wrong subforum hence never got a look at by anybody. Anyone care to have a crack at this?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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