Code Works On Windows 10 Machine But Not Windows 8.1:

[MattDW]

I have been writing some VBA code the past two weeks to automatically fill out a web ticket for work. The code I developed was done using a personal laptop that is running Windows 10 operating system.

I tried running this on my work machine (our organization runs Windows 8.1) and I discovered my code does not run correctly on this machine.

Both systems are running Excel 2016 and both utilize Internet Explorer 11 (which is a requirement). The Windows 10 x64 operating system's Internet Explorer is version 11.576.14393.0 while the Windows 8.1 x64 Internet Explorer version is 11.09600.17690. I've checked on my Windows 8.1 machines and there are no available IE updates available (the version I have under Windows 8.1 is the most recent version available). Our organization isn't ready to move to Windows 10 yet, so it is also not an option, at this time, to upgrade the operating system.

I wasn't aware when I was writing the code that there would be elements that might not run correctly using the slightly older version of Internet Explorer and I wasn't able to beta test on my work machine until today.

I've run the code multiple times on my personal laptop and the code itself is otherwise executing the way that it is supposed to (at least in the Windows 10 version of Internet Explorer).

When I step through the code I notice the IE.left/top/width/height don't execute correctly and also that the code seems to get stuck in an infinite loop at this portion of the code:

    While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Wend

It's all together possible some of my other lines of code may not be compatible with the lower version of Internet Explorer either.

Could one of the resident Excel gurus here tell me what the difference in browser versions makes and if there are easy ways I can modify my code to work in the Internet Explorer version running under Windows 8.1? Especially at the sticky point mentioned above?

Thank you so much!

'Automate The Creation Of The Web Help Desk Ticket

'Add reference to Microsoft Internet Controls (SHDocVw)
'Add reference to Microsoft HTML Object Library
        
Sub AutomateHelpDeskTicket()
        
    Dim IE As SHDocVw.InternetExplorer
    Dim doc As MSHTML.HTMLDocument
    Dim div
    Dim url As String
    Dim hwnd As Long, IECaption As String
    Dim Tags As Object
    Dim Tagx As Object
    Dim myElem As Object
    Dim WaitOver
    Dim LastName As Object
    Dim Count
    Dim l
    Dim Path As String
    Dim RequestID As String
    Dim StrRecordNumber As String
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document

    On Error Resume Next
    
GoToWebsite:

    'URL For The Ticketing System
    url = "https://help.psdschools.org/helpdesk/WebObjects/Helpdesk.woa"
    
    'Create InternetExplorer Object
    Set IE = New SHDocVw.InternetExplorer
    
    'Make Internet Explorer Visible
    IE.Visible = True
    
    'Go to UR Specified Above
    IE.Navigate url
    
    'Set Internet Explorer Window to 1024x768 Resolution With Position Being In The Top-Left Corner
    IE.Left = 0
    IE.Top = 0
    IE.Width = 1024
    IE.Height = 768
    
    'Wait Until Internet Explorer Is Not Busy
    While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
    
WaitForLogin:
    
    'Look At Only Element Types Of Input
    Set Tags = IE.Document.getElementsByTagName("input")
    
    'If Webpage Contains Textbox Named userName Then Loop Until Webpage No Longer Contains That Textbox
    For Each Tagx In Tags
        If Tagx.getAttribute("id") = "userName" Then
            Set myElem = IE.Document.getElementById("userName")
        End If
        If myElem Is Nothing Then
            GoTo ClickClientInfoButton
        Else
            Set myElem = Nothing
            GoTo WaitForLogin
        End If
    Next

    Exit Sub

ClickClientInfoButton:

    'Look At Only Element Types Of DIV
    Set Tags = IE.Document.getElementsByTagName("div")

    'If The DIV Button Is Class Named squareButtonLeft, I.E., "New Ticket," Then Click It
    For Each Tagx In Tags
        If Tagx.getAttribute("class") = "squareButtonLeft" Then
            Tagx.Click
            GoTo EnterClientInfo
        End If
    Next

    Exit Sub

