VBA HTML not running on all computers

Question

So I have this code I'm working on for my deptarment that goes to a website inputs data , clicks run and downloads the csv file to the worksheet. It works just fine on my PC and on my profile on the computers the other department uses. We are both using same versions of windows, excel , and IE. When i have someone from the other department run the macro it opens the website but never enters the data into the fields despite the site being the exact same coding as when i'm logged in.

Sub testmetric()



Dim appIE As Object
Dim Obj As Object

On Error Resume Next

Sheets("Audit").Select

Selection.AutoFilter

Sheets("Audit").Cells.Clear



Application.ScreenUpdating = False

Application.ScreenUpdating = True
Set appIE = CreateObject("InternetExplorer.Application")


sURL = "http://cctools/rportal/main.php?p=agentavaya"

' Instructes the macro to open IE and navigate to sURL.
With appIE
    .Navigate sURL
    .Visible = True

    Application.Wait Now + TimeValue("00:00:02")
     Set HTMLDOC = .Document
End With



Application.Wait Now + TimeValue("00:00:02")


appIE.Document.getElementById("agentLob").selectedIndex = "3"

appIE.Document.getElementById("timezone").selectedIndex = "1"




appIE.Document.getElementById("sdate").Value = Range("Date_Start")


   appIE.Document.getElementById("edate").Value = Range("Date_End")





   appIE.Document.getElementById("stenure").Value = Range("TenStart")

   appIE.Document.getElementById("etenure").Value = Range("TenEnd")





Application.Wait Now + TimeValue("00:00:02")

For Each Btninput In appIE.Document.getElementsByTagName("INPUT")
    If Btninput.Value = " Run " Then
        Btninput.Click
        Exit For
    End If
     Next



     Application.Wait Now + TimeValue("00:00:02")

Call CSV


appIE.Quit

Sheets("Audit").Select
Range("A1").Select
Sheets("audit").Paste



  End Sub

Sub CSV()
sCSVLink = "http://cctools/rportal/csvexport.php"
sfile = "csvexport.php"
ssheet = "Sheet10"

Set wnd = ActiveWindow

Application.ScreenUpdating = False


Workbooks.Open Filename:=sCSVLink

Windows(sfile).Activate
ActiveSheet.Cells.Copy
wnd.Activate
Range("A1").Select
Sheets("Audit").Paste
Application.DisplayAlerts = False
Windows(sfile).Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Sheets("Audit").Select
Range("A1").Select
Sheets("audit").Paste


End Sub

When this code is ran by a member of the other department it just opens the website inputs nothing and doesn't press the RUN button on the website.

Any ideas on what could be causing this? What setting or anything. I verified that both PC's VBA references in are there and no "Locations are missing paths" .

Answer 1

You are never checking to see if the web page has finished loading!

Your On Error Resume Next statement is also hiding any errors and preventing you from taking the appropriate remedial action by fixing the code. Please report back with the specific error number and line which raises the error. I have an idea which line and which error, and my answer is intended for that:

Type 91 error on line: Set HTMLDOC = .Document

Why?

While you are waiting, Application.Wait is a really ineffective method to wait for the browser to load, and 2 seconds may not always be enough time anyways. The best way I can think of to handle this, assuming you're getting a type 91 error, is like so:

How to make a ready-waiting loop for Browser control

Basically you need to put the thread in a loop until the browser has loaded the page. In pseudo-code, you're doing:

Do Until Browser Is Loaded
   DoEvents 'etc.
Loop

Using the WinAPI Sleep Function

You will need to use the WinAPI Sleep function (a lot of people use DoEvents or Application.Wait but I have found here that Sleep is preferable.

Because it is a Declare, you must put it in an ordinary module, it can't go in a class or userform module.

'## This needs to go in an ordinary code module
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Calling the WinAPI Sleep Function

Sleep 250   'sleep for 1/4 of a second, or Sleep 1000 to sleep for a full second, etc.

Putting it all together

You will need to put a Do ... Loop that calls on this function immediately after the .Navigate method.

   '## This goes in where you do Navigate method:
    With appIE
        .Navigate sURL

        Do
            Sleep 250
        Loop While Not .ReadyState <> 4 'READYSTATE_COMPLETE

        Set HTMLDoc = .Document
    End With

If it still doesn't work...

Yes, there's always an exception. I do very little web-based automation, and most of what I do is just tinkering with stuff here to help people out. What I have noticed is that increasingly websites are served dynamically (AJAX maybe?) and that in these cases, the browser may be .ReadyState = READYSTATE_COMPLETE and even .Busy = False, yet the .Document is still Nothing.

IF that happens, you could put a secondary loop, like:

Dim numberOfAttempts
Do While .Document Is Nothing
    Sleep 250
    numberOfAttempts = numberOfAttempts + 1
    If numberOfAttempts > 100 Then 
        MsgBox "This is taking too long, try again later."
        Exit Do
    End If
Loop

You may want to add an iterator/counter as I have done there and simulate a timeout otherwise this could potentially go in to an infinite loop.

Answer 2

Thanks for all the great tips going to implement plenty of them. The true error behind this was a Windows settings. UAC :

http://windows.microsoft.com/en-us/windows7/products/features/user-account-control Setting it down to the bottom "never notify" fixed it immediately.