Opening Website - looking for error

S

stu_c

Registered User.
Local time
Today, 22:10
Joined
Sep 20, 2007
Messages
492
Hello all,
I have got some VBA code that automatically opens up the correct intranet webpage via reference numbers in other fields, ideally what I want is when a user clicks out of the field it searches the website for the words "Incorrect Ref", if this happens then to do an If statement to show up a popup box indication incorrect REF number. any suggestions how to do this?

This is the code used to open the form, as mentined ideally want it to search for the words "Incorrect", close down the webpage and show a pop up screen on the form.

Code: 
Public Function FUNC_HRREFForm()

'Input data into Niche
   Dim IE As Object
    
'Create InternetExplorer Object
   Set IE = New InternetExplorerMedium
With IE
    .Visible = True
   .navigate "http://corp/ViewHRDetails.asp?Headerid=" & Mid([Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails].[Form]![HRRef], 3, 8)
End With

For Each win In CreateObject("Shell.Application").Windows
    If win.Name Like "*Internet Explorer" Then
        Set IE = win: Exit For
   End If
Next

'Wait for IE to load
With IE
   Do Until .ReadyState = 4
   DoEvents
   Loop

End With

End Function
 
I can't remember the exact syntax, but you should be able to query the HTML of the page:
Code: 
'Wait for IE to load
With IE
   Do Until .ReadyState = 4
   DoEvents
   Loop
   If InStr(.Page.InnerHTML, "Incorrect Ref") > 0 Then
     ' It's there!
   End If
End With

Separately, what is the point of this code?
Code: 
For Each win In CreateObject("Shell.Application").Windows
    If win.Name Like "*Internet Explorer" Then
        Set IE = win: Exit For
   End If
Next
Surely you already have the reference to the IE object since you just set it?
 
cheekybuddha said:
I can't remember the exact syntax, but you should be able to query the HTML of the page:
Code: 
'Wait for IE to load
With IE
   Do Until .ReadyState = 4
   DoEvents
   Loop
   If InStr(.Page.InnerHTML, "Incorrect Ref") > 0 Then
     ' It's there!
   End If
End With

Separately, what is the point of this code?
Code: 
For Each win In CreateObject("Shell.Application").Windows
    If win.Name Like "*Internet Explorer" Then
        Set IE = win: Exit For
   End If
Next
Surely you already have the reference to the IE object since you just set it?
Click to expand...
Hello
I tried the above code but nothing seemed to work, no errors either

Also the question about the code after a lot of testing it was needed, I am unsure why
 
Do you actually need to load the page visibly?

Or do you just need to know whether "Incorrect Ref" appears on the page?
 
stu_c said:
I tried the above code but nothing seemed to work, no errors either
Click to expand...
Nothing will happen with the code I posted.

You need to do something in the part where i put the comment: ' It's there!

Put a msgbox to indicate the result, eg:
Code: 
' ...
   If InStr(.Page.InnerHTML, "Incorrect Ref") > 0 Then
     MsgBox "It's there!"
   Else
     MsgBox "It's Not There"
   End If
' ...
 
cheekybuddha said:
Do you actually need to load the page visibly?

Or do you just need to know whether "Incorrect Ref" appears on the page?
Click to expand...
Hello,
no it doesn't have to load if it shows incorrect Ref but I was under the impression it would need to find the data?
 
Try adding the following function in a standard module:
Code: 
Function HTTP_Get(webServiceURL As String) As String

  Const XMLHTTP             As String = "MSXML2.ServerXMLHTTP", _
        METHOD_GET          As String = "GET", _
        HTTP_STATUS_SUCCESS As Integer = 200
    
  With CreateObject(XMLHTTP)
    .Open METHOD_GET, webServiceURL, False
    .Send
    If .Status = HTTP_STATUS_SUCCESS Then
      HTTP_Get = .ResponseText
    Else
      Debug.Print .Status & ": " & .StatusText
      Err.Raise vbObjectError + .Status, , .Status & ": " & .StatusText
    End If
  End With
 
End Function

Then, use code like:
Code: 
Dim url As String, html As String

url = "http://corp/ViewHRDetails.asp?Headerid=" & Mid([Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails].[Form]![HRRef], 3, 8)
html = HTTP_Get(url)
If InStr(html, "Incorrect Ref") > 0 Then
  MsgBox "Found it"
