Tackle the 'Not responding application outside of Microsoft Access' error in the calling Access VBA

Asked 21/9, 2021 at 10:21 Answered 22/9, 2021 at 16:11

I am using the ScriptControl in Access VBA to load the scripts (.vbs files) and execute them for extracting data from a SAP system. For the small data the code works fine.

However, when there is a big data which takes time or stops responding then Access opens a popup window asking me to switch to the app or retry. If I click on retry button or by hand switch to that window, then the script resumes!

Is there any way to tackle this access popup window or a code to press this retry button? Thanks

Mycode:

Open scriptPath For Input As #1
    vbsCode = Input$(LOF(1), 1)
    Close #1
    
    On Error GoTo ERR_VBS
    
    With CreateObject("ScriptControl")
        .Language = "VBScript"
        .AddCode vbsCode    '>>>>>>>>>>>>>>>> I get this popup window at this line

    End With

Tried :

Sub Test()
    
    Dim oSC As Object
    
    Set oSC = CreateObjectx86("ScriptControl") ' create ActiveX via x86 mshta host
    Debug.Print TypeName(oSC) ' ScriptControl
    ' do some stuff
    
    CreateObjectx86 Empty ' close mshta host window at the end
    
End Sub

Function CreateObjectx86(sProgID)
   
    Static oWnd As Object
    Dim bRunning As Boolean
    Dim vbsCode As String, result As Variant, Script As Object
    
    Open "\My Documents\\Desktop\x.vbs" For Input As #1
    vbsCode = Input$(LOF(1), 1)
    Close #1
    
   
            Set oWnd = CreateWindow()
            oWnd.execScript vbsCode, "VBScript"  '>>>>>>>>>Gets an Error says "Error on Script page"
            Set CreateObjectx86 = oWnd.CreateObjectx86(sProgID)
   
    
End Function

Function CreateWindow()

    ' source http://forum.script-coding.com/viewtopic.php?pid=75356#p75356
    Dim sSignature, oShellWnd, oProc
    
    On Error Resume Next
    Do Until Len(sSignature) = 32
        sSignature = sSignature & Hex(Int(Rnd * 16))
    Loop
    CreateObject("WScript.Shell").Run "%systemroot%\syswow64\mshta.exe about:""<head><script>moveTo(-32000,-32000);document.title='x86Host'</script><hta:application showintaskbar=no /><object id='shell' classid='clsid:8856F961-340A-11D0-A96B-00C04FD705A2'><param name=RegisterAsBrowser value=1></object><script>shell.putproperty('" & sSignature & "',document.parentWindow);</script></head>""", 0, False
    Do
        For Each oShellWnd In CreateObject("Shell.Application").Windows
            Set CreateWindow = oShellWnd.GetProperty(sSignature)
            If Err.Number = 0 Then Exit Function
            Err.Clear
        Next
    Loop
    
End Function

Alcaeus answered 21/9, 2021 at 10:21 Comment(7)

Related, you may find it useful - Getting ScriptControl to work with Excel 2010 x64 – Comeaux 21/9, 2021 at 11:32

I don't have 64bit system so I can't set this line systemroot%\syswow64\mshta.exe to create a window. I am using 32bit system. So how can I achieve this? Thanks – Alcaeus 21/9, 2021 at 13:26

If you're not on a x64 system just use %systemroot%\system32\mshta. – Comeaux 21/9, 2021 at 13:55

I tried the code from the reference and modified to use it as seen in the Edited Question above. But I get an error at oWnd.execScript vbsCode, "VBScript" '>>>>>>>>>Gets an Error says "Error on Script page" – Alcaeus 21/9, 2021 at 16:38

Sounds like you have an error in the VBScript file you are trying to execute. – Comeaux 21/9, 2021 at 17:53

No there is no error in the script. I also tried the simple script like Msgbox "Hello World" – Alcaeus 21/9, 2021 at 20:26

Not sure ScriptControl is necessary here at all. I would just save VBS code to file and execute it via shell - for the sake of simplicity, then read results saved to another file or from stdout. – Cordell 6/11, 2022 at 11:10

So after lot of headache, I found the solution! The solution is to use waitToReturn. This will make Access VBA wait for the Script to be completed no matter how long it take! Hence, this tackled the problem of Access popup window asking to switch to window or Retry!

Solution code:

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1 
Dim errorCode As Integer

errorCode = wsh.Run("C:\path\x.vbs", windowStyle, waitOnReturn)

If errorCode = 0 Then
    MsgBox "Script successful. "
Else
    MsgBox "Script exited with error code " & errorCode & "."
End If

Alcaeus answered 22/9, 2021 at 16:11 Comment(0)

-2

with cases like this you would always try to get the focus via the object you are manipulating, usually it is done by .setFocus or .active. the below is code that will help you out. I would try the session.setFocus.

Session.ActiveWindow.SetFocus

the below code will also help:
    Dim SapGuiAuto As Object
Dim Application As SAPFEWSELib.GuiApplication
Dim Connection As SAPFEWSELib.GuiConnection
Dim Session As SAPFEWSELib.GuiSession
Dim UserArea As SAPFEWSELib.GuiUserArea
' Dim oWindow As SAPFEWSELib.GuiConnection
Dim oUserAreaOfMobileWindow  As SAPFEWSELib.GuiUserArea
Dim oGuiSimpleContainer As SAPFEWSELib.GuiSimpleContainer

Set SapGuiAuto = GetObject("SAPGUI")

If Not IsObject(SapGuiAuto) Then
    Exit Sub
End If

Set Application = SapGuiAuto.GetScriptingEngine()

If Not IsObject(Application) Then
    Exit Sub
End If

Set Connection = Application.Connections(0)

If Not IsObject(Connection) Then
    Exit Sub
End If

Set Session = Connection.Sessions(0)
If Not IsObject(Session) Then
    Exit Sub
End If

Lenlena answered 21/9, 2021 at 15:6 Comment(3)

I tried the code to set the focus on Active window Session.ActiveWindow.SetFocus in .VBS as well the code below is already in every .VBS files to create a session and begin the transaction. I also tried Session.ActiveWindow.SetFocus in the calling VBA (Access) but did not work! – Alcaeus 21/9, 2021 at 15:40

you can try to sleep the app for a few seconds. However you will have to know where to place it (for example, in the section after the vbs call. the below should give you the sleep: – Lenlena 22/9, 2021 at 11:40

' Sleep 100 'Implements a 15th-of-a-Second delay, Sleep 1000, Implements a 1 second delay #If VBA7 And Win64 Then Declare PtrSafe Sub Sleep Lib "kernel32" _ (ByVal dwMilliseconds As LongPtr) #Else Declare Sub Sleep Lib "kernel32" _ (ByVal dwMilliseconds As Long) #End If – Lenlena 22/9, 2021 at 11:41

Recommended topics

Hot tags