EnterClientInfo:

    'Wait Until Internet Explorer Is Not Busy
    While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
    
    'Fill TextBox With Name lastNameField with Twogood
    IE.Document.getElementsByName("lastNameField")(0).Value = "Twogood"

    'Look At Only Element Types Of DIV
    Set Tags = IE.Document.getElementsByTagName("div")
    
    'If The DIV Button Is The Third Class Named panelButton, I.E., Button Named "Search," Then Click It
    Count = 0
    For Each Tagx In Tags
        If Tagx.getAttribute("class") = "panelButton" Then
            Count = Count + 1
            If Count = "3" Then
                Tagx.Click
                GoTo ClickAssetInfoButton
            End If
        End If
    Next
    
    Exit Sub

ClickAssetInfoButton:

    'Click On Asset Info Button
    Dim CurrentWindow As HTMLWindowProxy: Set CurrentWindow = IE.Document.parentWindow
    Call CurrentWindow.execScript("*******_e_7_21_0_0_0_0_2_3_0_0_1_2_0_3_0_1_5_5_0_1_1_1_1_1_1_1_0_3_1()")
    
    'Wait Until Internet Explorer Is Not Busy
    While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
    
    'Wait Additional 1 Second
    Application.Wait Now + TimeSerial(0, 0, 1)
    
EnterAssetInfo:
    
    'Fill TextBox With Name lastNameField With Computer Name From Column B Of The Selected Row
    IE.Document.getElementsByName("7.21.0.0.0.0.2.3.0.0.1.2.0.3.0.1.5.5.0.1.3.1.5.0.0.1.2.9.3.1.9.2.1.3.1.0.1.1.1.30.1.30.1.4.1")(0).Value = "" & Range("B" & ActiveCell.Row).Value & ""

    'Look At Only Element Types Of DIV
    Set Tags = IE.Document.getElementsByTagName("div")
    
    'If The DIV Button Is The Third Class Named panelButton, I.E., The Button Named "Search," Then Click It
    'Zero Out Counter
    Count = 0
    For Each Tagx In Tags
        If Tagx.getAttribute("class") = "panelButton" Then
            'Increment Counter By One Each Loop
            Count = Count + 1
            If Count = "3" Then
                'Click On DIV Button
                Tagx.Click
                GoTo ClickAssetHypeerlink
            End If
        End If
    Next

    Exit Sub

ClickAssetHypeerlink:
    
    'Look At Only Element Types Of DIV
    Set Tags = IE.Document.getElementsByTagName("div")
    
    'Click The Asset ID Hyperlink To Attach This To The Ticket
    For Each Tagx In Tags
        'Identify DIV Container By ID Attribute
        If Tagx.getAttribute("id") = "AssetSearchResultsDiv" Then
            'Get Path From upDateURL Attribute In DIV Container
            Path = Tagx.getAttribute("updateURL")
            'Use Srring Manipulation To Get Only The RequestID Number From Path
            RequestID = Right(Path, Len(Path) - Application.WorksheetFunction.Find("ajax/", Path) - 4)
            'Add RequestID To Below JavaScript Call To Click On Asset ID Hyperlink To Attach It To The Ticket
            Call CurrentWindow.execScript("AUL.update('AssignedAssetsUpdateContainer', {}, '" & RequestID & ".1.19.0.1.3.1');")
            GoTo ClickTicketDetailsButton
        End If
    Next
    
    Exit Sub
    
ClickTicketDetailsButton:

    'Click On Asset Info Button
    Call CurrentWindow.execScript("*******_e_7_21_0_0_0_0_2_3_0_0_1_2_0_3_0_1_5_5_0_1_1_1_2_1_1_1_0_3_1()")
    
    'Wait Until Internet Explorer Is Not Busy
    While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
    
    'Wait One Additional 1 Second
    Application.Wait Now + TimeSerial(0, 0, 1)