Else
  MsgBox "Not found"
End If
 
cheekybuddha said:
If InStr(html, "Incorrect Ref") > 0 Then
MsgBox "Found it"
Else
MsgBox "Not found"
End If
Click to expand...
Your code will find that string even if it's not meant to be shown. Maybe OP should post a snippet of the html.
 
561414 said:
Your code will find that string even if it's not meant to be shown. Maybe OP should post a snippet of the html.
Click to expand...
First step is to find whether text is present. Once found you can then check whether it's valid!
 
Hello
I have tried the below code but getting an error of "Compile Error Expected End With"

Code: 
'Wait for IE to load
With IE
   Do Until .ReadyState = 4
   DoEvents
   Loop

Dim url As String, html As String
If InStr(html, "Incorrect Ref") > 0 Then
  MsgBox "Found it"
Else
  MsgBox "Not found"
 
End If
End Function

cheekybuddha said:
Try adding the following function in a standard module:
Code: 
Function HTTP_Get(webServiceURL As String) As String

  Const XMLHTTP             As String = "MSXML2.ServerXMLHTTP", _
        METHOD_GET          As String = "GET", _
        HTTP_STATUS_SUCCESS As Integer = 200
   
  With CreateObject(XMLHTTP)
    .Open METHOD_GET, webServiceURL, False
    .Send
    If .Status = HTTP_STATUS_SUCCESS Then
      HTTP_Get = .ResponseText
    Else
      Debug.Print .Status & ": " & .StatusText
      Err.Raise vbObjectError + .Status, , .Status & ": " & .StatusText
    End If
  End With

End Function

Then, use code like:
Code: 
Dim url As String, html As String

url = "http://corp/ViewHRDetails.asp?Headerid=" & Mid([Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails].[Form]![HRRef], 3, 8)
html = HTTP_Get(url)
If InStr(html, "Incorrect Ref") > 0 Then
  MsgBox "Found it"
Else
  MsgBox "Not found"
End If
Click to expand...
 
stu_c said:
Hello
I have tried the below code but getting an error of "Compile Error Expected End With"
Click to expand...
You didn't really do what I suggested.

The code in Post #7 is separate to what you had already, a different method of trying to see whether the phrase is in the webpage.
 
Something like this should do the job
Code: 
Private Sub FUNC_HRREFForm()

    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim html As String
  
    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .Navigate "https://www.google.com.mx/"
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        Set doc = .Document
        html = doc.documentElement.OuterHTML
        Debug.Print html
        If Instr(html, "Incorrect Ref") > 0 Then
            MsgBox "Incorrect Ref exists in the HTML string"
        Else
            MsgBox "Incorrect Ref does NOT exists in the HTML string"
        End if
    End With
  
End Sub
Copy the html code from the immediate window and post it here so we can check how to do this in a better way. As is, it will just find the string "Incorrect Ref", but we don't know if it's a dynamically created element or if it exists in the HTML code and it becomes visible when conditions meet, there could also be other factors involved, so it's best if we analyze it. Or do that yourself.

Requires a reference to HTML Objects. I have found better results when this is early bound.
 
Last edited:
Hello,
This is the code, hopefully might shed some light
Code: 
<html>

<head>
<meta http-equiv="Content-Language" content="en-gb">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
<link rel="stylesheet" type="text/css" href="CPR.css">
<link rel="stylesheet" href="../jquery-ui-1.10.4/themes/redmond/jquery-ui.css">

<script src="../jquery-ui-1.10.4/jquery-1.10.2.js"></script> 
<script src="../jquery-ui-1.10.4/ui/jquery-ui.js"></script>

<script type="text/javascript">
function changeref(hid)
{
window.open("changeref.asp?headerid=" + hid, "STAFF", "top=50,width=500,height=300,toolbar=no,menubar=no,resizable=no,scrollbars=no,titlebar=no,menubar=no")
}

function changerecnum(hid)
{
window.open("changeRecNum.asp?headerid=" + hid, "STAFF", "top=50,width=500,height=300,toolbar=no,menubar=no,resizable=no,scrollbars=no,titlebar=no,menubar=no")
}


function chase(Hid)
{
window.open("chase.asp?headerid=" + Hid, "ChaseUpEmail", "top=50,width=400,height=200,toolbar=no,menubar=no,resizable=no,scrollbars=no")
}

