fail to open wanting-new-window via specific site's js code in VBA excel with ie automation

[Jongho Lee]

Hello,
I need any help or idea.

I did as follows :

1. in browser, connect url, KIPRIS ( real site ).
"some url" :
'http://engpat.kipris.or.kr/engpat/searchLogina.do?next=MainSearch'

2. input search keyword and search
"search keyword" :
'smart phone'

3. then click any hyperlink of search result, it shows detail info of search result in new window(popup)

4. navigating tabs or sheets-like link(?, i don't know the name) in the middle top of the detail info page.

5. and i want to gather data (some date info) in those page.
(ex:in the left side's app no, 10-2005-0098287, View Details - Legal Status - and in it's contents, i am gathering the no 7, 'Decision to grant' date info.)
** not english char means korean char(2 byte).

In browser it works well,
But i have a problem in coding -
1. calling js function in a web page with ie automation.
2. getting object of new window in vba-excel in my trying site.
** but other url(ex:Google) works well in new window, not i'm trying this url.
3. i got runtime err(automation err) in the code 'Do Until TypeName(ie2.Document) = "HTMLDocument"', i think so.
4. have to change the value of some_url var in Sheet1 code to 'http://engpat.kipris.or.kr/engpat/searchLogina.do?next=MainSearch' when test my code.

I think in my code

Code:
<code>ie1.navigate2 "javascriptpenDetail(...)", 1 </code>

some js function works well, but some other function(specially referencing ie document) not work, also callee window .open() in js call stack not work, i think so.
( in my below code, finally my trying 'javascriptpenDetail(...)' calls 'window .open()' )

What's wrong in my code ? Where is my miss in my code ?

Thanks in advance.



In my case(Korean Win 7 64bit, Visual Basic 6.5, Office 2007), it opens not wanting-detail-info search result page but download manager window.

My code as belows :

Sheet1 code :

<code>
Option Explicit
Dim oWB As Workbook

Private Sub CommandButton1_Click()
On Err GoTo HANDLER_outer

Dim some_url As String

Set oWB = ActiveWorkbook
Application.StatusBar = "url connecting ..."

some_url = "some_url"
oWB.IE_Auto (some_url)

If TypeName(oWB) <> "Nothing" Then
Set oWB = Nothing
End If

Exit Sub

HANDLER_outer:
Debug.Print "Exception occurred, outer !!!" & vbCrLf & " Err.Number : " & Err.Number & vbCrLf & " Err.Description : " & Err.Description

If TypeName(oWB) <> "Nothing" Then
Set oWB = Nothing
End If
End Sub
</code>


ThisWorkbook code :

<code>
Option Explicit

Private Declare Sub Sleep Lib "kernel32" ( _
ByVal dwMilliseconds As Long)

Dim WithEvents ie1 As InternetExplorer
Dim ie2 As InternetExplorer
Dim HTMLDoc As HTMLDocument

Dim Node As Object
Dim NodeCollection As Object
Dim objElement As Object


Private Sub ie1_NewWindow2(ppDisp As Object, Cancel As Boolean)
Set ie2 = CreateObject("InternetExplorer.Application")
Set ppDisp = ie2.Application
Debug.Print "NewWindow2"
End Sub

Public Sub closing()
Debug.Print "closing ..."
If TypeName(ie1) <> "Nothing" Then
ie1.Quit
Set ie1 = Nothing
End If
If TypeName(ie2) <> "Nothing" Then
ie2.Quit
Set ie2 = Nothing
End If
If TypeName(Node) <> "Nothing" Then
Set Node = Nothing
End If
If TypeName(NodeCollection) <> "Nothing" Then
Set NodeCollection = Nothing
End If
If TypeName(objElement) <> "Nothing" Then
Set objElement = Nothing
End If
Debug.Print "closed ..."
End Sub

Public Sub IE_Auto(some_url)

On Err GoTo HANDLER

Dim Text As String
Dim iCount As Integer ' loop counter
Dim total As String
Dim nTotal As Integer
Dim nSleep As Integer

nSleep = 100
'=============================
Debug.Print "--------------------------"
Debug.Print "Sub IE_Auto() starting ..."
Debug.Print "--------------------------" & vbCrLf

'
' URL connect
'==============

' Create InternetExplorer Object
If ie1 Is Nothing Then
Set ie1 = CreateObject("InternetExplorer.Application")
End If

ie1.Visible = True

' Send the form data To URL As POST binary request
ie1.Navigate some_url

' Statusbar
Application.StatusBar = "url is loading. Please wait ..."

Do While ie1.ReadyState <> READYSTATE_COMPLETE Or ie1.Busy
DoEvents 'wait until ie1 is done loading page.
Loop

Debug.Print "loaded web page."
'
' search
'==============
' TypeName(ie1.Document) : "HTMLDocument"
iCount = 0
Do Until TypeName(ie1.Document) = "HTMLDocument"
DoEvents
Sleep nSleep
iCount = iCount + 1
Debug.Print "Load-" & iCount & ", ie1 busy !"
If iCount * nSleep > 2000 Then
Debug.Print "Load-ie1 timeout : " & iCount * nSleep
MsgBox "Load-ie1.Document timeout : " & iCount * nSleep
closing
Exit Sub
End If
Loop

ie1.Document.all("KW").Value = "smart phone"
ie1.Document.all("btnItemizedSearch").Click

' Statusbar
Application.StatusBar = "searching. Please wait ..."

iCount = 0
Do While ie1.ReadyState <> READYSTATE_COMPLETE Or ie1.Busy
DoEvents 'wait until ie1 is done loading page.
Sleep nSleep
iCount = iCount + 1
Debug.Print "Search-" & iCount & ", ie1 busy !"
If iCount * nSleep > 5000 Then ' timeout
Debug.Print "Search-ie1 timeout : " & iCount * nSleep
MsgBox "Search-ie1.Document timeout : " & iCount * nSleep
closing
Exit Sub
End If
Loop

'
' search result
'==============


iCount = 0
Do Until TypeName(ie1.Document) = "HTMLDocument"
DoEvents
Sleep nSleep
iCount = iCount + 1
Debug.Print "SearchRslt-Load-" & iCount & ", ie1 busy !"
If iCount * nSleep > 10000 Then
Debug.Print "SearchRslt-Load-ie1 timeout : " & iCount * nSleep
MsgBox "SearchRslt-Load-ie1.Document timeout : " & iCount * nSleep
closing
Exit Sub
End If
Loop

iCount = 0
Do Until TypeName(ie1.Document.querySelector("span[class='total']")) = "HTMLSpanElement"
DoEvents
Sleep nSleep
iCount = iCount + 1
Debug.Print "Query-" & iCount & ", ie1 busy !"
If iCount * nSleep > 10000 Then
Debug.Print "Query-ie1 timeout : " & iCount * nSleep
MsgBox "Query-ie1 timeout : " & iCount * nSleep
closing
Exit Sub
End If
Loop

Set NodeCollection = ie1.Document.querySelector("span[class='total']")
total = NodeCollection.innerText
Debug.Print "search result(total) : " & total
nTotal = CInt(Replace(total, ",", ""))

Set NodeCollection = ie1.Document.querySelectorall("article[id]")
Debug.Print "querySelectorall 'article[id]' : " & NodeCollection.Length ' length : 30

iCount = 0
Do While iCount < NodeCollection.Length
Set objElement = NodeCollection.Item(iCount)
iCount = iCount + 1
If Not IsNull(objElement.ID) Then
' display data till 2
If iCount < 3 Then
Debug.Print iCount & ":" & objElement.ID
Else
Exit Do
End If
Else
End If
Loop


' javascript running test ==> result, normal js code OK
' , but happened automation err when js code referencing ie1.document was called ???
' AllCheckResultItem(), isNormalNo() and isCheckNo() , included js function of the web page
'----------
'Call ie1.Document.parentWindow.execScript("alert('test')", "JavaScript") ' OK
'Call ie1.Document.parentWindow.execScript("AllCheckResultItem('on')", "JavaScript") ' X
'Call ie1.Document.parentWindow.execScript("isNormalNo('ab*cd')", "JavaScript") ' OK
'Call ie1.Document.parentWindow.execScript("isCheckNo('xyz')", "JavaScript") ' OK

'closing
'Exit Sub

' program handling target tag : article, div, h1 title=, a, click
Set objElement = objElement.querySelector("a")
Debug.Print "anchor href : " & objElement.href ' javascriptpenDetail(...)

'Call ie1.Document.parentWindow.execScript(objElement.href) ' SCRIPT5007: Unable to get value of the property 'focus': object is null or undefined
ie1.Navigate2 objElement.href, 1 ' opened download manager window (in vba with WIN7 64 bit, IE10, OFFICE2007)

' open a download manager window ( something wrong ! )
iCount = 0
Do Until ie1.ReadyState = READYSTATE_COMPLETE And Not ie1.Busy And (Not ie2 Is Nothing)
DoEvents 'wait until IE is done loading page.
Sleep 2 * nSleep
iCount = iCount + 1
Debug.Print "New window-" & iCount & ", ie1 or IE2 busy !"
If iCount * nSleep > 5000 Then ' timeout
Debug.Print "New window timeout : " & iCount * nSleep
MsgBox "New window timeout : " & iCount * nSleep
closing
Exit Sub
End If
Loop

'Sleep 30 * nSleep ' unit : milli-sec

ie2.Visible = True

' data check
iCount = 0
Do Until TypeName(ie2.Document) = "HTMLDocument"
DoEvents
Sleep nSleep
iCount = iCount + 1
Debug.Print "New widnow-Rendering-" & iCount & ", ie2 busy !"
If iCount * nSleep > 10000 Then
Debug.Print "New widnow-Rendering timeout : " & iCount * nSleep
MsgBox "New widnow-Rendering timeout : " & iCount * nSleep
closing
Exit Sub
End If
Loop
Set NodeCollection = ie2.Document.querySelectorall("section")

MsgBox "querySelectorall 'section' : " & NodeCollection.Length ' length :

If NodeCollection.Length > 0 Then
iCount = 0
Do While iCount < NodeCollection.Length
Set objElement = NodeCollection.Item(iCount)
iCount = iCount + 1
If iCount < 3 Then
MsgBox iCount & ":" & objElement.ID
Else
Exit Do
End If
Loop
Else
MsgBox "wrong ... : not found any section tag"
End If

'
' Detail info
'==============
' ToDo : call js, javascript:goSubDetail('ViewSub03', '1020050098287') and query
'

'ie1.Visible = True
'ie2.Visible = True
closing

' Statusbar
Application.StatusBar = "Ended ..."

Exit Sub

HANDLER:
Debug.Print "Exception occurred !!!" & vbCrLf & " Err.Number : " & Err.Number & vbCrLf & " Err.Description : " & Err.Description
closing

End Sub
</code>

 

[Norie]

Why not just click the link?

That shoud trigger the script.

 

[Jongho Lee]

Thanks for your reply.

I want input search keyword and gather the search result(ex: app no) and a few date info in the sheet.

Now I have been doing handy periodically as your word, but it bothersome and too much like hard work,
so i am trying automation coding with vba.