SelectRequestTypeParent:

    'Wait Until Internet Explorer Is Not Busy
    While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
    
    'Indentify Request Type Parent Select Tag By Count (This Select Drop-Down Is The Third Select Tag On The Page)
    'I Couldn't Identify This Select Box By Name or ID Atrribute As These Values Change To Some Sudo-Random Value Each Refresh Of The Page
    'Zero Out Counter
    Count = 0
    For Each l In IE.Document.getElementsByTagName("select")
        'Increment Counter By One Each Loop
        Count = Count + 1
            'If The Select Tag Is The Third Tag Of It's Kind On The Page
            If Count = "3" Then
                'Get Focus On That Select Tag
                l.Focus
                'Set The Value Of That Select Tag To The Value Above The One Needed, I.E., To "Classroom Technology" (Item 3)
                l.selectedIndex = 3
                'Get Focus On That Select Tag
                l.Focus
                'Send The Down Arrow Key To The Select Object In Order To Change It To "Computer" (Item 4)
                'Because There Is A Yet Unidentified OnChange Event or JavaScript Code That Shows The Next Select Box When The Select Box Has Been Changed, I Couldn't Simply Assign The Value To This Select Box.  Nor Have I Yet Identified The Script That Activates Showing The Next Select Tag, In Order To Cell It Directly. Thus, Using This Round About Method, Which Gets The Desired Result.
                SendKeys "{down}"
            GoTo SelectRequestTypeSub1
            End If
    Next
    
    Exit Sub

SelectRequestTypeSub1:

    'Wait Until Internet Explorer Is Not Busy
    While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Wend

    'Wait Additional 1 Second
    Application.Wait Now + TimeSerial(0, 0, 1)
    
    'Indentify Request Type Sub Select Tag By Count (This Select Drop-Down Is The Third Select Tag On The Page)
    'I Couldn't Identify This Select Box By Name or ID Atrribute As These Values Change To Some Sudo-Random Value Each Refresh Of The Page
    'Zero Out Counter
    Count = 0
    For Each l In IE.Document.getElementsByTagName("select")
        'Increment Counter By One Each Loop
        Count = Count + 1
            'If The Select Tag Is The Fourth Tag Of It's Kind On The Page
            If Count = "4" Then
                'Get Focus On That Select Tag
                l.Focus
                'Set The Value Of That Select Tag To The Value Above The One Needed, I.E., To "Functionality Issue" (Item 1)
                l.selectedIndex = 1
                'Get Focus On That Select Tag
                l.Focus
                'Send The Down Arrow Key To The Select Object In Order To Change It To "Hardware/Repair Failure" (Item 2)
                'Because There Is A Yet Unidentified OnChange Event or JavaScript Code That Shows The Next Select Box When The Select Box Has Been Changed, I Couldn't Simply Assign The Value To This Select Box.  Nor Have I Yet Identified The Script That Activates Showing The Next Select Tag, In Order To Cell It Directly. Thus, Using This Round About Method, Which Gets The Desired Result.
                SendKeys "{down}"
            GoTo SelectRequestTypeSub2
            End If
    Next

    Exit Sub
    