function viewstf(hid)
{
window.open("viewstf.asp?headerid=" + hid, "StaffChange", "top=50,width=500,height=400,toolbar=no,menubar=no,resizable=no,scrollbars=no,titlebar=no,menubar=no")
}


function item(Hid,Iid)
{
window.open("detail.asp?headerid=" + Hid + "&ItemId=" + Iid, "Item", "top=50,width=800,height=700,toolbar=no,menubar=no,resizable=no,scrollbars=yes")
}

function editaddress(Hid)
}
function enablesave(opt)
{

    document.getElementById("SaveBtn").disabled = true;
    if (opt==1)
        {
        var AddPri=document.getElementById("Location").value
        if (AddPri.length>1)
            {
            document.getElementById("SaveBtn").disabled = false;
            }
            else
            {
            document.getElementById("SaveBtn").disabled = true;
            }
        }
    if (opt==2)
        {
        document.getElementById("SaveBtn").disabled = false;
        }
    if (opt==3)
        {
        document.getElementById("SaveBtn").disabled = false;
        }
    if (opt==4)
        {
        document.getElementById("SaveBtn").disabled = false;
        }
    if (opt==5)
        {
        var AddPri=document.getElementById("mthnotes").value
        if (AddPri.length>1)
            {
            document.getElementById("SaveBtn").disabled = false;
            }
            else
            {
            document.getElementById("SaveBtn").disabled = true;
            }
        }
    if (opt==6)
        {
        document.getElementById("SaveBtn").disabled = false;
        }           
}
</script>

<script type="text/javascript" src="../JS/atc.min.js" async defer></script>
</head>
<!--VERSION 2.0 10/05/2012-->
<!--
GetUserDetails modified to hold session variable.
magic dot emulation checking removed.
-->

<!--VERSION 2.1 29/06/2012-->
<!--
Now writes USER_AGENT to activity log.
-->



<body bgcolor="#CBD3DA">
 <font face="Arial" size=2>
<p>Microsoft SQL Server Native Client 10.0</font> <font face="Arial" size=2>error '80040e07'</font>
<p>
<font face="Arial" size=2>Incorrect Ref.</font>
<p>
<font face="Arial" size=2>/fgr/ViewDetails.asp</font><font face="Arial" size=2>, line 189</font>



561414 said:
Something like this should do the job
Code: 
Private Sub FUNC_HRREFForm()

    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim html As String
 
    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .Navigate "https://www.google.com.mx/"
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        Set doc = .Document
        html = doc.documentElement.OuterHTML
        Debug.Print html
        If Instr(html, "Incorrect Ref") > 0 Then
            MsgBox "Incorrect Ref exists in the HTML string"
        Else
            MsgBox "Incorrect Ref does NOT exists in the HTML string"
        End if
    End With
 
End Sub
Copy the html code from the immediate window and post it here so we can check how to do this in a better way. As is, it will just find the string "Incorrect Ref", but we don't know if it's a dynamically created element or if it exists in the HTML code and it becomes visible when conditions meet, there could also be other factors involved, so it's best if we analyze it. Or do that yourself.

Requires a reference to HTML Objects. I have found better results when this is early bound.
Click to expand...
 
Code: 
Private Sub FUNC_HRREFForm()

    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim html As String

    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .Navigate "http://corp/ViewHRDetails.asp?Headerid=" & Mid([Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails].[Form]![HRRef], 3, 8)
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        Set doc = .Document
        html = doc.documentElement.OuterHTML
        Debug.Print html
        If Instr(html, "Incorrect Ref") > 0 Then
            MsgBox "Incorrect Ref exists in the HTML string"
        Else
            MsgBox "Incorrect Ref does NOT exists in the HTML string"
        End if
    End With

End Sub

The code above will open your website using internet explorer. It will then print the entire html code in the immediate window, which fits 255 lines, I think, so if it's longer it won't show all of the lines. Then, regardless of what's in the immediate window, it will look for the string "Incorrect Ref" in the entire page and show a message box telling you whether the string exists or not.

