VB script hangs Excel since upgrading to Windows 7

[markhamlyn]

SAP version: 720

OS:  Windows 7

MS Office version: Office Professional Plus 2010

We have recently upgraded to Windows 7, and while my Excel VB script continues to work just fine, it hangs at the end of executing and I have to use windows task manager to shutdown Excel.  My guess is that it is some how failing to close the connection to SAP (though it SAP doesn't appear in the list of running programs).  As I said, it connects to SAP and runs the script just fine, but at the end, Excel is frozen up with the little spinning "I'm thinking" wheel.

Sub SAP_test()

    If Not IsObject(SAPguiApp) Then
        Set SAPguiApp = CreateObject("Sapgui.ScriptingCtrl.1")
    End If
    If Not IsObject(Connection) Then
        Set Connection = SAPguiApp.OpenConnection("<description of SAP connection>", True)
    End If
    If Not IsObject(session) Then
        Set session = Connection.Children(0)
    End If
   
   
    session.findById("wnd[0]").maximize
    session.findById("wnd[0]/tbar[0]/okcd").Text = "<t-code I wish to execute>"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/tbar[1]/btn[17]").press
    session.findById("wnd[1]/usr/txtV-LOW").Text = "<name of variant I wish to execute>"
    session.findById("wnd[1]/usr/txtENAME-LOW").Text = "<creator of the variant I wish to exectute>"
    session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 11
    session.findById("wnd[1]/tbar[0]/btn[8]").press
    session.findById("wnd[0]/usr/tabsTABSTRIP_TABBLOCK1/tabpS_TAB4").Select
    session.findById("wnd[0]/usr/tabsTABSTRIP_TABBLOCK1/tabpS_TAB4/ssub%_SUBSCREEN_TABBLOCK1:RI_ORDER_OPERATION_LIST:1400/ctxtS_ZZSCHD-LOW").Text = <string for start date>
    session.findById("wnd[0]/usr/tabsTABSTRIP_TABBLOCK1/tabpS_TAB4/ssub%_SUBSCREEN_TABBLOCK1:RI_ORDER_OPERATION_LIST:1400/ctxtS_ZZSCHD-HIGH").Text = <string for end date>
    session.findById("wnd[0]/usr/tabsTABSTRIP_TABBLOCK1/tabpS_TAB4/ssub%_SUBSCREEN_TABBLOCK1:RI_ORDER_OPERATION_LIST:1400/ctxtS_ZZSCHD-HIGH").SetFocus
    session.findById("wnd[0]/usr/tabsTABSTRIP_TABBLOCK1/tabpS_TAB4/ssub%_SUBSCREEN_TABBLOCK1:RI_ORDER_OPERATION_LIST:1400/ctxtS_ZZSCHD-HIGH").caretPosition = 10
    session.findById("wnd[0]/tbar[1]/btn[8]").press
    session.findById("wnd[0]/mbar/menu[0]/menu[6]").Select
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").Select
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[1]/tbar[0]/btn[0]").press

End Sub

Any help greatly appreciated, it's a pain having to close Excel every time I run a script and having to incude a line in the code to make it save before attemtping to exit SAP, just so I don't lose all the data every time.

Thanks,

Mark

[former_member213011]

Dear Mark,

Have you tried setting all the SAP Objects to nothing? i.e. :

Set SapGuiApp = Nothing

Set Connection = Nothing

Set Session  = Nothing

[markhamlyn]

Hello Sayuti,

A ha!  I tried it and it didn't work, but THEN I tried it removing one "button click" within the script and....SUCCESS!  it appears to have resolved the issue.   I shall test it a little more, but I think you have resolved my issue.  Thank you very much!

[former_member213011]

Hi Mark,

When it didn't work before you remove the 'button click', did VBA throw any error or simply hang?