SelectRequestTypeSub2:

    'Wait Until Internet Explorer Is Not Busy
    While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Wend

    'Wait Additional 1 Second
    Application.Wait Now + TimeSerial(0, 0, 1)

    'Indentify Request Type Sub Select Tag By Count (This Select Drop-Down Is The Third Select Tag On The Page)
    'I Couldn't Identify This Select Box By Name or ID Atrribute As These Values Change To Some Sudo-Random Value Each Refresh Of The Page
    'Zero Out Counter
    Count = 0
    For Each l In IE.Document.getElementsByTagName("select")
        'Increment Counter By One Each Loop
        Count = Count + 1
            'If The Select Tag Is The Fifth Tag Of It's Kind On The Page
            If Count = "5" Then
                'Get Focus On That Select Tag
                l.Focus
                'Set The Value Of That Select Tag To The Value Above The One Needed, I.E., To "Desktop" (Item 1)
                l.selectedIndex = 1
                'Get Focus On That Select Tag
                l.Focus
                'Send The Down Arrow Key To The Select Object In Order To Change It To "Laptop" (Item 2)
                'Because There Is A Yet Unidentified OnChange Event or JavaScript Code That Shows The Next Select Box When The Select Box Has Been Changed, I Couldn't Simply Assign The Value To This Select Box.  Nor Have I Yet Identified The Script That Activates Showing The Next Select Tag, In Order To Cell It Directly. Thus, Using This Round About Method, Which Gets The Desired Result.
                SendKeys "{down}"
            GoTo FillSubjectLine
            End If
    Next
    
    Exit Sub
    
FillSubjectLine:

    'Fill TextBox With ID Subject with Hardware Repair Item:
    IE.Document.getElementsByName("7.21.0.0.0.0.2.3.0.0.1.2.0.3.0.1.5.5.0.1.3.1.1.0.0.1.16.11.2.1.3.31.1.3.1.3")(0).Value = "Hardware Repair Item:"
    
FillRequestDetail:

    'Add Clipboard Contents To Variable
    Dim DataObj As MSForms.DataObject
    Dim strPaste As String
    
    Set DataObj = New MSForms.DataObject
    DataObj.GetFromClipboard
    strPaste = DataObj.GetText(1)

    'Fill TextBox With Name lastNameField With The Contents Of The Clipboard
    IE.Document.getElementsByName("7.21.0.0.0.0.2.3.0.0.1.2.0.3.0.1.5.5.0.1.3.1.1.0.0.1.16.11.2.1.3.33.1.3.1.3.1.5")(0).Value = strPaste
    
SelectStatus:

    'Wait Until Internet Explorer Is Not Busy
    While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Wend

    'Wait Additional 1 Second
    Application.Wait Now + TimeSerial(0, 0, 1)

    'Indentify Request Type Sub Select Tag By Count (This Select Drop-Down Is The Third Select Tag On The Page)
    'I Couldn't Identify This Select Box By Name or ID Atrribute As These Values Change To Some Sudo-Random Value Each Refresh Of The Page
    'Zero Out Counter
    Count = 0
    For Each l In IE.Document.getElementsByTagName("select")
        'Increment Counter By One Each Loop
        Count = Count + 1
            'If The Select Tag Is The Fifth Tag Of It's Kind On The Page
            If Count = "6" Then
                'Get Focus On That Select Tag
                l.Focus
                'Set The Value Of That Select Tag To The Value Above The One Needed, I.E., To "Closed" (Item 2)
                l.selectedIndex = 2
                'Get Focus On That Select Tag
                l.Focus
                'Send The Down Arrow Key To The Select Object In Order To Change It To "Equipment to Repair" (Item 3)
                'Because There Is A Yet Unidentified OnChange Event or JavaScript Code That Shows The Next Select Box When The Select Box Has Been Changed, I Couldn't Simply Assign The Value To This Select Box.  Nor Have I Yet Identified The Script That Activates Showing The Next Select Tag, In Order To Cell It Directly. Thus, Using This Round About Method, Which Gets The Desired Result.
                SendKeys "{down}"
                GoTo SaveTicket
            End If
    Next
    
    Exit Sub
    
SaveTicket:

    'Look At Only Element Types Of DIV
    Set Tags = IE.Document.getElementsByTagName("div")
    
    'If The DIV Button Is The Second Class Named aquaMiddle, I.E., Button Named "Save," Then Click It
    Count = 0
    For Each Tagx In Tags
        If Tagx.getAttribute("class") = "aquaMiddle" Then
            Count = Count + 1
            If Count = "2" Then
                Tagx.Click
                GoTo EscalateTicket
            End If
        End If
    Next
    
    Exit Sub

EscalateTicket:

    'Look At Only Element Types Of DIV
    Set Tags = IE.Document.getElementsByTagName("div")
    
    'Click The Escalate Button Twice To Escalate This Ticket To IT Electronic Repair Services Level 3
    For Each Tagx In Tags
        'Identify DIV Container By ID Attribute
        If Tagx.getAttribute("id") = "DetailsPanelDiv" Then
            'Get Path From upDateURL Attribute In DIV Container
            Path = Tagx.getAttribute("updateURL")
            'Use Srring Manipulation To Get Only The RequestID Number From Path
            RequestID = Right(Path, Len(Path) - Application.WorksheetFunction.Find("ajax/", Path) - 4)
            'Add RequestID To Below JavaScript Call To Click On Asset ID Hyperlink To Attach It To The Ticket
            Call CurrentWindow.execScript("AUL.update('DetailsPanelDiv', {onComplete:function(e){ EmailRecipientsContainerUpdate();DialogMessageUpdateContainerUpdate() }}, '" & RequestID & ".3.17.17.0.1.1');")
            Call CurrentWindow.execScript("AUL.update('DetailsPanelDiv', {onComplete:function(e){ EmailRecipientsContainerUpdate();DialogMessageUpdateContainerUpdate() }}, '" & RequestID & ".3.17.17.0.1.1');")
            GoTo SaveAndEmailTicket
        End If
    Next
    
    Exit Sub
    
SaveAndEmailTicket:

    'Look At Only Element Types Of DIV
    Set Tags = IE.Document.getElementsByTagName("div")
    
    'If The DIV Button Is The Second Class Named aquaMiddleSel, I.E., Button Named "Save," Then Click It
    For Each Tagx In Tags
        If Tagx.getAttribute("class") = "aquaMiddleSel" Then
            Tagx.Click
            GoTo PrintTicketPreview
        End If
    Next
    
    Exit Sub
    
PrintTicketPreview:

    'Look At Only Element Types Of DIV
    Set Tags = IE.Document.getElementsByTagName("form")
    
    'Click The Escalate Button Twice To Escalate This Ticket To IT Electronic Repair Services Level 3
    For Each Tagx In Tags
        'Identify DIV Container By ID Attribute
        If Tagx.getAttribute("name") = "ticketForm" Then
            'Get Path From upDateURL Attribute In DIV Container
            Path = Tagx.getAttribute("action")
            'Use Srring Manipulation To Get Only The RequestID Number From Path
            RequestID = Right(Path, Len(Path) - Application.WorksheetFunction.Find("wo/", Path) - 2)
            'Add RequestID To Below JavaScript Call To Click On Asset ID Hyperlink To Attach It To The Ticket
            Call CurrentWindow.execScript("javascript:************('/helpdesk/WebObjects/Helpdesk.woa/wo/" & RequestID & ".1.1.6.1','printView','toolbar=no,location=no,status=no,menubar=no,resizable=yes,scrollbars=yes,top=50,left=50,width=700,height=700')")
            GoTo PrintTicket
        End If
    Next
    
    Exit Sub
    
PrintTicket:

    'Send Ticket To Default Printer
    IE.ExecWB 6, 2
    
End Sub

 

[MattDW]

P.S., I don't think this makes any difference to the problem at hand, at least I don't suspect the web browser form interface lines of code reference anything from the Microsoft Word library (as they do the Microsoft Internet Controls (SHDocVw)
and the Microsoft HTML Object Library libraries) - but I'm not 100% sure and so I'll mention it here, just in case.

I noticed that Excel 2016 on Windows 10 utilizes the reference library of Microsoft Word 16.0 and on my Windows 8.1 machine only has the library of Microsoft Word 15.0. When I copy, from my Windows 10 machine, and launch code on my Windows 8.1 machine Excel complains about a missing library. I can fix that error by unchecking the missing Microsoft Word 16.0 library and rechecking the Microsoft Word 15.0 library. This causes the error message about the missing library, at least, to go away.