stu_c said:
when a user clicks out of the field it searches the website for the words "Incorrect Ref"
Click to expand...
What field do you mean? Is it a textbox? If so, what is the name of that textbox? Let's say the textbox is called txtBox, are you going to check for txtBox_LostFocus() or txtBox_Exit() ? Or something else? Where is the user going to click to count as out of the field? Should either of the two previous events fire, they will fire if you focus on some other control, Exit first, then LostFocus. And if they fire, then you can call FUNC_HRREFForm from them, which will open an internet explorer window looking for that string in the html code.

By the way, the html code you posted appears to be incomplete. Post how it looks when the Incorrect Ref string is visible and when it's not, so we can evaluate, or just post here whether Incorrect Ref exists in the source code even when it does not show for the user. In simple terms, if it's a hidden string that becomes visible, we have to check for its visibility, if that's not the behavior then we can simply check for its existence, like cheekybuddha suggested.

If you just want to check for its existence, modify this part of the code to open the form you want to open:
Code: 
        If Instr(html, "Incorrect Ref") > 0 Then
            'You can use the OpenArgs argument to notify the user of the result
            DoCmd.OpenForm "asd", , , , , acDialog, "Incorrect Ref. Exists"
        Else
            DoCmd.OpenForm "asd", , , , , acDialog, "Incorrect Ref. Does NOT Exists"
        End if

Then, to quit the app, use IE.Quit in the appropriate place, maybe before the above instruction.
 
Hello,
Thank you for the below, I have decided to put it into a button which probably is going to be a bit less annoying for the user, the page opens up but then I get an error of Run-time error '-2147467259 (80004005) automation error unspecified error.

it then highlights While .Busy Or .ReadyState <> 4: :(

561414 said:
Code: 
Private Sub FUNC_HRREFForm()

    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim html As String

    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .Navigate "http://corp/ViewHRDetails.asp?Headerid=" & Mid([Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails].[Form]![HRRef], 3, 8)
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        Set doc = .Document
        html = doc.documentElement.OuterHTML
        Debug.Print html
        If Instr(html, "Incorrect Ref") > 0 Then
            MsgBox "Incorrect Ref exists in the HTML string"
        Else
            MsgBox "Incorrect Ref does NOT exists in the HTML string"
        End if
    End With

End Sub

The code above will open your website using internet explorer. It will then print the entire html code in the immediate window, which fits 255 lines, I think, so if it's longer it won't show all of the lines. Then, regardless of what's in the immediate window, it will look for the string "Incorrect Ref" in the entire page and show a message box telling you whether the string exists or not.


What field do you mean? Is it a textbox? If so, what is the name of that textbox? Let's say the textbox is called txtBox, are you going to check for txtBox_LostFocus() or txtBox_Exit() ? Or something else? Where is the user going to click to count as out of the field? Should either of the two previous events fire, they will fire if you focus on some other control, Exit first, then LostFocus. And if they fire, then you can call FUNC_HRREFForm from them, which will open an internet explorer window looking for that string in the html code.

By the way, the html code you posted appears to be incomplete. Post how it looks when the Incorrect Ref string is visible and when it's not, so we can evaluate, or just post here whether Incorrect Ref exists in the source code even when it does not show for the user. In simple terms, if it's a hidden string that becomes visible, we have to check for its visibility, if that's not the behavior then we can simply check for its existence, like cheekybuddha suggested.

If you just want to check for its existence, modify this part of the code to open the form you want to open:
Code: 
        If Instr(html, "Incorrect Ref") > 0 Then
            'You can use the OpenArgs argument to notify the user of the result
            DoCmd.OpenForm "asd", , , , , acDialog, "Incorrect Ref. Exists"
        Else
            DoCmd.OpenForm "asd", , , , , acDialog, "Incorrect Ref. Does NOT Exists"
        End if

Then, to quit the app, use IE.Quit in the appropriate place, maybe before the above instruction.
Click to expand...
 
Did you check if the page will open when the url string contains an incorrect ref nr?
 
At this point, it would be best if you could post something we can play with, because that error is not very specific, but since it highlights the line that checks if it's fully loaded, we could add a timer or approach it differently.

You have not yet confirmed if the "Incorrect Ref" string exists within the html code when it does not show to the user. It is important because it could be a hidden element and since we don't know how it's rendered, all we can do is wait for your answer to that. So, post how it looks when Incorrect Ref does not show.

You also didn't mention if cheeky's suggestion was followed. There's also the possibility of doing this with a browser control.
 
This is the Only HTML code I can see?

Code: 
<html>
<head>
<meta http-equiv="Content-Language" content="en-gb">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
<link rel="stylesheet" type="text/css" href="CPR.css">
<link rel="stylesheet" href="../jquery-ui-1.10.4/themes/redmond/jquery-ui.css">

<script src="../jquery-ui-1.10.4/jquery-1.10.2.js"></script>
<script src="../jquery-ui-1.10.4/ui/jquery-ui.js"></script>

<script type="text/javascript">
function changeref(hid)
{
window.open("changeref.asp?headerid=" + hid, "STAFF", "top=50,width=500,height=300,toolbar=no,menubar=no,resizable=no,scrollbars=no,titlebar=no,menubar=no")
}

function changerecnum(hid)
{
window.open("changeRecNum.asp?headerid=" + hid, "STAFF", "top=50,width=500,height=300,toolbar=no,menubar=no,resizable=no,scrollbars=no,titlebar=no,menubar=no")
}


function chase(Hid)
{
window.open("chase.asp?headerid=" + Hid, "ChaseUpEmail", "top=50,width=400,height=200,toolbar=no,menubar=no,resizable=no,scrollbars=no")
}

function viewstf(hid)
{
window.open("viewstf.asp?headerid=" + hid, "StaffChange", "top=50,width=500,height=400,toolbar=no,menubar=no,resizable=no,scrollbars=no,titlebar=no,menubar=no")
}


function item(Hid,Iid)
{
window.open("detail.asp?headerid=" + Hid + "&ItemId=" + Iid, "Item", "top=50,width=800,height=700,toolbar=no,menubar=no,resizable=no,scrollbars=yes")
}

function editaddress(Hid)
}
function enablesave(opt)
{

    document.getElementById("SaveBtn").disabled = true;
    if (opt==1)
        {
        var AddPri=document.getElementById("Location").value
        if (AddPri.length>1)
            {
            document.getElementById("SaveBtn").disabled = false;
            }
            else
            {
            document.getElementById("SaveBtn").disabled = true;
            }
        }
    if (opt==2)
        {
        document.getElementById("SaveBtn").disabled = false;
        }
    if (opt==3)
        {
        document.getElementById("SaveBtn").disabled = false;
        }
    if (opt==4)
        {
        document.getElementById("SaveBtn").disabled = false;
        }
    if (opt==5)
        {
        var AddPri=document.getElementById("mthnotes").value
        if (AddPri.length>1)
            {
            document.getElementById("SaveBtn").disabled = false;
            }
            else
            {
            document.getElementById("SaveBtn").disabled = true;
            }
        }
    if (opt==6)
        {
        document.getElementById("SaveBtn").disabled = false;
        }           
}
</script>

