Hello Joel

Thanks for your help.

I gert the following error when I click on the Open button of the web
browser when the download call history web dialog pops up:

Error on Method "Busy" of the Object "IWebBrowser2"

Thanks & regards
farid

"joel" wrote:

Private Sub Workbook_Open()
Net2Phone
End Sub

Sub Net2Phone()
Dim objIE As Object
Dim strServAcct As String

strServAcct = "3484690293"

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True

objIE.Navigate "https://partner.net2phone.com/apps/account/search.aspx"
WaitForLoad objIE

Set UserId = objIE.document.getElementById("txtUserID")
If Not UserId Is Nothing Then
'Input user name and password
UserId.Value = "famaperu45"
Set Password = objIE.document.getElementById("txtPassword")
Password.Value = "my69car"

'submit the form by clicking "Login"
Set Login = objIE.document.getElementById("btnlogin")
Login.Click

WaitForLoad objIE
End If

Set AccountBox = _
objIE.document.getElementById("ctl00$pageBody$txtS erviceAccount")
AccountBox.Value = strServAcct

Set SearchButton = _
objIE.document.getElementById("ctl00$pageBody$btnS earch")
SearchButton.Click

WaitForLoad objIE

objIE.Navigate "https://partner.net2phone.com/apps/account/calls.aspx"
WaitForLoad1 objIE
RowCount = 1
'format the phone number as text
'long phone number become scientific notsation 1.3E+14
'which don't look like phone numbers
Columns("C").NumberFormat = "@"

RowCount = 1
'Done indicats we have no more pages in the call history
Done = False
With Sheets("Hoja1")
Do While Done = False
' wait for History page to load
'or Next page when multiple pages
WaitForLoad1 objIE

'the call history table start with class name equal gridview
State = "Find_GridView"
'get all items in html data
For Each itm In objIE.document.all

Select Case State

'skip everything until GRIDVIEW is found
Case "Find_GridView":
'when gridview is found output header row
If itm.classname = "gridview" Then
'set state to grid view to process
'call histor table
State = "Found_GridView"
End If
Case "Found_GridView":
'Input tag is the table on spreadsheet
'moving to next and previous page
If itm.tagname = "INPUT" Then
src = UCase(itm.src)
'code to download to excel option
If InStr(src, "EXCEL") 0 Then
itm.Onclick = src
'gop to next page
itm.Click
Exit For
End If
End If

End Select
Next itm
Loop
End With

End Sub

Sub WaitForLoad(IE As Object)


Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
End Sub

Sub WaitForLoad1(IE As Object)


Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
End Sub




"farid2001" wrote:

Joel

Thank you very much for your help.

I runned the following code successfully and for a 360 call's Call History
took 84 seconds, which is not bad at all.

Private Sub Workbook_Open()
Net2Phone
End Sub

Sub Net2Phone()
' PERFECT FOR UNLOADING MID-SIZE CALL_HISTORY TO WORKBOOK!!
Application.ScreenUpdating = False
Dim StartTime As Date, EndTime As Date
StartTime = Timer

Dim objIE As Object
Dim strServAcct As String

strServAcct = "4872750789"

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
objIE.Navigate "https://partner.net2phone.com/apps/account/search.aspx"
WaitForLoad objIE

Set UserID = objIE.document.getElementById("txtUserID")
If Not UserID Is Nothing Then
'Input user name and password
UserID.Value = "famaperu45"
Set Password = objIE.document.getElementById("txtPassword")
If Not Password.Value Is Nothing Then
Password.Value = "my69car"

'submit the form by clicking "Login"
Set Login = objIE.document.getElementById("btnlogin")
Login.Click

WaitForLoad objIE
End If
End If

Set AccountBox = _
objIE.document.getElementById("ctl00$pageBody$txtS erviceAccount")
AccountBox.Value = strServAcct

Set SearchButton = _
objIE.document.getElementById("ctl00$pageBody$btnS earch")
SearchButton.Click

WaitForLoad objIE


objIE.Navigate "https://partner.net2phone.com/apps/account/calls.aspx"
WaitForLoad objIE
RowCount = 1
'format the phone number as text
'long phone number become scientific notsation 1.3E+14
'which don't look like phone numbers
Columns("C").NumberFormat = "@"

RowCount = 1
'Done indicats we have no more pages in the call history
Done = False
With Sheets("Hoja1")
Do While Done = False
' wait for History page to load
'or Next page when multiple pages
WaitForLoad objIE

'the call history table start with class name equal gridview
State = "Find_GridView"
'get all items in html data
For Each itm In objIE.document.all

Select Case State

'skip everything until GRIDVIEW is found
Case "Find_GridView":
'when gridview is found output header row
If itm.classname = "gridview" Then
'set state to grid view to process
'call histor table
State = "Found_GridView"
'only put header row for 1st page
If RowCount = 1 Then
ColCount = 1
'put header row on worksheet
For Each cell In itm.Cells
.Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
End If
End If
Case "Found_GridView":
'Input tag is the table on spreadsheet
'moving to next and previous page
If itm.tagname = "INPUT" Then
src = UCase(itm.src)
'we are looking for item NEXT_PG_1
'to move to next page.
'not sure when there are more than two pages
'what the src is going to look like
'so I'm going to move to next page
'except under the case NEXT_PG_0
If InStr(src, "NEXT") 0 Then
If InStr(src, "NEXT_PG_0") = 0 Then
'found next page
'need to execute the command
'so I put the command in OnClick
'Then execute on click
itm.Onclick = src
'gop to next page
itm.Click
Exit For
End If
End If
End If

'If we don't find next page then we are at
'last page and the end of the gridview table
'is indicated by the ! - stop processing
If itm.tagname = "!" Then
'reached last page - stop
Done = True
Exit For
End If
'each item in call history has the tag TR
If itm.tagname = "TR" Then
'the end of the call history is a blank innertext
'we don't w\ant to put blank data into worksheet
'we can't end because we still need to check for
'a next page
If itm.innertext < "" Then
ColCount = 1
For Each cell In itm.Cells
.Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
End If
End If
End Select
Next itm
Loop
End With
EndTime = Timer
MsgBox Format(EndTime - StartTime, " 0.000 Segundos"), , "WHL Analisys
Tool"
End Sub

Sub WaitForLoad(IE As Object)
Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
End Sub

What would the complete code be for just downloading the excel file, without
transferring the calls to the workbook at all?

Thanks & regards
Farid

"joel" wrote:

I added to code previous code to export to excel. I left all the other code
intact so you can modifiy as required. the present code wil use my old code
to put one page into a work sheet and then call the Download to Excel option
and exit.



Private Sub Workbook_Open()
Net2Phone
End Sub

Sub Net2Phone()
Dim objIE As Object
Dim strServAcct As String

strServAcct = "3484690293"

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True