Thanks much,

 

[John_w]

For the IE wait loop, try just IE.Busy, or just IE.ReadyState.

For the IE Left/Top/Width/Height, what do you mean by "don't execute properly"?

 

[MattDW]

Thank you for your response, John. I really appreciate it.

Unfortunately, using just IE.Busy, or IE.ReadyState still results in an infinite loop that VBA never escapes when using the Windows 8.1 version of Internet Explorer. I commented out this section of code, so that I could test the rest of my code, and the bigger problem is that none of the other lines of code that are supposed to interact with the web browser are functioning in this version of the web browser either. So every time a line of code is supposed to click on a button or to change a select box, the line of code doesn't produce an error, it simply passes with no effect within the web browser. This is also what I meant (sorry I didn't explain that better) when I mentioned that the .left, .top, .width and .height weren't working. I can run the code or I can step through the code without any errors, but the web browser window position doesn't change, at least not on my Windows 8.1 machines. It works perfectly using the Internet Explorer version on my Windows 10 machine.

This makes me wonder if the Set IE = New SHDocVw.InternetExplorer line of code that establishes the connection between VBA and the web browser is maybe version specific? Or am I missing something else, bigger, more obvious and/or simpler than that? I have checked and I have the same references checked in both Excel versions and I have made copies of the Excel spreadsheet on both computers so that I can run the same spreadsheet/code in both scenarios. This works perfectly on my Windows 10 machine but not on my Windows 8.1 machine.

Also, if I do need to change the New SHDocVw.InternetExplorer, what other portions of my code would need to be changed as a result of this? I started experimenting with the concept of using VBA to trigger events in a web browser a few weeks back on my work Windows 8.1 machine and I started out with a simple example I found that explained how to enter text into a Google search box and to submit this by trigging the click of the Search button. However, when I tried to use that same Set IE line of code (which was not Set IE = New SHDocVw.InternetExplorer, in that case) I ended up getting Access Denied error messages when trying to apply that to this form, so I ended up researching to try and find a different way to make that connection, which lead me finally to the SHDocVw.InternetExplorer line, which worked when I tested it on my personal laptop.

Anyone who might have an idea what the differences are or if there is a an easy way to modify my code to work under the Windows 8.1 version of Internet Explorer or even both versions, please let me know. I'd hate to have to scrap what I've done and start over again (and at this point I'm not even sure what is needed to get this to work on our Windows 8.1 machines). I also am finding it frustrating as going by the IE version numbers, it appears to be very similar versions of IE (both are version 11, one is 11.0 and one is 11.5), so I would think that this should work -- even though it's evident to me that it doesn't.

Thanks much for any and all help!

 

[John_w]

I don't know of any differences between IE 11.0 and 11.5. (There are differences between IE8 and higher versions which affect VBA IE automation.)

Instead of the InternetExplorer object, try InternetExplorerMedium, which uses a different security level:

    Dim IE As SHDocVw.InternetExplorerMedium
    Set IE = New SHDocVw.InternetExplorerMedium

 

[MattDW]

The security level seems to have been a huge part of the problem, John, and changing this to InternetExplorerMedium helped a great deal. The code is still getting stuck at points half way through its run (its now getting hung up at the asset tag section) but with this change its getting to a point it wasn't able to get to before. I'm going to continue to step through the code to see if I can figure out why it's hanging up.

Thanks much for your help with this!

All the best,

 

[MattDW]

John, I wanted to let you know that your suggestion worked. I stepped through my code and I found that on my work laptop I had to add a few 1 second pauses in order for the form to fill correctly. It appears the code was reaching that step in the code prior to the page fully having loaded and sometimes it would fill correctly and sometimes not. It appears everything I coded previously is otherwise working the way it does on my home laptop.

Thanks again for your help with this!