Fill Form with VBA. Problems with .busy .readyState

**Zheno** · 01-16-2012, 05:13 AM

I Have some code that goes through cells in my spreadsheet opens the hyperlink in the cells, Puts in logging information and prints the file.

Here is the code i use for this

Sub OpenandPrintURLinIE()
    
    Dim FilePath, CurrentWB
    Dim PrintRng As Range
    Dim myURL As String
    Dim IElocation As String
    Dim myIE As New InternetExplorer
    Dim myURLx As String
    Dim myDoc As HTMLDocument
    Dim strPass As String
    Dim StrName As String
    
    'the range in excel that should be printed
    Set PrintRng = Range("WeldPrint")
    
    CurrentWB = ThisWorkbook.Name
    
    For Each Cell In PrintRng
           

            Workbooks(CurrentWB).Activate
            
            Cell.Select
            
            'check if there is an actual hyperlink in cell
            If ActiveCell.Value = "-" Then GoTo Line2 Else GoTo Line1

Line1:      'if there is a hyperlink code starts here
            If ActiveCell.Value = "" Then Exit Sub
            FilePath = Selection.Hyperlinks(1).Address
            
            'the input values
            strPass = xxxx
            StrName = yyyy
            
            'filepath to webfor to fill
            myURL = FilePath
            
            myIE.navigate myURL
            myIE.Visible = True
            
            'wait until IE has loaded. This is the part that bugs for me
            Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
            DoEvents
            Loop

            Set myDoc = myIE.document
            
            'Input the values in the webform
            myDoc.forms(0).userPswd.Value = strPass
            myDoc.forms(0).useradr.Value = StrName
            
            'From here on it just open the page and prints it
            Sleep 2000
            SendKeys "~"
            Sleep 4000
            
            SendKeys "^p", True
    'Delay a second before entering OK
            Sleep 2000
    'Send <Enter> to print
            SendKeys "~", True
    'Delay five seconds so printjob can be spooled before closing IE
            Sleep 5000
    ' Send <ALT>+F4 to close IE
            SendKeys "%{F4}", True
Line2:
    Next Cell
    End Sub

The filepath is a variation of https://service.projectplace.com/pp/start.cgi? but if you put in that adress you can see the elements that you need.

This is the part of the code atm that i have problem with

Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
            DoEvents
            Loop

i get an automatation error, undefined error.

When i first implented the code everything worked, but the second day when i was gonna try it out, it didnt work and it hasnt been working since then. I have tried diffrent methods of this oart of the code but i cant get it to work anymore.
Anyone that have any idea?

**Chippy** · 01-16-2012, 07:45 PM

See if you can incorporate this code into your code. It does the IE automation, populating and submitting the form, but obviously the login is rejected because I don't know a valid email address and password.

Sub Automate_IE()

    Dim URL As String
    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument

    Set IE = New InternetExplorer
    
    URL = "https://service.projectplace.com/pp/start.cgi?"
    
    With IE
        .navigate URL
        .Visible = True
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Loop
        
        Set HTMLdoc = .document
            
        With HTMLdoc.forms(0)
            .useradr.Value = "Email@email.com"
            .userPswd.Value = "Password"
            .submit
        End With
        
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Loop
    End With
    
End Sub

Also, there's really no need to use GoTo and the associated labels. Avoid them by reversing the logic of the If statement like this: If ActiveCell.Value <> "-" Then

**Zheno** · 01-17-2012, 02:45 AM

Thanks Chippy,

I still get the Automation error, undefined error on the code line

Do While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Loop

I have put reference to Microsoft HTML obejct and Microsoft Internet controls Is there some other reference i need to activate?

Just to be clear, i used your code in a new workbook so it shouldnt be anything else in my code messing it up.

Im really lost here why that line of code wont work for me.