<script type="text/javascript" src="../JS/atc.min.js" async defer></script>
</head>
<!--VERSION 2.0 10/05/2012-->
<!--
GetUserDetails modified to hold session variable.
magic dot emulation checking removed.
-->

<!--VERSION 2.1 29/06/2012-->
<!--
Now writes USER_AGENT to activity log.
-->



<body bgcolor="#CBD3DA">
 <font face="Arial" size=2>
<p>Microsoft SQL Server Native Client 10.0</font> <font face="Arial" size=2>error '80040e07'</font>
<p>
<font face="Arial" size=2>Incorrect Ref.</font>
<p>
<font face="Arial" size=2>/fgr/ViewDetails.asp</font><font face="Arial" size=2>, line 189</font>
 
I think Edgar wants to see the HTML when 'Incorrect Ref' is not visible to you on your screen.

This is because the words may actually still be in the HTML, just hidden. If that's the case, then just scanning the HTML for the words is not sufficient for your needs as you might get false positives.

However, if 'Incorrect Ref' is not present in the HTML when it's not visible to you on the screen, then the current method will be OK.
 
You must log in or register to reply here.

Similar threads

S
VBA code to automate Internet explorer VIA if function
Replies
1
Views
588
bastanu
bastanu
S
Search HTML for words
Replies
11
Views
889
cheekybuddha
cheekybuddha
S
change URL
Replies
3
Views
625
sonic8
S
A
IE Automation - File Browser freezes VB
Replies
0
Views
1,432
AdrianThorn
A
M
How do I open a website from a cell in a spreadsheet using VBA?
Replies
0
Views
850
mrtnlagonda
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom